ALTER TABLE (Transact-SQL)

Syntax

ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name 
{ 
    ALTER COLUMN column_name 
    { 
        [ type_schema_name. ] type_name [ ( { precision [ ,scale ] 
            | max | xml_schema_collection } ) ] 
        [ COLLATE collation_name ] 
        [ SPARSE | NULL | NOT NULL ] 
    | {ADD | DROP } 
        { ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION | SPARSE }
    } 
        | [ WITH { CHECK | NOCHECK } ]

    | ADD 
    { 
        <column_definition>
      | <computed_column_definition>
      | <table_constraint> 
      | <column_set_definition> 
    } [ ,...n ]

    | DROP 
    { 
        [ CONSTRAINT ] constraint_name 
        [ WITH ( <drop_clustered_constraint_option> [ ,...n ] ) ]
        | COLUMN column_name 
    } [ ,...n ] 

    | [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT 
        { ALL | constraint_name [ ,...n ] } 

    | { ENABLE | DISABLE } TRIGGER 
        { ALL | trigger_name [ ,...n ] }

    | { ENABLE | DISABLE } CHANGE_TRACKING 
        [ WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } ) ]

    | SWITCH [ PARTITION source_partition_number_expression ]
        TO target_table 
        [ PARTITION target_partition_number_expression ]

    | SET ( FILESTREAM_ON = { partition_scheme_name | filegroup | 
                "default" | "NULL" } )

    | REBUILD 
      [ [PARTITION = ALL]
        [ WITH ( <rebuild_option> [ ,...n ] ) ] 
      | [ PARTITION = partition_number 
           [ WITH ( <single_partition_rebuild_option> [ ,...n ] ) ]
        ]
      ]

    | (<table_option>)
}
[ ; ]

<column_set_definition> ::= 
    column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS

<drop_clustered_constraint_option> ::=  
    { 
        MAXDOP = max_degree_of_parallelism      | ONLINE = {ON | OFF }
      | MOVE TO { partition_scheme_name (column_name) | filegroup
          | "default" }
    }
<table_option> ::=
    {
        SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )
    }

<single_partition_rebuild__option> ::=
{
      SORT_IN_TEMPDB = { ON | OFF }
    | MAXDOP =max_degree_of_parallelism
    | DATA_COMPRESSION = { NONE | ROW | PAGE} }
}

Arguments

• database_name
  Is the name of the database in which the table was created.

• schema_name
  Is the name of the schema to which the table belongs.

• table_name
  Is the name of the table to be altered. If the table is not in the current database or is not contained by the schema owned by the current user, the database and schema must be explicitly specified.

• ALTER COLUMN
  Specifies that the named column is to be changed or altered. For more information, see sp_dbcmptlevel (Transact-SQL).

  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 an index, unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, the new size is equal to or larger than the old size, and the index is not the result of a PRIMARY KEY constraint.

  • 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:

    • text to varchar(max), nvarchar(max), or xml

    • ntext to varchar(max), nvarchar(max), or xml

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

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

  Note

  If the COLLATE clause is not specified, changing the data type of a column will cause a collation change to the default collation of the database.

• precision
  Is the precision for the specified data type. For more information about valid precision values, see Precision, Scale, and Length (Transact-SQL).

• scale
  Is the scale for the specified data type. For more information about valid scale values, see Precision, Scale, and Length (Transact-SQL).

• max
  Applies only to the varchar, nvarchar, and varbinary data types for storing 2^31-1 bytes of character, binary data, and of Unicode data.

• xml_schema_collection
  Applies only to the xml data type for associating an XML schema with the type. Before typing an xml column to a schema collection, the schema collection must first be created in the database by using CREATE XML SCHEMA COLLECTION.

• COLLATE < collation_name >
  Specifies the new collation for the altered column. If not specified, the column is assigned the default collation of the database. Collation name can be either a Windows collation name or a SQL collation name. For a list and more information, see Windows Collation Name (Transact-SQL) and SQL Server Collation Name (Transact-SQL).

  The COLLATE clause can be used to change the collations only of columns of the char, varchar, nchar, and nvarchar data types. To change the collation of a user-defined alias data type column, you must execute separate ALTER TABLE statements to change the column to a SQL Server system data type and change its collation, and then change the column back to an alias data type.

  ALTER COLUMN cannot have a collation change if one or more of the following conditions exist:

  • If a CHECK constraint, FOREIGN KEY constraint, or computed columns reference the column changed.

  • If any index, statistics, or full-text index are created on the column. Statistics created automatically on the column changed are dropped if the column collation is changed.

  • If a schema-bound view or function references the column.

  For more information, see COLLATE (Transact-SQL).

