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:
The Database Designer is designed to support Microsoft SQL Server versions 6.5 and 7.0.
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.
Through Index Server 2.0 shipped with SQL Server 7.0, you can implement full-text indexing. However, Database Designer provides limited support for tables that contain full-text indexes. For example, when you add a table to a diagram or design a table that contains a full-text index, you will not be able to save changes that you make to that table. You can modify the table if you remove the index from the table before you attempt to use it in Database Designer.
For more information on full-text indexing, refer to the SQL Server 7.0 documentation.
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:
Note In Microsoft SQL Server version 7.0, you have the ability to add computed columns in your tables. Database Designer does not support this feature. If you modify a table that has computed columns, these columns may be converted to actual columns. For more information, see "Using Computed and Calculated Columns" in your SQL Server documentation.
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 (selected) |
| 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 selected) |
| 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.
For information on Microsoft SQL Server data types, see "Data Types" in SQL Server Books Online or Database Developer’s Companion.
Version 7.0 of SQL Server includes the following data type enhancements:
Expanded maximum length of character and binary data to 8,000 bytes.
New Unicode data types.
Maximum length of (nchar, nvarchar) character data to 4,000 characters.
New data type for storing globally unique identifier (GUID).
For more information, see "New Data Types" in your SQL Server documentation.
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.
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 selected, 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 |