Modifying Column Properties
Each column in a table has a set of properties, such as name, data type, nullability, and data length. The complete set of properties for a column makes up the definition of the column in a table.
Column Data Type
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 (Transact-SQL).
Column Data Length
When you select a data type, the length is automatically defined. You can increase or decrease the length property only for a column with a data type of binary, char, nchar, varbinary, varchar, or nvarchar. 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. You cannot change the length of a column defined with a PRIMARY KEY or FOREIGN KEY constraint.
Column Precision
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 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 that these columns use. The Database Engine prevents you from changing the precision of a column that does not have one of these assigned data types.
Column Scale
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.
Column Nullability
A column can be defined to either allow for 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, follow these steps:
- Add a new column with a DEFAULT definition that inserts a valid value instead of NULL.
- Copy the data in the old, existing, column to the new column.
- Delete the old column.
An existing column that does not allow for null values can be changed to allow for null values, unless a PRIMARY KEY constraint is defined on the column.
To set column properties
Modifying Columns (Visual Database Tools)
To view column properties
Table Column Properties (SQL Server Management Studio)
To rename a column
How to: Rename Columns (Visual Database Tools)
See Also
Other Resources
Precision, Scale, and Length (Transact-SQL)