• SPARSE | NULL | NOT NULL
  Specifies whether the column is a sparse column or can accept null values. If the column to be altered is a sparse column, you must explicitly specify this property or the column will revert to a nonsparse column. Sparse columns cannot be designated as NOT NULL. Converting a column from sparse to nonsparse or from nonsparse to sparse locks the table for the duration of the command execution.

  For additional restrictions and more information about sparse columns and nullability, see Using Sparse Columns.

  Columns that do not allow null values can be added with ALTER TABLE only if you specify a default value for the column or if the entire table is empty. NOT NULL can be specified for computed columns only if PERSISTED is also specified. If the new column allows null values and no default is specified, the new column contains a null value for each row in the table. If the new column allows null values and a default definition is added with the new column, WITH VALUES can be used to store the default value in the new column for each existing row in the table.

  If the new column does not allow null values and the table is not empty, a DEFAULT definition must be added with the new column, and the new column automatically loads with the default value in the new columns in each existing row.

  NULL can be specified in ALTER COLUMN to force a NOT NULL column to allow null values, except for columns in PRIMARY KEY constraints. NOT NULL can be specified in ALTER COLUMN only if the column contains no null values. The null values must be updated to some value before the ALTER COLUMN NOT NULL is allowed, for example:

  UPDATE MyTable SET NullCol = N'some_value' WHERE NullCol IS NULL
  ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL
  

  When you create or alter a table with the CREATE TABLE or ALTER TABLE statements, the database and session settings influence and possibly override the nullability of the data type that is used in a column definition. We recommend that you always explicitly define a column as NULL or NOT NULL for noncomputed columns.

  If you add a column with a user-defined data type, we recommend that you define the column with the same nullability as the null property of the user-defined data type and specify a default value for the column. For more information, see CREATE TABLE (Transact-SQL).

  Note

  If NULL or NOT NULL is specified with ALTER COLUMN, new_data_type [(precision [, scale ])] must also be specified. If the data type, precision, and scale are not changed, specify the current column values.

• [ {ADD | DROP} ROWGUIDCOL ]
  Specifies the ROWGUIDCOL property is added to or dropped from the specified column. ROWGUIDCOL indicates that the column is a row GUID column. Only one uniqueidentifier column per table can be designated as the ROWGUIDCOL column, and the ROWGUIDCOL property can be assigned only to a uniqueidentifier column. ROWGUIDCOL cannot be assigned to a column of a user-defined data type.

  ROWGUIDCOL does not enforce uniqueness of the values that are stored in the column and does not automatically generate values for new rows that are inserted into the table. To generate unique values for each column, either use the NEWID function on INSERT statements or specify the NEWID function as the default for the column.

• [ {ADD | DROP} PERSISTED ]
  Specifies that the PERSISTED property is added to or dropped from the specified column. The column must be a computed column that is defined with a deterministic expression. For columns specified as PERSISTED, the Database Engine physically stores the computed values in the table and updates the values when any other columns on which the computed column depends are updated. By marking a computed column as PERSISTED, you can create indexes on computed columns defined on expressions that are deterministic, but not precise. For more information, see Creating Indexes on Computed Columns.

  Any computed column that is used as a partitioning column of a partitioned table must be explicitly marked PERSISTED.

• DROP NOT FOR REPLICATION
  Specifies that values are incremented in identity columns when replication agents perform insert operations. This clause can be specified only if column_name is an identity column. For more information, see Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION.

• SPARSE
  Specifies that the column to be added or dropped is a sparse column. The storage of sparse columns is optimized for null values. Sparse columns cannot be designated as NOT NULL. Converting a column from sparse to nonsparse or from nonsparse to sparse locks the table for the duration of the command execution.

  Note

  You must specify the SPARSE property each time you alter the column, or the column will revert to a nonsparse column.

  For additional restrictions and more information about sparse columns, see Using Sparse Columns.

• WITH CHECK | WITH NOCHECK
  Specifies whether the data in the table is or is not validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint. If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for re-enabled constraints.

  If you do not want to verify new CHECK or FOREIGN KEY constraints against existing data, use WITH NOCHECK. We do not recommend doing this, except in rare cases. The new constraint will be evaluated in all later data updates. Any constraint violations that are suppressed by WITH NOCHECK when the constraint is added may cause future updates to fail if they update rows with data that does not comply with the constraint.

  The query optimizer does not consider constraints that are defined WITH NOCHECK. Such constraints are ignored until they are re-enabled by using ALTER TABLE table CHECK CONSTRAINT ALL.

• ADD
  Specifies that one or more column definitions, computed column definitions, or table constraints are added.

• DROP { [ CONSTRAINT ] constraint_name | COLUMN column_name }
  Specifies that constraint_name or column_name is removed from the table. Multiple columns and constraints can be listed.

  The user-defined or system-supplied name of the constraint can be determined by querying the sys.check_constraint, sys.default_constraints, sys.key_constraints, and sys.foreign_keys catalog views.

  A PRIMARY KEY constraint cannot be dropped if an XML index exists on the table.

  A column cannot be dropped when it is:

  • Used in an index.

  • Used in a CHECK, FOREIGN KEY, UNIQUE, or PRIMARY KEY constraint.

  • Associated with a default that is defined with the DEFAULT keyword, or bound to a default object.

  • Bound to a rule.

  Note

  Dropping a column does not reclaim the disk space of the column. You may have to reclaim the disk space of a dropped column when the row size of a table is near, or has exceeded, its limit. Reclaim space by creating a clustered index on the table or rebuilding an existing clustered index by using ALTER INDEX.

• WITH <drop_clustered_constraint_option>
  Specifies that one or more drop clustered constraint options are set.

• MAXDOP = max_degree_of_parallelism
  Overrides the max degree of parallelism configuration option only for the duration of the operation. For more information, see max degree of parallelism Option.

  Use the MAXDOP option to limit the number of processors used in parallel plan execution. The maximum is 64 processors.

  max_degree_of_parallelism can be one of the following values:

  • 1
    Suppresses parallel plan generation.

  • >1
    Restricts the maximum number of processors used in a parallel index operation to the specified number.

  • 0 (default)
    Uses the actual number of processors or fewer based on the current system workload.

  For more information, see Configuring Parallel Index Operations.

  Note

  Parallel index operations are available only in SQL Server Enterprise, Developer, and Evaluation editions.

• ONLINE = { ON | OFF }
  Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. The default is OFF. REBUILD can be performed as an ONLINE operation.

  • ON
    Long-term table locks are not held for the duration of the index operation. During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. This enables queries or updates to the underlying table and indexes to continue. At the start of the operation, a Shared (S) lock is held on the source object for a very short time. At the end of the operation, for a short time, an S (Shared) lock is acquired on the source if a nonclustered index is being created; or an SCH-M (Schema Modification) lock is acquired when a clustered index is created or dropped online and when a clustered or nonclustered index is being rebuilt. ONLINE cannot be set to ON when an index is being created on a local temporary table. Only single-threaded HEAP rebuild operation is allowed.

  • OFF
    Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements. Allows multi-threaded HEAP rebuild operations.

  For more information, see How Online Index Operations Work. For more information about locks, see Lock Modes.

  Note

  Online index operations are available only in SQL Server Enterprise, Developer, and Evaluation editions.

• MOVE TO { partition_scheme_name**(** column_name [ 1**,** ... n] ) | filegroup | "default" }
  Specifies a location to move the data rows currently in the leaf level of the clustered index. The table is moved to the new location.

  Note

  In this context, default is not a keyword. It is an identifier for the default filegroup and must be delimited, as in MOVE TO "default" or MOVE TO [default]. If "default" is specified, the QUOTED_IDENTIFIER option must be ON for the current session. This is the default setting. For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).

• { CHECK | NOCHECK } CONSTRAINT
  Specifies that constraint_name is enabled or disabled. This option can only be used with FOREIGN KEY and CHECK constraints. When NOCHECK is specified, the constraint is disabled and future inserts or updates to the column are not validated against the constraint conditions. DEFAULT, PRIMARY KEY, and UNIQUE constraints cannot be disabled.

