ALTER TABLE add column

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


© Copyright SS64.com 1999-2014
Some rights reserved