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
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 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
| Data Type | Description | Default value |
|---|---|---|
| Column Name | The name of a column in a table. Column names must conform to rules for identifiers and must be unique in the table. | Blank |
| Datatype | The data type of the column. System- or |
Character (char) |
| Length | The maximum number of digits (for numeric data types) or characters allowed for values in the column. | Differs for different data types (e.g., 10 for Character, 50 for VARBINARY) |
| 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. Applies only to DECIMAL and NUMERIC data types. | 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 the Identity setting is No, Identity Seed is blank. If the Identity setting is Yes, Identity Seed defaults to 1. | Blank or 1. |
| 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 the Identity setting is No, Identity Increment is blank. If the Identity setting is Yes, Identity Increment defaults to 1. | Blank or 1. |
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.
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
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
Assigning a Default Value to a Column
For each column in your table, you can specify a
- 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 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
- In your database diagram, assign a new default value to the column you want to change.
- 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
- In your database diagram, select the table in which you want to change the identity properties of a column.
- If you are not already in Standard view, right-click the table and choose Table View, then Standard from the shortcut menu.
- If the Allow Nulls property is selected, clear the check box.
- 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.
- 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.
- 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