ALTER TABLE ADD: How to add a column to a table in SQL Server

SQL Server databases are widely used in various industries to store, manage and manipulate large amounts of data. As the data grows, it becomes necessary to continuously maintain the database and optimize its performance. Adding new columns to the existing tables is a common task in this regard.

Whether it's to store additional information or to improve data indexing and query processes, the ALTER TABLE command in SQL Server provides a straightforward and efficient way to accomplish this task. In this article, we'll explore the syntax and usage of the ALTER TABLE command in SQL Server, along with some best practices and considerations to keep in mind when adding columns to your database tables.

ALTER TABLE syntax

The basic syntax of the ALTER TABLE command used to add a new column to the existing database table is below:

ALTER TABLE table_name
ADD column_name data_type;
					

Where:

table_name is the name of the table where you want to add a new column.

column_name is the name of the new column you are adding.

data_type is the data type of the new column you are adding (VARCHAR, DATE, INT, etc.).

To add several columns to a table in SQL Server, you can modify the standard command syntax in the following way:

ALTER TABLE table_name
ADD column_name1 data_type1,
ADD column_name2 data_type2,
ADD column_nameN data_typeN;		
						

You can add as many columns to a table as needed, and specify different data types for them all.

Key points

Before you add a new column to a table in SQL Server, note the following aspects:

• Ensure that you have ALTER permissions on the table to modify the table structure.
• Specify the new column name and data type in the query syntax to avoid command failure.
• SQL Server inserts a new column after the last column of the existing table.
• Keep in mind that a table can only have one primary key. If you attempt to add a new primary key column to an existing table, the query will result in an error.

Add new columns to a SQL Server table using queries

Here, let us demonstrate how to use the ALTER TABLE command to add new columns to an existing database table. Several practical examples will be provided to illustrate the process - we'll use the sakila sample database.

Add one column to a table

In this example, we'll demonstrate how to use the ALTER TABLE command in SQL Server to add a new column to the film table in the sakila test database. The new column will store information about the genres each movie belongs to.

ALTER TABLE dbo.film
ADD genre VARCHAR(35);									
						

Add multiple columns to a table

The following example demonstrates how to add multiple columns to an existing table in a SQL Server database. Let's imagine that we need to add additional information about the movies in our DVD store.

ALTER TABLE dbo.film
ADD subtitles VARCHAR(40), director VARCHAR(25), production_company VARCHAR(25);															
						

Let us run the query. As you can see, SQL Server puts the new columns after the last table column in the same order as specified in the query.

Add a column at a specific position in a table

You might need to add a column to the existing table for a number of reasons from maintaining backward compatibility to accommodating UI or presentation layer considerations. Let us look at how you can do this using SQL statements.

Step 1: Create a new table with the desired column order, including the column you want to add.

CREATE TABLE new_table (
	column1 datatype1,
	column2 datatype2,
	new_column datatype,
	column3 datatype3,
	...
);									
						

Step 2: Copy the data from the existing table to the new table.

INSERT INTO new_table (column1, column2, new_column, column3, ...)
SELECT column1, column2, old_column, column3, ...
FROM old_table;									
						

Step 3: Drop the existing table.

DROP TABLE old_table;									
						

Step 4: Rename the new table to the original table name.

EXEC sp_rename 'new_table', 'old_table';									
						

Add a column to one table based on information from another table

To add a column to one table based on information from another table in SQL Server, you can use the ALTER TABLE statement with the ADD COLUMN clause and a SELECT statement.

ALTER TABLE table1_name
ADD new_column_name datatype;
							
UPDATE table1_name
SET new_column_name = table2_column_name
FROM table1_name
JOIN table1_name ON table1.common_column = table2.common_column;									
						

The ALTER TABLE statement adds the new column to table1, and the UPDATE statement populates the new column by retrieving the corresponding information from table2 based on the specified join condition.

Add a column to a table using a visual table editor in SSMS

To add a new column to an existing SQL Server table using SQL Server Management Studio (SSMS), first, open SSMS and connect to the required database server. In Object Explorer, navigate to the database that contains the table where you want to add a new column, expand the database, and right-click the table you want to add a new column to. In the context menu that appears, select Design.

In the Table Designer window, that appears, put the cursor on an empty row at the end of the list of columns. In the new column's row, enter the desired name for the new column in the Column Name field. Next, use the Data Type drop-down menu to select the appropriate data type for the new column (e.g. VARCHAR, INT, DATE, etc.). Then, specify any additional properties for the new column, such as its length, precision, or scale, using the corresponding fields in the Column Properties pane below. Save the new column by clicking the Save icon or selecting Save from the File menu. Finally, close the Table Designer window.

Add columns to SQL Server table using dbForge Studio

dbForge Studio for SQL Server is a powerful IDE that provides a range of features to enhance SQL Server database development and management. Some of its capabilities include code completion, schema comparison and synchronization, SQL debugging, and data generation. It also provides a graphical interface that allows modifying the table (in particular -adding a new column or columns) without the need for manual coding.

Add a new column using the Studio

Let's take a look at how to visually add a new column to a SQL Server table using dbForge Studio. This approach can be more user-friendly and intuitive than using SQL queries directly, especially for those who may not be as comfortable with writing SQL code.

In Database Explorer, right-click the table you want to change and select Edit Table:

The Table Editor window will appear. Right-click the column area and select New Column (or just scroll the list of columns down to get to the empty lines):

Enter the new column name and customize its properties such as data type, NOT NULL, identity, etc.

Once done, click Apply Changes to save the modifications.

Add a column in the middle of a SQL Server table

Adding a new column to an SQL Server table using the ALTER TABLE command or dbForge Studio typically results in the column being appended to the end of the table. However, there may be situations where you need to insert a new column in the middle of existing columns. Let us look at how it can be achieved in dbForge Studio for SQL Server.

To insert a new column in the middle of an existing SQL Server table using dbForge Studio for SQL Server, open the Table Editor the same way we accessed it earlier. Then right-click the column name after which you want to insert your new column and select Insert Column.

Once done, click Apply Changes to save the modifications.

Add a nullable column to an existing SQL Server table

In dbForge Studio for SQL Server, you can choose between two methods to add a nullable column to an existing table. The first method involves utilizing the built-in Table Editor, which provides a visual interface for performing the operation without the need for coding. The second method involves using the SQL Editor, where you can manually enter the relevant SQL statement to add the nullable column.

Add a nullable column to a table using the Studio's Table Editor

To access the Table Editor, right-click the table you want to add a nullable column to in Database Explorer and then select Edit Table from the context menu.

In the Table Editor, enter the name of the new column and specify its datatype. To allow the new column to accept NULL values, ensure that the Not Null checkbox remains unselected.

Add a nullable column to a table using SQL statements

In SQL Server, you can use the following code to add a nullable column to an existing table.

ALTER TABLE table_name
ADD new_column_name int NULL;									
						

This statement adds a new column named with the specified data type of int. By including the NULL keyword, it allows the column to accept null values during data insertion.

Conclusion

Adding a new column to an SQL Server table is a relatively straightforward task that can be accomplished using either SQL queries or visual interfaces such as SQL Server Management Studio and dbForge Studio for SQL Server. Although both tools provide a visual interface for adding columns to a table, dbForge Studio offers more flexibility and customizations for changing the scope of columns. Ultimately, the method you choose to add a column to an SQL Server table will depend on your specific needs and preferences.