How to ALTER TABLE in SQL Server

In this SQL Server tutorial, you will learn how to ALTER TABLE in SQL Server.

You will understand the ALTER TABLE statement and why you should use it. Then, you will learn how to add a new column to an existing table, modify the column’s datatype, and drop the existing column of the table.

Additionally, you will understand how to add the constraint on the table, such as PRIMARY KEY.

What is ALTER TABLE in SQL Server?

The ALTER TABLE is a command or statement in SQL Server that changes the existing tables’ structure.

Changes allow you to add the column, drop the column, change the existing column’s data type, and even add constraints like primary and foreign keys, etc.

As time passes or requirements arise, business needs to change the properties of a database table, and this statement is specially created to deal with that kind of change.

Let’s learn each change we can make on the table individually.

Add Column using ALTER TABLE in SQL Server

So, if you have a table and a requirement to add a new column to that table arises, you can use the following syntax.

ALTER TABLE table_name
ADD column_name datatype;

Where,

  • ALTER TABLE: It is the command to change the properties of the table.
  • table_name: Name the table where you want to add the new column.
  • ADD column_name: Specify the keyword ADD followed by the column name you want to add.
  • datatype: Specify the data type of the column, which means which kind of a value column will store, such as int, varchar, etc.

For example, you have Orders with columns OrderID and OrderStatus, as shown below.

Add Column using ALTER TABLE in SQL Server on Orders Table

You have a task to add the new column named ‘OrderDate’ to this table, and the column’s data type should be a date. Here, you can use the ALTER TABLE statement with the ADD keyword followed by column_name to add that column, as shown below.

ALTER TABLE Orders
ADD OrderDate Date;

SELECT * FROM Orders;
Add Column using ALTER TABLE in SQL Server

From the output, you can see a new column, OrderDate, which is added to type Date. Now, it can store the order date of the item.

This is how you can use the ADD command with ALTER TABLE in SQL Server to add a new column to an existing table.

Change the Datatype of the Column using ALTER TABLE in SQL Server

In the above section, you added a new column for the date data type; now, you have modified that column to store the time of the order item.

So here, you need to change the date type of the column from Date to DateTime data type. To change the date type of the column, the ALTER COLUMN command is used with the ALTER TABLE statement; use the following syntax.

ALTER TABLE table_name
ALTER COLUMN column_name datatype;

Where,

  • ALTER TABLE: It is the command to change the properties of the table.
  • table_name: Name the table where you want to change the data type of the new column.
  • ALTER COLUMN column_name: Specify the keyword ALTER COLUMN followed by the column name whose data type you want to change.
  • datatype: Specify the new data type of the column, which means which kind of a value column will store, such as int, varchar, etc.

Let’s change the datatype of the OrderDate from Date to DateTime datatype. But before that, use the query below to see the current datatype of the OrderDate column.

SELECT 
COLUMN_NAME, 
DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Orders'; 
Checking Datatype of Column in a Table

The current data type of the OrderDate column is the date; now, let’s change it to datetime so that it can also store the time of the order items. For that, use the query below.

ALTER TABLE Orders
ALTER COLUMN OrderDate DateTime;

After executing the above query, it changes the column’s datatype to the new datatype DateTime.

Again, view the datatype of the column using the query below.

SELECT 
COLUMN_NAME, 
DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Orders';
Change the Datatype of the Column using ALTER TABLE in SQL Server

The column of the OrderDate datatype changed to datetime, as you can see in the above output.

You can change the datatype of any column to any datatype, such as varchar, boolean, etc.

This is how to modify the column’s datatype using the ALTER COLUMN command with ALTER TABLE in SQL Server.

Drop Column using ALTER TABLE in SQL Server

You have learned how to add columns and modify the column’s data type, but sometimes you don’t require any column of the table, and you must delete that column.

You can follow the syntax below to drop the unrequited column from the table.

ALTER TABLE table_name
DROP COLUMN column_name;

Where,

  • ALTER TABLE: It is the command to change the properties of the table.
  • table_name: Name the table containing the column you want to delete.
  • DROP COLUMN column_name: Specify the keyword DROP COLUMN followed by the column name you want to drop.

Suppose you mistakenly added the OrderDate column to the Orders table or no longer need the OrderDate column. So you must drop that column; here, you can use the DROP COLUMN command with the ALTER TABLE statement, as shown below.

ALTER TABLE Orders
DROP COLUMN OrderDate;

After executing the query for dropping the column, run the query below to view the table Orders.

SELECT * FROM Orders;
Drop Column using ALTER TABLE in SQL Server

Look, the OrderDate column is dropped successfully from the table Orders. This is how to drop the table column using the DROP COLUMN command with ALTER TABLE in SQL Server.

Adding Constraints using ALTER TABLE in SQL Server

Using the ALTER TABLE statement, you can add constraints like PRIMARY and FOREIGN KEY to the existing table.

The syntax to add the primary key constraint on the table is given below.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column_name);

Where,

  • ALTER TABLE: It is the command to change the properties of the table.
  • table_name: Name the table where you want to add the primary key constraint.
  • ADD CONSTRAINT constraint_name: Specify the keyword ADD CONSTRAINT followed by the constraint_name you want to add.
  • PRIMARY KEY (column_name): The name of the constraint is PRIMARY KEY, and specify the name of the column that will act as the primary key column.

Let’s take an example by creating a table from scratch. Create a Sales table with columns SaleID and SaleDate, as shown below.

CREATE TABLE Sales (
SaleID INT NOT NULL,
SaleDate DATETIME
);

After creating a table, let’s see if any of the constraints exist on the table; for that, execute the query below.

SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'Sales';
Checking Constraints on Table

As you can see, this table has no constraints; let’s add one. Here, you have to add primary key constraints on the SaleID column. For that, use the query below.

ALTER TABLE Sales
ADD CONSTRAINT pk_sales PRIMARY KEY (SaleID);

Executing the above query adds a PRIMARY KEY constraint on the SaleID column with the name pk_sales.

Again, view all the constraints on the Sales table using the query below.

SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'Sales';
Adding Constraints using ALTER TABLE in SQL Server

You can see the constraint pk_sales that we have added from the output. You can add the foreign key constraint to a table.

This is how you can use the ADD CONSTRAINT command with ALTER TABLE in SQL Server to add a constraint to a table.

Conclusion

In this SQL Server tutorial, you learned how to use the ALTER TABLE statement with commands ADD, ALTER COLUMN, DROP COLUMN, and ADD CONSTRAINT to add, drop and alter the column’s datatype.

You may like to read: