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.
No comments:
Post a Comment