Syntax
ALTER TABLE table ALTER COLUMN column [ WITH {CHECK | NOCHECK} ]
ADD column_definition [,...n] [;]
column_definition:
column data_type
[COLLATE collation_name ]
[NULL | NOT NULL]
[CONSTRAINT constraint] DEFAULT constant_expression ]
[IDENTITY [ ( seed ,increment ) ] [ NOT FOR REPLICATION ]
[ROWGUIDCOL] [column_constraint [ ...n ] ]
data_type:
[type_schema.] type
[ ( precision [ , scale ] | max |
[ { CONTENT | DOCUMENT } ] xml_schema_collection ) ]
table:
database.schema.table
database..table
schema.table
Arguments:
ALL - All constraints or triggers in the table are enabled or disabled.
column - A maximum of 128 characters. The name 'timestamp' is used if no name is specified for a timestamp data type column.
TEXTIMAGE_ON - Specifies an alternate storage filegroup for columns of type: text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max), and CLR user-defined type.
CONTENT - Allow multiple top-level elements in each instance of the xml data type.
DOCUMENT - Allow only one top-level element in each instance of the xml data type.
DEFAULT - A value provided for the column when nothing is explicitly supplied during an insert.
IDENTITY - An identity column, one per table: tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0)
ONLINE - Make underlying tables and associated indexes are available during the index operation.
NOT FOR REPLICATION - Do not enforce constraints for the replication agent (IDENTITY, FOREIGN KEY and CHECK constraints.)
CONSTRAINT - Define a PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, or CHECK constraint.
NULL / NOT NULL - Whether the column can accept null values.
CLUSTERED | NONCLUSTERED - The type of index is created for a PRIMARY KEY or UNIQUE constraint. PRIMARY KEY constraints default to CLUSTERED, and UNIQUE constraints default to NONCLUSTERED.
FOREIGN KEY REFERENCES - A constraint to provide referential integrity for the data, requires a UNIQUE INDEX on the referenced table.
max - Applies only to the varchar, nvarchar, and varbinary data types for storing 2^31-1 bytes of character / binary / Unicode data.
WITH CHECK / WITH NOCHECK - Is data in the table validated against the new FOREIGN KEY or CHECK constraint.
To modify a table column you may need to: Delete statistics, Remove Primary, Foreign Key or Check constraints, Delete Indexes.
The following columns may not be modified:
ROWGUIDCOL, timestamp data type, computed column (or used in a computed column), associated with a DEFAULT definition (may still adjust length, precision & scale.) you cannot modify an existing column to add the IDENTITY property.
Example
-- Add a column ALTER TABLE MyTable ADD MyNewColumn VARCHAR(45) NULL ;
GO
"At a good table we may go to school" - Thomas Fuller
Related commands:
CREATE TABLE
DROP TABLE
Equivalent Oracle command: ALTER TABLE