When you create a table in SQL Server using T-SQL, you specify all the columns for that table, along with their data types, any constraints, etc.

But what happens if one day you decide to add a new column to that table? How do you add the new column to the existing table without creating the table again? After all, dropping the table and starting again is usually not an option, as the table will already contain data, and you probably don’t want to have to backup all that data and re-insert it after dropping and creating the table.

The answer is: The ALTER TABLE statement.

The ALTER TABLE Statement

The ALTER TABLE statement allows you to modify an existing table without messing up its existing definition and any data that may reside within it.

You can add a new column to an existing table like this:

Here’s an example:

ALTER TABLE Tasks
    ADD TaskDescription varchar(255) NULL;
GO

In this example, we add a new column called TaskDescription to the Tasks table. Our new column has a data type of varchar(255) and it can contain null values.

We also use GO in this case, which is not actually a Transact-SQL command, but it is recognised by the sqlcmd and osql utilities and SQL Server Management Studio Code Editor, and signals the end of a batch of Transact-SQL statements.

Check the Results

You can check the results by running a query that returns all columns for the given table. Like this:

USE Solutions;
SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Tasks';

This lists the details of all columns in the Tasks table. I switch to the correct database firs (in this case, the Solutions database). Of course, you’ll need to change the database and table name as required.