ALTER Column in SQL Server

ALTER COLUMN

It is possible to change the data type of a column in a table after it has been created.

Specifies that the named column is to be changed or altered.

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 statistics generated by the CREATE STATISTICS statement unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, and the new size is equal to or greater than the old size, or if the column is changed from not null to null. First, remove the statistics using the DROP STATISTICS statement. Statistics that are automatically generated by the query optimizer are automatically dropped by ALTER 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 text, ntext and image columns can be changed only in the following ways:

o    text to varchar(max), nvarchar(max), or xml

o    ntext to varchar(max), nvarchar(max), or xml

o    image to varbinary(max)

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. For more information, see CAST and CONVERT (Transact-SQL). Reducing the precision or scale of a column may cause data truncation.

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.

If the column being modified is encrypted using ENCRYPTED WITH, you can change the datatype to a compatible datatype (such as INT to BIGINT) but you cannot change any encryption settings.

column_name

Is the name of the column to be altered, added, or dropped. column_name can be a maximum of 128 characters. For new columns, column_name can be omitted for columns created with a timestamp data type. The name timestamp is used if no column_name is specified for a timestamp data type column.

[ type_schema_name. ] type_name

Is the new data type for the altered column, or the data type for the added column. type_name cannot be specified for existing columns of partitioned tables. type_name can be any one of the following:

·         A SQL Server system data type.

·         An alias data type based on a SQL Server system data type. Alias data types are created with the CREATE TYPE statement before they can be used in a table definition.

·         A .NET Framework user-defined type, and the schema to which it belongs. .NET Framework user-defined types are created with the CREATE TYPE statement before they can be used in a table definition.

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.

·         The current setting for SET ARITHABORT is ignored. ALTER TABLE operates as if ARITHABORT is set to ON.