• ALL
  Specifies that all constraints are either disabled with the NOCHECK option or enabled with the CHECK option.

• { ENABLE | DISABLE } TRIGGER
  Specifies that trigger_name is enabled or disabled. When a trigger is disabled it is still defined for the table; however, when INSERT, UPDATE, or DELETE statements are executed against the table, the actions in the trigger are not performed until the trigger is re-enabled.

• ALL
  Specifies that all triggers in the table are enabled or disabled.

• trigger_name
  Specifies the name of the trigger to disable or enable.

• { ENABLE | DISABLE } CHANGE_TRACKING
  Specifies whether change tracking is enabled disabled for the table. By default, change tracking is disabled.

  This option is available only when change tracking is enabled for the database. For more information, see ALTER DATABASE SET Options (Transact-SQL).

  To enable change tracking, the table must have a primary key.

• WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } )
  Specifies whether the Database Engine tracks which change tracked columns were updated. The default value is OFF.

• SWITCH [ PARTITION source_partition_number_expression ] TO [ schema_name**.** ] target_table [ PARTITION target_ partition_number_expression ]
  Switches a block of data in one of the following ways:

  • Reassigns all data of a table as a partition to an already-existing partitioned table.

  • Switches a partition from one partitioned table to another.

  • Reassigns all data in one partition of a partitioned table to an existing non-partitioned table.

  If table is a partitioned table, source_partition_number_expression must be specified. If target_table is partitioned, target_partition_number_expression must be specified. If reassigning a table's data as a partition to an already-existing partitioned table, or switching a partition from one partitioned table to another, the target partition must exist and it must be empty.

  If reassigning one partition's data to form a single table, the target table must already be created and it must be empty. Both the source table or partition, and the target table or partition, must reside in the same filegroup. The corresponding indexes, or index partitions, must also reside in the same filegroup. Many additional restrictions apply to switching partitions. For more information, see Transferring Data Efficiently by Using Partition Switching. table and target_table cannot be the same. target_table can be a multi-part identifier.

  source_partition_number_expression and target_partition_number_expression are constant expressions that can reference variables and functions. These include user-defined type variables and user-defined functions. They cannot reference Transact-SQL expressions.  

  Note

  You cannot use the SWITCH statement on replicated tables.

• SET ( FILESTREAM_ON = { partition_scheme_name | filestream_filegroup_name | "default" | "NULL" })
  Specifies where FILESTREAM data is stored.

  ALTER TABLE with the SET FILESTREAM_ON clause will succeed only if the table has no FILESTREAM columns. The FILESTREAM columns can be added by using a second ALTER TABLE statement.

  If partition_scheme_name is specified, the rules for CREATE TABLE apply. The table should already be partitioned for row data, and its partition scheme must use the same partition function and columns as the FILESTREAM partition scheme.

  filestream_filegroup_name specifies the name of a FILESTREAM filegroup. The filegroup must have one file that is defined for the filegroup by using a CREATE DATABASE or ALTER DATABASE statement, or an error is raised.

  "default" specifies the FILESTREAM filegroup with the DEFAULT property set. If there is no FILESTREAM filegroup, an error is raised.

  "NULL" specifies that all references to FILESTREAM filegroups for the table will be removed. All FILESTREAM columns must be dropped first. You must use SET FILESTREAM_ON**="NULL"** to delete all FILESTREAM data that is associated with a table.

• SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )
  Specifies the allowed methods of lock escalation for a table.

  • AUTO
    This option allows SQL Server Database Engine to select the lock escalation granularity that is appropriate for the table schema.

    • If the table is partitioned, lock escalation will be allowed to partition. After the lock is escalated to the partition level, the lock will not be escalated later to TABLE granularity.

    • If the table is not partitioned, the lock escalation will be done to the TABLE granularity.

  • TABLE
    Lock escalation will be done at table-level granularity regardless whether the table is partitioned or not partitioned. This behavior is the same as in SQL Server 2005. TABLE is the default value.

  • DISABLE
    Prevents lock escalation in most cases. Table-level locks are not completely disallowed. For example, when you are scanning a table that has no clustered index under the serializable isolation level, Database Engine must take a table lock to protect data integrity.

