Modifies a table definition by altering, adding, or dropping columns and constraints, reassigning partitions, or disabling or enabling constraints and triggers.
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
You can change the length, precision, or scale of a column by specifying a new size for the column data type in the ALTER COLUMN clause. If data exists in the column, the new size cannot be smaller than the maximum length of the data. Also, the column cannot be defined in an index, unless the column is a varchar, nvarchar, or varbinary data type and the index is not the result of a PRIMARY KEY constraint. See Example P.
Locks and ALTER TABLE
The changes specified in ALTER TABLE are implemented immediately. If the changes require modifications of the rows in the table, ALTER TABLE updates the rows. ALTER TABLE acquires a schema modify lock on the table to make sure that no other connections reference even the metadata for the table during the change, except online index operations that require a very short SCH-M lock at the end. In an ALTER TABLE…SWITCH operation, the lock is acquired on both the source and target tables. The modifications made to the table are logged and fully recoverable. Changes that affect all the rows in very large tables, such as dropping a column or adding a NOT NULL column with a default, can take a long time to complete and generate many log records. These ALTER TABLE statements should be executed with the same care as any INSERT, UPDATE, or DELETE statement that affects many rows.
Parallel Plan Execution
In SQL Server 2005 Enterprise Edition, the number of processors employed to run a single ALTER TABLE ADD (index based) CONSTRAINT or DROP (clustered index) CONSTRAINT statement is determined by the max degree of parallelism configuration option and the current workload. If the Database Engine detects that the system is busy, the degree of parallelism of the operation is automatically reduced before statement execution starts. You can manually configure the number of processors that are used to run the statement by specifying the MAXDOP option.
Partitioned Tables
In addition to performing SWITCH operations that involve partitioned tables, ALTER TABLE can be used to change the state of the columns, constraints, and triggers of a partitioned table just like it is used for nonpartitioned tables. However, this statement cannot be used to change the way the table itself is partitioned. To repartition a partitioned table, use ALTER PARTITION SCHEME and ALTER PARTITION FUNCTION. Additionally, you cannot change the data type of a column of a partitioned table.
Restrictions on Tables with Schema-Bound Views
The restrictions that apply to ALTER TABLE statements on tables with schema-bound views are the same as the restrictions currently applied when modifying tables with a simple index. Adding a column is allowed. However, removing or changing a column that participates in any schema-bound view is not allowed. If the ALTER TABLE statement requires changing a column used in a schema-bound view, ALTER TABLE fails and the Database Engine raises an error message. For more information about schema binding and indexed views, see CREATE VIEW (Transact-SQL).
Adding or removing triggers on base tables is not affected by creating a schema-bound view that references the tables.
Indexes and ALTER TABLE
Indexes created as part of a constraint are dropped when the constraint is dropped. Indexes that were created with CREATE INDEX must be dropped with DROP INDEX. The ALTER INDEX statement can be used to rebuild an index part of a constraint definition; the constraint does not have to be dropped and added again with ALTER TABLE.
All indexes and constraints based on a column must be removed before the column can be removed.
When a constraint that created a clustered index is deleted, the data rows that were stored in the leaf level of the clustered index are stored in a nonclustered table. In SQL Server 2005, you can drop the clustered index and move the resulting table to another filegroup or partition scheme in a single transaction by specifying the MOVE TO option. The MOVE TO option has the following restrictions:
-
MOVE TO is not valid for indexed views or nonclustered indexes.
-
The partition scheme or filegroup must already exist.
-
If MOVE TO is not specified, the table will be located in the same partition scheme or filegroup as was defined for the clustered index.
When you drop a clustered index, you can specify ONLINE = ON option so the DROP INDEX transaction does not block queries and modifications to the underlying data and associated nonclustered indexes.
ONLINE = ON has the following restrictions:
-
ONLINE = ON is not valid for clustered indexes that are also disabled. Disabled indexes must be dropped by using ONLINE = OFF.
-
Only one index at a time can be dropped.
-
ONLINE = ON is not valid for indexed views, nonclustered indexes or indexes on local temp tables.
Temporary disk space equal to the size of the existing clustered index is required to drop a clustered index. This additional space is released as soon as the operation is completed.
.gif) Note: |
|---|
|
The options listed under <drop_clustered_constraint_option> apply to clustered indexes on tables and cannot be applied to clustered indexes on views or nonclustered indexes.
|
Replicating Schema Changes
By default, when you run ALTER TABLE on a published table at a SQL Server Publisher, that change is propagated to all SQL Server Subscribers. This functionality has some restrictions and can be disabled. For more information, see Making Schema Changes on Publication Databases.
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.
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 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
The following example modifies a table to remove 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
The following example changes a column of a table from INT to DECIMAL.
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
The following example adds a new column with a UNIQUE 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
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 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
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 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
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 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
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 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
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 re-enables the 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
The following example removes a UNIQUE constraint from a table.
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
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 re-enabling 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 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
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 ;
P. Changing the size of a column
The following example increases the size of a varchar column and the precision and scale of a decimal column. Because the columns contain data, the column size can only be increased. Also notice that col_a is defined in a unique index. The size of col_a can still be increased because the data type is a varchar and the index is not the result of a PRIMARY KEY constraint.
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');