To set column propertiesEach column in a table has a set of properties, such as name, data type, nullability, and data length. The entire set of properties for a column makes up the definition of the column in a table.
The column properties can be specified directly in a database table by using a database diagram. Three column properties are required before you can create a table in the database:
The properties of a column can be changed, for example, by renaming it, altering its length, specifying a default value, and so on.
The data type of an existing column can be changed provided that the existing data in the column can be implicitly converted to the new data type. For more information, see ALTER TABLE.
When you select a data type, length is defined automatically. You can change the length property only for a column with a data type of binary, char, nchar, varbinary, varchar, or nvarchar 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 and cannot be changed. If the new specified length is smaller than the original column length, all values in the column that exceed the new length are truncated without any warning. It is not possible to change the length of a column defined with a PRIMARY KEY or FOREIGN KEY constraint.
Note Changing the column data length re-creates the table in the database when you save the table or database diagram using SQL Server Enterprise Manager.
The precision of a numeric column is the maximum number of digits used by the selected data type. The precision of a nonnumeric column generally refers to either the maximum length or the defined length of the column.
For all data types except decimal and numeric, precision is defined automatically. 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. SQL Server Enterprise Manager prevents you from changing the precision of a column that does not have one of these assigned data types.
Note Changing the column precision re-creates the table in the database when you save the table or database diagram using SQL Server Enterprise Manager.
The scale of a numeric or decimal column is to the maximum number of digits to the right of the decimal point. When you select a data type, the column scale by default is set to 0. 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 re-creates the table in the database when you save the table or diagram using SQL Server Enterprise Manager.
A column can be defined to either allow or disallow null values. By default, a column permits null values. An existing column can be changed to disallow null values only if no existing null values exist in the column and there is no existing index created on the column. To disallow null values in an existing column that contains null values:
An existing column that does not allow null values can be changed to allow null values unless a PRIMARY KEY constraint is defined on the column.
Note Changing the nullability on a new, nonkey column re-creates the table in the database when you save the table or database diagram using Database Diagrams within SQL Server Enterprise Manager.
To set column properties
To view column properties
To rename a column
| Changing a Table View | Working with Database Objects |
| Precision, Scale, and Length |