ALTER TABLE

Modify a table definition. (columns, constraints, partitions, enable/disable constraint/trigger.)

Syntax
      ALTER TABLE table ALTER COLUMN column [ WITH {CHECK | NOCHECK} ]
         ADD column_definition [,...n] [;]

      ALTER TABLE table ALTER COLUMN column 
         DROP COLUMN column [,...n] [;]

      ALTER TABLE table ALTER COLUMN column [ WITH {CHECK | NOCHECK} ]
         ADD computed_column_definition [,...n] [;]
    
      ALTER TABLE table ALTER COLUMN column [ WITH {CHECK | NOCHECK} ]
         ADD table_constraint [,...n] [;]

      ALTER TABLE table ALTER COLUMN column
         DROP [CONSTRAINT] constraint [,...n] [;]

      ALTER TABLE table ALTER COLUMN column
         DROP [WITH ( drop_clustered_constraint [,...n] ) ][,...n] [;]
  
      ALTER TABLE table ALTER COLUMN column
         [WITH { CHECK | NOCHECK }] { CHECK | NOCHECK } CONSTRAINT 
            { ALL | constraint [,...n] } [;]
        
      ALTER TABLE table ALTER COLUMN column
         {ENABLE | DISABLE} TRIGGER  { ALL | trigger [,...n] } [;]

      ALTER TABLE table ALTER COLUMN column 
         SWITCH [ PARTITION source_partition_number_expression ]
            TO [schema.] target_table 
                [PARTITION target_partition_number_expression ] [;]

      ALTER TABLE table ALTER COLUMN column 
      [type_schema.] type [ ( 
                      { precision [ , scale ] | max | xml_schema_collection }
                          ) ] 
         [COLLATE collation_name ] 
            [NULL | NOT NULL] [;]

      ALTER TABLE table ALTER COLUMN column 
         {ADD | DROP } { ROWGUIDCOL | PERSISTED } [;]

      ALTER TABLE table ALTER COLUMN column
         DROP NOT FOR REPLICATION [;]


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.

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.)

Examples

-- Add a new column (will allow nulls by default)
ALTER TABLE MyTable 
ADD MyNewColumn VARCHAR(45) ;
GO

-- Disallow nulls on an existing column
ALTER TABLE MyTable 
ALTER COLUMN MyNewColumn VARCHAR(45) NOT NULL;
GO

-- Add a new column (and dont allow nulls)
ALTER TABLE MyTable 
ADD MyNewCol2 int NOT NULL ;
GO
 
--Add a default constraint
ALTER TABLE MyTable 
ADD CONSTRAINT MyNewDefault
DEFAULT 50 FOR MyNewCol2 ;
GO

-- drop a column
ALTER TABLE MyTable 
DROP COLUMN MyOLDColumn;

-- add a date/time column defaulting to today
ALTER TABLE MyTable
ADD MyDateCol smalldatetime NULL
CONSTRAINT MyDateDflt
DEFAULT GETDATE() WITH VALUES ;
GO

"At a good table we may go to school" - Thomas Fuller

Related commands:

CREATE TABLE
DROP TABLE

Equivalent Oracle command:

ALTER TABLE