• REBUILD
  Use the REBUILD WITH syntax to rebuild an entire table including all the partitions in a partitioned table. If the table has a clustered index, the REBUILD option rebuilds the clustered index. REBUILD can be performed as an ONLINE operation.

  Use the REBUILD PARTITION syntax to rebuild a single partition in a partitioned table.

• PARTITION = ALL
  Rebuilds all partitions when changing the partition compression settings.

• REBUILD WITH ( <rebuild_option> )
  All options apply to a table with a clustered index. If the table does not have a clustered index, the heap structure is only affected by some of the options.

  For complete descriptions of the rebuild options, see index_option (Transact-SQL).

• DATA_COMPRESSION
  Specifies the data compression option for the specified table, partition number, or range of partitions. The options are as follows:

  • NONE
    Table or specified partitions are not compressed.

  • ROW
    Table or specified partitions are compressed by using row compression.

  • PAGE
    Table or specified partitions are compressed by using page compression.

  To rebuild multiple partitions at the same time, see index_option (Transact-SQL). If the table does not have a clustered index, changing the data compression rebuilds the heap and the nonclustered indexes. For more information about compression, see Creating Compressed Tables and Indexes.

• column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
  Is the name of the column set. A column set is an untyped XML representation that combines all of the sparse columns of a table into a structured output. A column set cannot be added to a table that contains sparse columns. For more information about column sets, see Using Column Sets.

Remarks

To add new rows of data, use INSERT. To remove rows of data, use DELETE or TRUNCATE TABLE. To change the values in existing rows, use UPDATE.

If there are any execution plans in the procedure cache that reference the table, ALTER TABLE marks them to be recompiled on their next execution.

Changing the Size of a Column

Locks and ALTER TABLE

Parallel Plan Execution

Partitioned Tables

Restrictions on Tables with Schema-Bound Views

Indexes and ALTER TABLE

Replicating Schema Changes

Data Compression

Permissions

Requires ALTER permission on the table.

ALTER TABLE permissions apply to both tables involved in an ALTER TABLE SWITCH statement. Any data that is switched inherits the security of the target table.

If any columns in the ALTER TABLE statement are defined to be of a common language runtime (CLR) user-defined type or alias data type, REFERENCES permission on the type is required.

Examples

A. Adding a new column

CREATE TABLE dbo.doc_exa (column_a INT) ;
GO
ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL ;
GO
EXEC sp_help doc_exa ;
GO
DROP TABLE dbo.doc_exa ;
GO

B. Dropping a column

CREATE TABLE dbo.doc_exb (column_a INT, column_b VARCHAR(20) NULL) ;
GO
ALTER TABLE dbo.doc_exb DROP COLUMN column_b ;
GO
EXEC sp_help doc_exb ;
GO
DROP TABLE dbo.doc_exb ;
GO

C. Changing the data type of a column

CREATE TABLE dbo.doc_exy (column_a INT ) ;
GO
INSERT INTO dbo.doc_exy (column_a) VALUES (10) ;
GO
ALTER TABLE dbo.doc_exy ALTER COLUMN column_a DECIMAL (5, 2) ;
GO
DROP TABLE dbo.doc_exy ;
GO

D. Adding a column with a constraint

CREATE TABLE dbo.doc_exc (column_a INT) ;
GO
ALTER TABLE dbo.doc_exc ADD column_b VARCHAR(20) NULL 
    CONSTRAINT exb_unique UNIQUE ;
GO
EXEC sp_help doc_exc ;
GO
DROP TABLE dbo.doc_exc ;
GO

E. Adding an unverified CHECK constraint to an existing column

CREATE TABLE dbo.doc_exd ( column_a INT) ;
GO
INSERT INTO dbo.doc_exd VALUES (-1) ;
GO
ALTER TABLE dbo.doc_exd WITH NOCHECK 
ADD CONSTRAINT exd_check CHECK (column_a > 1) ;
GO
EXEC sp_help doc_exd ;
GO
DROP TABLE dbo.doc_exd ;
GO

F. Adding a DEFAULT constraint to an existing column

CREATE TABLE dbo.doc_exz ( column_a INT, column_b INT) ;
GO
INSERT INTO dbo.doc_exz (column_a)VALUES ( 7 ) ;
GO
ALTER TABLE dbo.doc_exz
ADD CONSTRAINT col_b_def
DEFAULT 50 FOR column_b ;
GO
INSERT INTO dbo.doc_exz (column_a) VALUES ( 10 ) ;
GO
SELECT * FROM dbo.doc_exz ;
GO
DROP TABLE dbo.doc_exz ;
GO

