How to add new columns to an existing table in SQL Server database? Example tutorial (original) (raw)

Hello guys, adding a new column to an existing table with data is always tricky and if you don't pay enough due diligence then you risk of corrupting or deleting existing data. 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 the 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 the ALTER command. Btw, you need to be careful while doing anything with existing tables because of the 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 the existing tables like suppose your table contains 10M rows then adding a new column would be a little bit slow because the 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 the existing table in SQL Server 2008, 2012, and other versions.

SQL query to add columns into an existing table

As I said you can use the ALTER SQL clause for modifying an existing table. You can use the 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 the 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 the addition of this column. Column's' hobbies' cannot be added to the 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 an existing table in a 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 columns, first hobbies, and second activity_score into the StudentRecords table.

best course to learn Microsoft SQL Server

SQL Server ALTER TABLE ADD COLUMN Example

Now, let's see a live example where we will try to add a column into an 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 a 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.

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

Other related SQL queries, Interview questions, and articles:

Thanks for reading this article, if you like this SQL tutorial.. If you have any questions or feedback, then please drop a note.