If you are creating database diagrams using Microsoft® SQL Server™, you can take advantage of features specific to that server. The following guidelines outline differences you will notice in the Database Designer when you work with SQL Server, and provide information about SQL Server-specific features that you can use.
Below you will find information about:
Column and table names in a SQL Server database can be stored in uppercase letters, lowercase letters, or a combination of both. For example, a column name can appear as "LASTNAME," "LastName," or "lastname."
Depending on how SQL Server was installed, databases can be case-sensitive or case-insensitive. If a database is case-sensitive, you must enter owner, table, and column names using the correct combination of uppercase and lowercase characters. If the case of the name you provide does not match exactly, SQL Server returns an error reporting an "invalid object name."
The objects in the Data View window always accurately reflect the case-sensitivity of your server. However, when entering column names in a database diagram, you must be careful to match names to the way they will be interpreted by the server.
If the server was installed with a case-insensitive option, you can enter database object names using any combination of uppercase and lowercase characters.
Tip To determine the case sensitivity of a server, execute the stored procedure sp_server_info, and then examine the contents of row 18. If the server has been installed with the case-insensitive setting, the option for sort_order will be set to nocase.
For information on the procedure you use to set column properties, see Setting Column Properties.
Although the procedure for setting column properties is similar regardless of the database you are using, the following items contain information specific to Microsoft SQL Server databases.
The following default values are automatically added for the new column:
| Data Type | Description | Default value |
| Column Name | The name of a column in a table. Column names must conform to your database rules for identifiers and must be unique in the table. | Blank |
| Datatype | The data type of the column. System- or user-defined data types are acceptable. | Character (char) |
| Length | The maximum number of digits (for numeric data types) or characters allowed for values in the column. | 10 |
| Precision | The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. | 0 |
| Scale | The maximum number of decimal digits that can be stored to the right of the decimal point. This value must be less than or equal to the precision. | 0 |
| Allow Nulls | Whether or not the column can accept null values. | Yes (checked) |
| Default Value | The value that will be inserted into the column if the user does not make an entry. Default values are ignored for columns with a timestamp data type. If you do not define a default value and a column allows nulls, NULL will be inserted. | Blank |
| Identity | Whether or not the column will generate incremental values for new rows based on the Identity Seed and Identity Increment settings. | No (not checked) |
| Identity Seed | The value assigned to the first row in the table. If left blank, this setting default is 1. | Blank |
| Identity Increment | The value which is added to the Identity Seed and assigned to the second row in the table. Each subsequent row is increased by this value. If left blank, this setting default is 1. | Blank |
A column's data type determines what kind of data can be stored in the column. A list of system-defined data types appears in the Datatype property column in Column Properties view.
You can choose the appropriate data type for the information you want to store in the column. If user-defined data types exist for your database, they appear at the end of the Datatype list. The system-defined data type that corresponds to the user-defined data type appears in parentheses at the end of the user-defined data type name. For example: "id (varchar)."
For information about creating user-defined data types in Microsoft SQL Server, see "Creating User-Defined Datatypes" in SQL Server Books Online or Database Developer’s Companion.
Note Changing the data type recreates the table in the database when you save the table or diagram. For more information about automatically recreating tables, see Working with Database Objects.
Caution If this column is related to columns in other tables, then the data type of the related columns must also be changed to preserve referential integrity. When you save the table or diagram, the Datatype Change Required dialog box enables you to automatically change the data type of the related columns.
| Data type | Description |
| binary | Holds a maximum of 255 bytes of fixed-length binary data; default length is 10. Columns of this type are accessed somewhat faster than varbinary columns because they use a fixed storage length. Choose binary when you think the values entered in this column will be consistently close to the same size. You can create relationships between binary columns and varbinary columns. |
| bit | Holds either 1 or 0. Integer values other than 1 or 0 are accepted but are always interpreted as 1. Storage size is 1 byte. Multiple bit types in a table can be collected into bytes. For example, 7-bit columns fit into 1 byte; 9-bit columns take 2 bytes. Columns of this type cannot be NULL and cannot have indexes on them. Choose bit for true/false or yes/no types of data. |
| char | Holds a maximum of 255 characters; default length is 10. Columns of this type are accessed somewhat faster than varchar columns because they use a fixed storage length. Choose char when you think the data entries in the column will be consistently close to the same size. You can create relationships between char columns and varchar columns. |
| datetime | Stored in two 4-byte integers: 4 bytes for the number of days before or after the base date of January 1, 1990, and 4 bytes for the number of milliseconds after midnight. The date segments of datetime values that represent dates prior to the base date are stored as negative values. Data values for datetime range from January 1, 1753, to December 31, 9999, to an accuracy of one three-hundredth second, or 3.33 milliseconds. Microsoft SQL Server rejects all values it cannot recognize as dates between 1753 and 9999. You can omit either portion of the value, but if you omit both, the datetime default of January 1, 1900, 12:00:00:000AM is supplied. If you omit the time portion of a datetime value, the default (12:00:00:000AM) is supplied. If you omit the date portion, the default (Jan 1 1900) is supplied. |
| decimal | An exact numeric data type that holds values from 1038 - 1 through -1038. The Precision property specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The Scale property specifies the maximum number of decimal digits that can be stored to the right of the decimal point; it must be less than or equal to the precision. Storage size varies according to the specified precision, and ranges from a minimum of 2 bytes to a maximum of 17 bytes. As a general rule, calculate the number of storage bytes required by dividing the precision by 2. For example, precision between 17 and 19 requires 9 storage bytes. For more information, refer to your database documentation. |
| float | Holds positive or negative floating-point numbers. By default, this data type has a 15-digit precision. The range of positive values is approximately from 2.23E - 308 through 1.79E 308; zero can be stored; and the range of negative values is approximately from -2.23E - 308 through -1.79E 308. Storage size is 8 bytes. All arithmetic operations except modulo are available with float.
For float data with an exponential component, enter a number (with or without a decimal point and a positive or negative sign), followed by |
| image | A variable-length data type that holds a maximum of through 2E31 - 1 (2,147,483,647) bytes of binary data; default length is 16. The image data type cannot be used for variables or parameters in stored procedures. When image data is entered with an odd number of bytes (but fewer than 255), it is padded with a leading zero. You cannot insert image values with an odd number of characters greater than 255 bytes. |
| int | Holds whole numbers from -2E31 (-2,147,483,648) through 2E31 - 1 (2,147,483,647). Storage size is 4 bytes. |
| money | Stores monetary values from -922,337,203,685,477.5808 through +922,337,203,685,477.5807, with accuracy to a ten-thousandth of a monetary unit. Money values are represented as double-precision integers. Storage size is 8 bytes. |
| numeric | Identical to the decimal data type in the Microsoft SQL Server 6.5 implementation. Both are provided for ANSI compatibility. See decimal data type, above. Caution You should use either decimal or numeric for exact numeric data types within a database to avoid conversion between decimal and numeric data types, and to allow relationships between columns with exact numeric data types. |
| real | A floating point column similar to float (see above). This column has 7-digit precision. The range of positive values is approximately from 1.18E - 38 through 3.40E 38; zero can be stored; and the range of negative values is approximately from -1.18E - 38 through -3.4E 38. Storage size is 4 bytes. |
| smalldatetime | A date and time data type that is less precise than datetime. Storage size is 4 bytes, consisting of one small integer for the number of days after January 1, 1900, and one small integer for the number of minutes past midnight. Data values for smalldatetime range from January 1, 1900, through June 6, 2079, with accuracy to the minute. |
| smallint | Holds whole numbers from -2E15 (-32,768) through 2E15 - 1 (32,767). Storage size is 2 bytes. |
| smallmoney | Stores monetary values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit. When smallmoney values are displayed, they are rounded up to two places. |
| sysname | Is the same as varchar(30) and should be assigned only to a column that references the name of a database object in the Microsoft SQL Server system tables such as sysbobjects. |
| text | Is a variable-length data type that can hold a maximum of 2E31 - 1 (2,147,483,647) characters; default length is 16. |
| timestamp | Automatically updated every time a row containing a timestamp column is inserted or updated. Values in timestamp columns are not datetime data, but binary(8) or varbinary(8), indicating the sequence of Microsoft SQL Server activity on the row. A table can have only one timestamp column. The timestamp data type has no relation to the system time — it is simply a monotonically-increasing counter whose values will always be unique within a database. |
| tinyint | Holds whole numbers from 0 through 255. Storage size is 1 byte. |
| varbinary | A variable-length binary data type that holds a maximum of 255 bytes of variable-length binary data; default length is 50. Storage size is the actual length of the data entered. Choose varbinary when you expect null values or a variation in data size. You can create relationships between binary columns and varbinary columns. |
| varchar | Holds a maximum of 255 characters; default length is 50. Storage size is not fixed with a varchar column. Instead, storage is defined by the actual size of the data entered (ignoring all trailing blanks). Choose varchar when you expect null values or a wide variation in data size. You can create relationships between char columns and varchar columns. |
When you select a data type, the column length is automatically defined. You can reset the length property for a column with a data type of binary, char, varbinary, or varchar if you want to increase or decrease the length of acceptable values in that column. For columns with other data types, the length is derived from the data type.
Note Changing the column length recreates the table in the database when you save the table or diagram. For more information about automatically recreating tables, see Working with Database Objects.
| Data type | Default length | Range of allowable length values |
| binary | 10 | 1 - 255 |
| bit | 1 | |
| char | 10 | 1 - 255 |
| datetime | 8 | |
| decimal | 9 | |
| float | 8 | |
| image | 16 | |
| int | 4 | |
| money | 8 | |
| numeric | 9 | |
| real | 4 | |
| smalldatetime | 4 | |
| smallint | 2 | |
| smallmoney | 4 | |
| sysname | 30 | 1 - 255 |
| text | 16 | |
| timestamp | 8 | |
| tinyint | 1 | |
| varbinary | 50 | 1 - 255 |
| varchar | 50 | 1 - 255 |
For most data types, the column precision is automatically defined. You can change the column precision for the decimal and numeric data types if you want to redefine the maximum number of digits these columns use. The precision of a numeric column refers to the maximum number of digits used by the selected data type. The precision of a non-numeric column generally refers to either the maximum length or the defined length of the column.
The Database Designer prevents you from changing the precision of a column that does not have one of these assigned data types.
Note Changing the column precision recreates the table in the database when you save the table or diagram. For more information about automatically recreating tables, see Working with Database Objects.
When you select a data type, the column scale by default is set to 0. The scale of a numeric column refers to the maximum number of digits to the right of the decimal point. For columns with approximate floating point numbers, the scale is undefined because the number of digits to the right of the decimal point is not fixed.
You can change the scale for a numeric or decimal column if you want to redefine the number of digits that can appear to the right of the decimal point.
Note Changing the column scale recreates the table in the database when you save the table or diagram. For more information about automatically recreating tables, see Working with Database Objects.
For each column in your table, you can specify whether to allow null values or disallow null values. A null value, or NULL, is not the same as zero (0) or blank; NULL means that no entry has been made. Its presence usually implies that the value is either unknown or undefined. For example, a null value in the price column of the titles table of the pubs sample database does not mean that the book has no price; it means that the price is unknown or has not been set.
If null values are not allowed, the user entering data in the table must enter a value in the column or the table row cannot be accepted in the database.
Note You cannot change this property on a primary key column. If Allow Nulls is not checked, but null values are stored in that column, Microsoft® SQL Server™ will generate an error message when you try to save the table. Also, Identity columns can't have null values.
Changing the null option on a new, non-key column recreates the table in the database when you save the table or diagram. For more information about automatically recreating tables, see Working with Database Objects.
For each column in your table, you can specify a default value that will be entered in the column if the user leaves it blank. If you do not assign a default value and the user leaves the column blank, then:
For details on setting the Allow Nulls option, see Changing the Null Option Assigned to a Column.
For text strings, enclose the value in single quotation marks ('); do not use double quotation marks (") because they are reserved for quoted identifiers. For example, type: 98036 or 'Paris, France'.
If your entry in the Default Value column replaces a bound default (which is shown without parentheses), the default will be unbound and the new value will replace it.
A global default is one that is defined for a specific database and is shared by columns of different tables. For example, suppose several of your tables have a quantity column. You can define a global default in your database that inserts a value of 1 in the quantity column whenever the user leaves that column blank in any table.
If a global default is bound to a column, you can specify a different default value for that column in a specific table. In such a case, the existing global default is unbound from the column before the new default value is bound to the column.
To redefine a global default
For details about assigning default values, see Assigning a Default Value to a Column.
You can change the identity properties of a column if you want to redefine the sequential numbers that are automatically generated and stored in that column when new rows are added to the table. You can set the identity properties on only one column per table.
Columns that have the Identity property contain system-generated sequential values that uniquely identify each row within a table (for example, employee identification numbers). When inserting values into a table with an identity column, Microsoft SQL Server automatically generates the next identifier based on the last used identity value (the Identity Seed property) and the increment value (the Identity Increment property) specified during the creation of the column.
The Identity property can be set only for a column with a data type of decimal, int, numeric, smallint, or tinyint; it must not allow null values; and it must not be bound by a default constraint.
To change a column’s identity properties
Note Only one column per table can be defined as an identity column.
For example, suppose you want to automatically generate a 5-digit Order ID for each row added to the orders table, beginning with 1000 and incremented by a value of 10. To do this, you would check the Identity property box, type an Identity Seed of 1000, and type an Identity Increment of 10.
If you change any of the identity properties for a table, the existing identity values will be preserved. Your new settings apply only to new rows that are added to the table.
Note If an identity column exists for a table with frequent deletions, gaps can occur between identity values. If you want to avoid such gaps, do not use the Identity property.
In SQL Server, the following options are available when enforcing referential integrity:
| For details about this option | See |
| Check existing data on creation | Checking Existing Data When Creating a Relationship |
| Enable relationship for INSERT and UPDATE statements | Disabling a Foreign Key Constraint with INSERT and UPDATE Statements |
| Enable relationship for replication | Disabling a Foreign Key Constraint for Replication |