G. Adding several columns with constraints

CREATE TABLE dbo.doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE) ;
GO
ALTER TABLE dbo.doc_exe ADD 

-- Add a PRIMARY KEY identity column.
column_b INT IDENTITY
CONSTRAINT column_b_pk PRIMARY KEY, 

-- Add a column that references another column in the same table.
column_c INT NULL  
CONSTRAINT column_c_fk 
REFERENCES doc_exe(column_a),

-- Add a column with a constraint to enforce that 
-- nonnull data is in a valid telephone number format.
column_d VARCHAR(16) NULL 
CONSTRAINT column_d_chk
CHECK 
(column_d LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' OR
column_d LIKE
'([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),

-- Add a nonnull column with a default.
column_e DECIMAL(3,3)
CONSTRAINT column_e_default
DEFAULT .081 ;
GO
EXEC sp_help doc_exe ;
GO
DROP TABLE dbo.doc_exe ;
GO

H. Adding a nullable column with default values

USE AdventureWorks ; 
GO
CREATE TABLE dbo.doc_exf ( column_a INT) ;
GO
INSERT INTO dbo.doc_exf VALUES (1) ;
GO
ALTER TABLE dbo.doc_exf 
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT GETDATE() WITH VALUES ;
GO
DROP TABLE dbo.doc_exf ;
GO

I. Disabling and re-enabling a constraint

CREATE TABLE dbo.cnst_example 
(id INT NOT NULL,
 name VARCHAR(10) NOT NULL,
 salary MONEY NOT NULL
    CONSTRAINT salary_cap CHECK (salary < 100000)
);

-- Valid inserts
INSERT INTO dbo.cnst_example VALUES (1,'Joe Brown',65000);
INSERT INTO dbo.cnst_example VALUES (2,'Mary Smith',75000);

-- This insert violates the constraint.
INSERT INTO dbo.cnst_example VALUES (3,'Pat Jones',105000);

-- Disable the constraint and try again.
ALTER TABLE dbo.cnst_example NOCHECK CONSTRAINT salary_cap;
INSERT INTO dbo.cnst_example VALUES (3,'Pat Jones',105000);

-- Re-enable the constraint and try another insert; this will fail.
ALTER TABLE dbo.cnst_example CHECK CONSTRAINT salary_cap;
INSERT INTO dbo.cnst_example VALUES (4,'Eric James',110000) ;

J. Dropping a constraint

CREATE TABLE dbo.doc_exc ( column_a INT
CONSTRAINT my_constraint UNIQUE) ;
GO
ALTER TABLE dbo.doc_exc DROP CONSTRAINT my_constraint ;
GO
DROP TABLE dbo.doc_exc ;
GO

K. Switching partitions between tables

CREATE TABLE PartitionTable (col1 int, col2 char(10))
ON myRangePS1 (col1) ;
GO
CREATE TABLE NonPartitionTable (col1 int, col2 char(10))
ON test2fg ;
GO
ALTER TABLE PartitionTable SWITCH PARTITION 2 TO NonPartitionTable ;
GO

L. Disabling and re-enabling a trigger

CREATE TABLE dbo.trig_example 
(id INT, 
name VARCHAR(12),
salary MONEY) ;
GO
-- Create the trigger.
CREATE TRIGGER dbo.trig1 ON dbo.trig_example FOR INSERT
AS
IF (SELECT COUNT(*) FROM INSERTED
WHERE salary > 100000) > 0
BEGIN
    print 'TRIG1 Error: you attempted to insert a salary > $100,000'
    ROLLBACK TRANSACTION
END ;
GO
-- Try an insert that violates the trigger.
INSERT INTO dbo.trig_example VALUES (1,'Pat Smith',100001) ;
GO
-- Disable the trigger.
ALTER TABLE dbo.trig_example DISABLE TRIGGER trig1 ;
GO
-- Try an insert that would typically violate the trigger.
INSERT INTO dbo.trig_example VALUES (2,'Chuck Jones',100001) ;
GO
-- Re-enable the trigger.
ALTER TABLE dbo.trig_example ENABLE TRIGGER trig1 ;
GO
-- Try an insert that violates the trigger.
INSERT INTO dbo.trig_example VALUES (3,'Mary Booth',100001) ;
GO

M. Creating a PRIMARY KEY constraint with index options

USE AdventureWorks;
GO
ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK 
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)
WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON)
GO

