How to add Columns to an Existing table in SQL Server

Adding a new column to an existing table with data is always tricky. You need to know what data is there, how much data is there, to gauge how long your query is gonna take to complete in production. Also, you cannot add NOT NULL columns into an existing table if they are not empty and you don't have a default value specified. If you know SQL then you probably know that you can add columns to an existing table in SQL Server using ALTER command. It not only allows you to add a column but to drop columns as well. You can also add or drop constraints using ALTER command. Btw, you need to be careful while doing anything with existing tables because of data inside, which presents some challenges while adding new columns or dropping existing ones.

You also need to be careful about how long your query is going to take to complete in production. For example, when you add new columns to existing table e.g. suppose your table contains 10M rows than adding a new column would be a little bit slow because default value has to be populated on those 10M rows, it will also affect indexes, database stats etc. Anyway, In this article, you will learn about SQL query or command to add new columns into existing table in SQL Server 2008, 2012 and other versions.



If you are learning SQL or working with Microsoft SQL Server as a developer and learning in hotch potch manner on the need to know basis, I suggest you read at least one good book on SQL e.g. Head First SQL and another good one on SQL Server to know the fundamentals well. Expanding on that knowledge will then make  a lot of sense because you already know how to solve the problem.

Best book to learn SQL



SQL query to add columns into existing table

As I said you can use ALTER SQL clause for modifying an existing table. You can use following SQL query to add columns into an existing table in SQL Server 2008:

ALTER TABLE dbo.StudentRcords ADD hobbies VARCHAR(50) NULL;
When you add a new column to existing table with data, it's important you give a default value or make it NULLABLE, as you need some values to be used with existing rows.

If you make the hobbies column NOT NULL then the above query will not work as shown below:
ALTER TABLE dbo.StudentRcords ADD hobbies VARCHAR(50) NOT NULL;
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'hobbies' cannot be added to non-empty table 'StudentRecords' because it does not satisfy these conditions.

If you want to make sure it's not null then you must provide a default value as shown below:

ALTER TABLE dbo.StudentRcords ADD hobbies VARCHAR(50) NOT NULL DEFAULT "Cricket";

By the way, you can also add more than one column into existing table in single ALTER query. You just need to separate them via comma as shown below:


ALTER TABLE dbo.StudentRcords ADD hobbies VARCHAR(50) NULL, acitivity_score INT NULL;

This query adds two column, first hobbies and second activity_score into StudentRecords table.


SQL Server ALTER TABLE ADD COLUMN Example

Now, let's see a live example where we will try to add a column into existing table using SQL Server Management Studio Query Editor:

We have an existing table called Test.dbo.Customer, which has two columns customer_id and customer_name and we will add a new column called customer_address into the table. By the way, don't follow this naming convention, instead of customer_id, you should just name the column id, name and address. Customer information is redundant because you know they are customer information as they are in the Customer table. 

How to add columns into existing table in SQL Server with example


Everything went fine because we chose the constraint as NULL, had we have made customer_address NOT NULL, you would have got the below error because the existing table is not empty.

SQL Server ALTER table Add Column with NOT NULL constraint

In order to add NOT NULL constraint on a new column into an existing table, either you have to make the table empty or you need to provide  a default value e.g. empty addresses as shown below:

SQL Server ALTER table ADD column NOT NULL with default values


You can see that this time the ALTER statement is successful and you can also see the customer_address column added with empty values. To learn more about ALTER command, read SQL Server 2012 T-SQL Fundamentals.


That's all about how to add a new column into an existing table in SQL Server. It'an always trick if your existing table is not empty. In fact, something adding a new column in production database takes quite a long time depending upon how much data you have. You also have limitation upon adding NOT NULL constraints as you must provide a default value to be used for existing rows.



No comments :

Post a Comment