Database Designer Considerations for SQL Server Databases

The following guidelines outline SQL Server-specific features that you can use.

Below you will find information about:

• Case Sensitivity
  
• Full-Text Indexes
  
  
• Changing Column Properties

Case Sensitivity

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 you are using a case-sensitive database, you must think carefully when you refer to a database object by name, since two objects named "CUSTOMER" and "Customer" can exist in the same database.

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. You can run a stored procedure from the Query Analyzer.

Full-Text Indexes

A table can include full-text indexes. Database Designer and Table Designer provide limited support for manipulating tables with full-text indexes.  You cannot use Database Designer or Table Designer to create a full-text index, but if you modify a table with full-text indexes, the Database Designer or Table Designer warns you if your modification affects the full-text index.  In most cases, however, when you save your modifications, the Database Designer or Table Designer will be able to reestablish the table's full-text indexes. 

For more information, see Full-Text Indexing Support.

Changing Column Properties

For information on the procedure you use to set column properties, see Setting Column Properties.

The following items contain information specific to Microsoft SQL Server databases:

• Default Values for Data Types
  
  
• Changing the Data Type Assigned to a Column
  
  
• Changing the Column Length
  
  
• Changing the Column Precision
  
  
• Changing the Column Scale
  
  
• Changing the Null Option Assigned to a Column
  
  
• Assigning a Default Value to a Column
  
  
• Redefining a Global Default
  
  
• Changing a Column's Identity Properties

Default Values for Data Types

The following default values are automatically added for a new column:

Changing the Data Type Assigned to a Column

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 Data type column.

You can choose the appropriate data type for the information you want to store in the column. User-defined data types appear at the end of the data type 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)."

Special considerations for assigning a user-defined data type to a column

• Selecting a data type automatically sets the length, precision, and scale for the column based on the data type's definition. You cannot change these settings for user-defined data types.
  
  
• You can change the Allow Nulls setting only if the user-defined data type allows null values.

For more information, see Creating User-Defined Data Types.

Note   Changing the data type recreates the table in the database when you save the table or diagram.

Caution   If you change the data type of a column that 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 more information, see Data Types.

Changing the Column Length

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, nchar, nvarchar, 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.

Changing the Column Precision

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 whose data type is not decimal or numeric.

Changing the Column Scale

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.

Changing the Null Option Assigned to a Column

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. Also, identity columns cannot have null values.  That is, you cannot create or modify a column so that its Identity setting is Yes and its Nulls Allowed setting is Yes.

Assigning a Default Value to a Column

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:

• If you set the option to allow null values, NULL will be inserted into the column.
  
  
• If you did not set the option to allow null values, the column will remain blank, but you will not be able to save the row until you supply a value for the 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.

Redefining a Global Default

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

1. In your database diagram, assign a new default value to the column you want to change.
   
   
2. A message prompts you to permanently unbind the existing default in order for the new default to be applied. Choose OK.

Changing a Column's Identity Properties

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 whose data type is decimal, int, numeric, smallint, bigint, or tinyint and that disallows null values.

To change a column's identity properties

1. In your database diagram, select the table in which you want to change the identity properties of a column.
   
   
2. If you are not already in Standard view, right-click the table and choose Table View, then Standard from the shortcut menu.
   
   
3. If the Allow Nulls property is selected, clear the check box.
   
   
4. Select the Identity cell for the column whose values you want to automatically increment.

   Note   Only one column per table can be defined as an identity column.

5. Type a value in the Identity Seed cell. This value will be assigned to the first row in the table. If you leave this cell blank, the value 1 will be assigned by default.
   
   
6. Type a value in the Identity Increment cell. This value is the increment that will be added to the Identity Seed for each subsequent row. If you leave this cell blank, the value 1 will be assigned by default.

For example, suppose you want to automatically generate a 5-digit Order ID for each row added to the orders table, beginning with 10000 and incremented by a value of 10. To do this, you would select the Identity property box, type an Identity Seed of 10000, 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.

See Also

Constraints | Creating a Relationship Between Tables | Creating an Index | Database Designer | Deleting a Check Constraint | Enforcing Referential Integrity Between Tables | Setting Column Properties | Table Relationships