N. Dropping a PRIMARY KEY constraint in the ONLINE mode

USE AdventureWorks;
GO
ALTER TABLE Production.TransactionHistoryArchive
DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID
WITH (ONLINE = ON);
GO

O. Adding and dropping a FOREIGN KEY constraint

USE AdventureWorks ;
GO
CREATE TABLE Person.ContactBackup
(ContactID int) ;
GO
ALTER TABLE Person.ContactBackup
ADD CONSTRAINT FK_ContactBacup_Contact FOREIGN KEY (ContactID)
    REFERENCES Person.Contact (ContactID) ;
ALTER TABLE Person.ContactBackup
DROP CONSTRAINT FK_ContactBacup_Contact ;
GO
DROP TABLE Person.ContactBackup ;

P. Changing the size of a column

IF OBJECT_ID ( 'dbo.doc_exy', 'U' ) IS NOT NULL 
    DROP TABLE dbo.doc_exy;
GO
-- Create a two-column table with a unique index on the varchar column.
CREATE TABLE dbo.doc_exy ( col_a varchar(5) UNIQUE NOT NULL, col_b decimal (4,2));
GO
INSERT INTO dbo.doc_exy VALUES ('Test', 99.99);
GO
-- Verify the current column size.
SELECT name, TYPE_NAME(system_type_id), max_length, precision, scale
FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.doc_exy');
GO
-- Increase the size of the varchar column.
ALTER TABLE dbo.doc_exy ALTER COLUMN col_a varchar(25);
GO
-- Increase the scale and precision of the decimal column.
ALTER TABLE dbo.doc_exy ALTER COLUMN col_b decimal (10,4);
GO
-- Insert a new row.
INSERT INTO dbo.doc_exy VALUES ('MyNewColumnSize', 99999.9999) ;
GO
-- Verify the current column size.
SELECT name, TYPE_NAME(system_type_id), max_length, precision, scale
FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.doc_exy');

Q. Allowing lock escalation on partitioned tables

ALTER TABLE T1 SET (LOCK_ESCALATION = AUTO)
GO

R. Configuring change tracking on a table

USE AdventureWorks;
ALTER TABLE Person.Contact
ENABLE CHANGE_TRACKING;

USE AdventureWorks;
ALTER TABLE Person.Contact
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);

USE AdventureWorks;
ALTER TABLE Person.Contact
DISABLE CHANGE_TRACKING;

S. Modifying a table to change the compression

ALTER TABLE T1 
REBUILD WITH (DATA_COMPRESSION = PAGE);

ALTER TABLE PartitionTable1 
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  NONE) ;
GO

ALTER TABLE PartitionTable1 
REBUILD PARTITION ALL 
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;

T. Adding a sparse column

CREATE TABLE T1
(C1 int PRIMARY KEY,
C2 varchar(50) SPARSE NULL,
C3 int SPARSE NULL,
C4 int ) ;
GO

ALTER TABLE T1
ADD C5 char(100) SPARSE NULL ;
GO

ALTER TABLE T1
ALTER COLUMN C4 ADD SPARSE ;
GO

ALTER TABLE T1
ALTER COLUMN C4 DROP SPARSE;
GO

U. Adding a column set

CREATE TABLE T2
(C1 int PRIMARY KEY,
C2 varchar(50) NULL,
C3 int NULL,
C4 int ) ;
GO

ALTER TABLE T2
ADD CS XML COLUMN_SET FOR ALL_SPARSE_COLUMNS ;
GO

ALTER TABLE T2
ALTER COLUMN C2 ADD SPARSE ; 
GO

ALTER TABLE T2
ALTER COLUMN C3 ADD SPARSE ;
GO

V. Changing column collation

CREATE TABLE T3
(C1 int PRIMARY KEY,
C2 varchar(50) NULL,
C3 int NULL,
C4 int ) ;
GO

ALTER TABLE T3
ALTER COLUMN C2 varchar(50) COLLATE Latin1_General_BIN 
GO

Document Change History

See Also

Reference

Concepts

Other Resources