Altering a Table

SQL Server allows existing tables to be modified in several ways. You can use ALTER TABLE to add or drop constraints from a table. Using the ALTER table command, you can make the following types of changes to an existing table:

● Change the datatype or NULL property of a single column.
● Add one or more new columns, with or without defining constraints for those columns.
● Add one or more constraints.
● Drop one or more constraints.
● Drop one or more columns.
● Enable or disable one or more constraints (only applies to CHECK and FOREIGN KEY constraints).
● Enable or disable one or more triggers.

Changing a Datatype
By using the ALTER COLUMN clause of ALTER TABLE, you can modify the datatype or NULL property of an existing column. But be aware of the following restrictions:

● The modified column can't be a text, image, ntext, or rowversion (timestamp) column.
● If the modified column is the ROWGUIDCOL for the table, only DROP ROWGUIDCOL is allowed; no datatype changes are allowed.
● The modified column can't be a computed or replicated column.
● The modified column can't have a PRIMARY KEY or FOREIGN KEY constraint defined on it.
● The modified column can't be referenced in a computed column.
● The modified column can't have the type changed to timestamp.
● If the modified column participates in an index, the only type changes that are allowed are increasing the length of a variable-length type (for example, VARCHAR(10) to VARCHAR(20)), changing nullability of the column, or both.
● If the modified column has a UNIQUE OR CHECK constraint defined on it, the only change allowed is altering the length of a variable-length column. For a UNIQUE constraint, the new length must be greater than the old length.
● If the modified column has a default defined on it, the only changes that are allowed are increasing or decreasing the length of a variable-length type, changing nullability, or changing the precision or scale.
● The old type of the column should have an allowed implicit conversion to the new type.
● The new type always has ANSI_PADDING semantics if applicable, regardless of the current setting.
● If conversion of an old type to a new type causes an overflow (arithmetic or size), the ALTER TABLE statement is aborted.

Here's the syntax and an example of using the ALTER COLUMN clause of the ALTER TABLE statement:

SYNTAX

ALTER TABLE table-name ALTER COLUMN column-name
{ type_name [ ( prec [, scale] ) ] [COLLATE <collation name> ]
[ NULL NOT NULL ]
{ADD DROP} ROWGUIDCOL }

EXAMPLE

/* Change the length of the emp_name column in the employee table from varchar(30) to varchar(50) */

ALTER TABLE employee
ALTER COLUMN emp_name varchar(50)

Adding a New Column
You can add a new column, with or without specifying column-level constraints. You can add only one column for each ALTER TABLE statement. If the new column doesn't allow NULLs and isn't an identity column, the new column must have a default constraint defined. SQL Server populates the new column in every row with a NULL, the appropriate identity value, or the specified default. If the newly added column is nullable and has a default constraint, the existing rows of the table are not filled with the default value, but rather with NULL values. You can override this restriction by using the WITH VALUES clause so that the existing rows of the table are filled with the specified default value.
Dropping a Column
You can use ALTER TABLE to remove one or more columns from a table. However, you can't drop the following columns:

● A replicated column.
● A column used in an index.
● A column used in a CHECK, FOREIGN KEY, UNIQUE, or PRIMARY KEY constraint.
● A column associated with a default defined with the DEFAULT keyword or bound to a default object.
● A column to which a rule is bound.

Dropping a column is accomplished using the following syntax:

ALTER TABLE table-name
DROP COLUMN column-name [, next-column-name]...

Downlosd