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 SQL Server 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.
Sparse Columns and Column Sets
Sparse columns are columns that contain null values for most of the rows in the table. Consider using sparse columns when 20 percent to 40 percent of the values in a column will be NULL. Sparse columns are normal columns that have an optimized storage for null values. For more information, see Using Sparse Columns.
Tables that use sparse columns can designate a column set to return all sparse columns in the table. A column set is an untyped XML representation that combines all the sparse columns of a table into a structured output. A column set is similar to a calculated column in that the column set is not physically stored in the table. For more information, see Using Column Sets.
To set column properties
To view column properties
To rename a column