SQL ALTER TABLE – Modifying Table Structure in SQL Server

The T-SQL ALTER TABLE statement is used to modify the structure of a table. Using the T-SQL ALTER TABLE statement you can add new columns, delete columns, change the data type of existing columns, or create indexes or constraints. ALTER TABLE can also reassign and rebuild partitions and/or disable and enable constraints and data compression.

Let’s take a closer look at how to use the T-SQL ALTER TABLE statement for some of the most common T-SQL operations.

SQL ALTER TABLE syntax

First, let’s take a quick look at the syntax of the ALTER TABLE command. The basic syntax of an ALTER TABLE query is actually quite simple as you can see in the following listing.

ALTER TABLE table_name [alter_option ...];

The ALTER TABLE statement is followed by the table name that will be modified. If the table isn’t in the current database or contained by the schema owned by the current user, you must explicitly specify the database and schema.

Then the alter_option specifies the type of operation to be performed on a table. For example, the ADD COLUMN option is used to add a column to the table while ALTER COLUMN is used to change the data type of a column.

Sample SQL table

First, let’s go ahead and create a simple sample table that we can use to demonstrate the different ALTER TABLE options. The following code shows a SQL CREATE TABLE statement that creates a simple example table.

CREATE TABLE dbo.myTable
 (
     myID INT,
     myFirstName VARCHAR(20),
     myLastName VARCHAR(20)
 );

In this case the table is named myTable and it has three columns.

• The first column is name myID and it is an INT (Integer) data type.
• The next two column are myFirstName and myLastName which are both VARCHARs with a maximum length of 20 characters. 

After creating the table, I added a few rows of sample data.

Next let’s closer look at some of the most important ALTER TABLE options in more detail.

ADD COLUMN

One of the most common ways to use ALTER TABLE is to add a column to a table. To add a column to a table you need to use the ALTER TABLE statement with the option ADD like you can see in the listing below.

ALTER TABLE myTable ADD myAge INT;

Here the ALTER TABLE statement identifies myTable as the table to work with and the myAge column will be added as an INT data type. You can see the results in the figure below.

It’s important to note that when you execute an ALTER TABLE statement SQL Server puts an exclusive lock on the table until the operation completes.

DROP COLUMN

To drop an existing column from a table you need use the ALTER TABLE statement with the DROP COLUMN option as shown below.

ALTER TABLE myTable DROP COLUMN myAge;

In this example the DROP COLUMN option specifies that the ALTER TABLE statement will drop the recently added myAge column. The following figure shows the command and the results.

ALTER COLUMN

Following is the syntax to change the data type of any column in the specified table.

ALTER TABLE myTable ALTER COLUMN myLastName VARCHAR(30);

Here the ALTER COLUMN option is used to specify the name of the column in myTable where the data type will be changed. In this example, myLastName is the modified column and the VARCHAR has been increased in length to 30.

You could also change it to different data types entirely. SQL Server will attempt to convert the data to the new data type. If there is incompatible data SQL Server will generate an error. The following figure shows the command and the results.

ADD PRIMARY KEY

A primary key is a column that has been configured as the unique row identifier for a given table. A table can only have one primary key. Primary keys can only be added to columns that are defined as NOT NULL.

You would typically create a primary key constraint when you create the table. However, you can also add a primary key to an existing table using the ALTER TABLE statement. The following statement adds a primary key to the myTable table.

ALTER TABLE myTable ADD CONSTRAINT PK_myTable PRIMARY KEY (myID);

To add a primary key, you need to use the ADD CONSTRAINT option and give the constraint a name. Here I used the name of PK_myTable. Then you need use the PRIMARY KEY option to specify the column that will become the primary key.

You can see the results of this command on the sample table in the figure below.

DROP PRIMARY KEY

Like you might expect you can also use the ALTER TABLE statement to drop the primary key from a table. The following example deletes the primary key from the myTable table.

ALTER TABLE myTable DROP CONSTRAINT PK_myTable;

In this example the DROP CONSTRAINT option is used with the name of the primary key constraint that was created earlier to delete the primary key. You can see the result below.

ADD CONSTRAINT

You can also use the ADD CONSTRAINT option to add constraints to a column beyond making it the primary key. You can add various constraints like a UNIQUE value constraint, a FOREIGN KEY constraint, a NULL constraint, or a CHECK or DEFAULT value constraint.

The following example shows how to add a UNIQUE constraint to the myID column.

ALTER TABLE myTable ADD CONSTRAINT U_myTable UNIQUE (myID);

Here ADD CONSTRAINT uses the U_myTable name to add a new UNIQUE constraint to the myID column. This ensures that all values in the myID column must be unique. You can see the results of running the statement in the following figure.

DROP CONSTRAINT

You can remove a normal constraint exactly like dropping the constraint for a primary key. The following example drops the newly added UNIQUE constraint on the myID column.

ALTER TABLE myTable DROP CONSTRAINT U_myTable;

Here DROP CONSTRAINT is used with the U_myTable name to specify the constraint name. You can see the results in the figure below.

Enable table data compression

The ALTER TABLE statement can also be used to enable table level data compression. You have the choice of using row-level or page-level data compression.

ALTER TABLE myTable REBUILD WITH (DATA_COMPRESSION = PAGE);

In this example ALTER TABLE identifies myTable as the table that will be compressed. The REBUILD WITH clause uses the DATA_COMPRESSION = PAGE argument to specify page-level data compression.

Disable table data compression

Likewise, you can use ATLER TABLE to disable data compression for a table like you can see in the following command.

ALTER TABLE myTable REBUILD WITH (DATA_COMPRESSION = NONE);

Here the ALTER TABLE statement specifies that it will work on myTable and the data compression value is set to none meaning the table will not use data compression.

Summary

In this tutorial you learned about the most common ways of using the ALTER TABLE statement. ALTER TABLE can also change the storage partition used by a table but that is a less common usage.

You might notice there is no option for rename column or table. Unlike some other platforms like mySQL or Oracle, the SQL Server ALTER TABLE statement doesn’t have that capability. Typically, you would use the stored procedure sp_rename for rename operations. Likewise, in SQL Server, the ALTER TABLE command does not work with indexes beyond the primary key unless it is for a memory optimized table.