How to add new columns to an existing table in SQL Server database? Example tutorial

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.

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. 

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.

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:

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:

• How to join multiple tables using LEFT Join in SQL (example)
• The real difference between WHERE and HAVING clause in SQL? (answer)
• 5 Free Courses to learn Database and SQL (free courses)
• How to migrate SQL queries from Oracle to SQL Server 2008? (answer)
• 4 Free Books to learn Microsoft SQL Server database (books)
• Top 5 Websites to learn SQL online for FREE? (websites)
• 5 Free Courses to learn Oracle and SQL Server? (courses)
• How to join three tables in one single SQL query (solution)
• Difference between clustered and non-clustered index in SQL? (answer)
• 5 Advanced SQL books to level up your SQL skills (books)
• 5 Courses to learn Oracle and Microsoft SQL Server database (courses)
• Difference between Self and Equi Join in SQL? (answer)
• 10 Frequently asked SQL Query interview questions (solution)
• Write SQL Query to find duplicate emails - LeetCode problem (solution)
• Write a SQL query to find all table names on a database in MySQL (solution)
• 5 Courses to learn Database and SQL Better (courses)
• Top 5 Websites to learn SQL online for FREE? (resource)
• What is the difference between UNION and UNION ALL in SQL? (answer)
• Write a SQL query to copy or backup a table in MySQL (solution)
• How do you find the duplicate rows in a table on a database? (solution)
• Top 5 Courses to learn MySQL Database for Beginners (Courses)
• What is the difference between View and Materialized View in Database? (answer)
• Difference between Primary and Candidate key in table? (answer)
• 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
• Difference between Unique and Primary key in table? (answer)
• Top 5 Courses to learn PostgreSQL in-depth (courses)
• 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
• Difference between Primary and Foreign key in table? (answer)
• Top 5 Courses to learn Microsoft SQL Server in-depth (courses)

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