Alter Column - Rules to remember

The modified column cannot be any one of the following:

·         A column with a timestamp data type.

·         The ROWGUIDCOL for the table.

·         A computed column or used in a computed column.

·         Used in a PRIMARY KEY or [FOREIGN KEY] REFERENCES constraint.

·         Used in a CHECK or UNIQUE constraint. However, changing the length of a variable-length column used in a CHECK or UNIQUE constraint is allowed.

·         Associated with a default definition. However, the length, precision, or scale of a column can be changed if the data type is not changed. 

·         The data type of a column of a partitioned table cannot be changed.

·         The data type of columns included in an index cannot be changed unless the column is a varchar, nvarchar, or varbinary data type, and the new size is equal to or larger than the old size.

·         A columns included in a primary key constraint, cannot be changed from NOT NULL to NULL.

Some data type changes may cause a change in the data. For example, changing an nchar or nvarchar column to char or varchar may cause the conversion of extended characters. Reducing the precision or scale of a column may cause data truncation.  

The following are criteria for type_name of an altered column:

·         The previous data type must be implicitly convertible to the new data type.

• type_name cannot be timestamp.
• ANSI_NULL defaults are always on for ALTER COLUMN; if not specified, the column is nullable.
• ANSI_PADDING padding is always ON for ALTER COLUMN.
•  If the modified column is an identity column, new_data_type must be a data type that supports the identity property.