How to Add a Column to an Existing Table in SQL Using the ALTER TABLE ADD Statement

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

Parameters:

• 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, and more).

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);

As you can see, the database management system automatically adds the new genre column to the end of the table.

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);

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.

How to rename a column in an existing table

Adding columns to tables often involves renaming existing columns. This is a common operation that takes place for many reasons, such as improving readability and clarity, aligning with naming conventions, fixing errors, adapting to business requirements, schema evolution, compatibility issues, and so on.

In SQL Server, we use the system stored procedure sp_rename to change the names of various database objects, such as tables, indexes, or individual table columns. The syntax of the procedure is:

EXEC sp_rename 'old_name', 'new_name' [, 'object_type'];

Therefore, to rename an existing column in a table, we specify 'column' as the object type:

EXEC sp_rename 'old_name', 'new_name', 'column';

Let us see how to rename a column in a table in practice. In the dbo.address table, we have the address column that we want to rename to street. We use the below query:

EXEC sp_rename 'dbo.address.address', 'street', 'column'

If you rename a column, you may need to update references to it manually. The new column name is not automatically updated in views, functions, or stored procedures that rely on it. Modern database management tools like dbForge Studio for SQL Server that we'll explore further include the option of automatic refactoring allowing you to update all references to the renamed database object immediately.

How to modify a column in an existing table

Renaming is not the only common operation that takes place concerning table columns. Database specialists often need to modify columns, changing their assigned data types, sizes, default values, and more. SQL Server allows us to change the column data type by using the ALTER TABLE ... ALTER COLUMN statement to modify the data type and/or nullability.

The syntax of the query is:

ALTER TABLE table_name
ALTER COLUMN column_name new_data_type [NULL | NOT NULL]; 

Let us see the example. The street column in the dbo.address table we use for our tests was assigned the CHAR data type. This column does not allow NULLs. We want to change the data type to VARCHAR and allow NULL values. The query is:

ALTER TABLE dbo.address
ALTER COLUMN street VARCHAR(35)

Note that changing the data type of a column is not always possible. If the column is part of a clustered or non-clustered index, or referenced by a foreign key constraint, SQL Server will block direct modifications. To change the data type, you need to drop the index or foreign key constraint, modify the column, and then recreate the index or constraint. Alternatively, you can create a new column, migrate the data, rename that column, and drop the original column, as explained earlier.

Now, let's explore how to add columns to database tables using popular visual database management tools: SQL Server Management Studio (SSMS) and dbForge Studio for SQL Server.

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.). Specify additional properties for the new column, such as its length, precision, or scale, according to your needs, using the corresponding fields in the Column Properties pane.

Save the new column by clicking the Save icon or selecting Save from the File menu and 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 is more user-friendly and intuitive than using SQL queries, 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 Open Editor:

In the Table Editor window, right-click the column area and select New Column (or scroll the list down to get to 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.

FAQ