A. Adding a new column
The following example adds a column that allows null values and has no values provided through a DEFAULT definition. In the new column, each row will have NULL.

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

 

B. Dropping a column
The following example modifies a table to remove a column.

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

 

C. Changing the data type of a column
The following example changes a column of a table from INT to DECIMAL.

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

 

D. Adding a column with a constraint
The following example adds a new column with a UNIQUE constraint.

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

 

E. Adding an unverified CHECK constraint to an existing column
The following example adds a constraint to an existing column in the table. The column has a value that violates the constraint. Therefore, WITH NOCHECK is used to prevent the constraint from being validated against existing rows, and to allow for the constraint to be added.

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

 

F. Adding a DEFAULT constraint to an existing column
The following example creates a table with two columns and inserts a value into the first column, and the other column remains NULL. A DEFAULT constraint is then added to the second column. To verify that the default is applied, another value is inserted into the first column, and the table is queried.

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

 

G. Adding several columns with constraints
The following example adds several columns with constraints defined with the new column. The first new column has an IDENTITY property. Each row in the table has new incremental values in the identity column.

  
CREATE TABLE doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE) ;
GO
ALTER TABLE 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 doc_exe ;
GO

 

H. Adding a nullable column with default values
The following example adds a nullable column with a DEFAULT definition, and uses WITH VALUES to provide values for each existing row in the table. If WITH VALUES is not used, each row has the value NULL in the new column.

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

 

I. Disabling and reenabling a constraint
The following example disables a constraint that limits the salaries accepted in the data. NOCHECK CONSTRAINT is used with ALTER TABLE to disable the constraint and allow for an insert that would typically violate the constraint. CHECK CONSTRAINT reenables the constraint.

  
CREATE TABLE 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 cnst_example VALUES (1,'Joe Brown',65000)
INSERT INTO cnst_example VALUES (2,'Mary Smith',75000)

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

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

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

 

J. Dropping a constraint
The following example removes a UNIQUE constraint from a table.

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

 

K. Switching partitions between tables
The following example creates a partitioned table, assuming that partition scheme myRangePS1 is already created in the database. Next, a nonpartitioned table is created with the same structure as the partitioned table and on the same filegroup as PARTITION 2 of table PartitionTable. The data of PARTITION 2 of table PartitionTable is then switched into table NonPartitionTable.

  
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 reenabling a trigger
The following example uses the DISABLE TRIGGER option of ALTER TABLE to disable the trigger and allow for an insert that would typically violate the trigger. ENABLE TRIGGER is then used to re-enable the trigger.

  
CREATE TABLE trig_example 
(id INT, 
name VARCHAR(12),
salary MONEY) ;
GO
-- Create the trigger.
CREATE TRIGGER trig1 ON 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 trig_example VALUES (1,'Pat Smith',100001) ;
GO
-- Disable the trigger.
ALTER TABLE trig_example DISABLE TRIGGER trig1 ;
GO
-- Try an insert that would typically violate the trigger.
INSERT INTO trig_example VALUES (2,'Chuck Jones',100001) ;
GO
-- Re-enable the trigger.
ALTER TABLE trig_example ENABLE TRIGGER trig1 ;
GO
-- Try an insert that violates the trigger.
INSERT INTO trig_example VALUES (3,'Mary Booth',100001) ;
GO

 

M. Creating a PRIMARY KEY constraint with index options
The following example creates the PRIMARY KEY constraint PK_TransactionHistoryArchive_TransactionID and sets the options FILLFACTOR, ONLINE, and PAD_INDEX. The resulting clustered index will have the same name as the constraint.

  
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
The following example deletes a PRIMARY KEY constraint with the ONLINE option set to ON.

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

 

O. Adding and dropping a FOREIGN KEY constraint
The following example creates the table ContactBackup, and then alters the table, first by adding a FOREIGN KEY constraint that references the table Contact, then by dropping the 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 ;

 

See Also