The Database Designer is designed to support Microsoft SQL Server 2000, but is compatible with earlier versions as well. If you attempt to use a SQL Server 2000 feature when connected to version 7.0 or 6.5 of SQL Server, the server will report an error.
In a Microsoft Access project, names of objects:
- Can be up to 64 characters long.
- Can include any combination of letters, numbers, spaces, and special characters except a period (.), an exclamation point (!), an accent grave (`), double quotation mark ("), and brackets ([ ]).
- Can't begin with a leading space or a pound sign (#) character. Microsoft SQL Server uses a leading pound sign (#) in an object name to indicate that the object is a temporary object.
- Can't include control characters (ASCII values 0 through 31).
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.
To determine the case sensitivity of a server, execute the system 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 _iso.
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 on full-text indexing, refer to the SQL Server documentation.
Default values for column properties
The following default values are automatically added for a new column property:
| 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 |
| Data Type | 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. | 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. |
| Is RowGuid | Whether or not the identity column is used by SQL Server as a ROWGUID column. | No |
| Formula | The formula for a computed column. | Blank |
| Collation | The collating sequence that SQL Server applies by default to the column whenever the column values are used to sort rows of a query result (SQL Server 2000 only) . | The database default. |
| Description | The text description of the selected column stored as a SQL Server extended property (Microsoft SQL Server 2000 only). | Blank |
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. If user-defined data types exist for your database, they 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 information about creating user-defined data types in Microsoft SQL Server, see the SQL Server documentation.
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 Datat Type 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 the SQL Server documentation.
Microsoft SQL Server 2000 data types
SQL Server 2000 includes the following data type enhancements:
- sql_variant
New data type for storing values of different data types within an individual column.
- bigint
New data type for storing large (8-byte) integers.
For more information about data types, see 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, 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.
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.
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.
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.
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.
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.