SQL Server Add Column + Examples

In this SQL Server tutorial, we will learn How to add a column in the SQL Server table, Different examples related to adding a column, and will cover the following topics.

• SQL Server add column with default value
• SQL Server add column to existing table
• SQL Server add column after another column
• SQL Server add column it not exist
• SQL Server add column primary autoincrement
• SQL Server add column bit default 0
• SQL Server add column with not null
• SQL Server add column description
• SQL Server add column with foreign key
• SQL Server add column to existing table at specific position

SQL server add column with default value

In SQL Server, we can specify a default value for a column in SQL server 2019, and it will be added to all the new records in the table where no value is specified. And to set a default value for a column in SQL Server, we use the DEFAULT constraint.

Now there are two ways to assign a default value to a column in SQL Server. The first is by using SQL Server Management Studio (SSMS) and the second is by using Transact-SQL query. And we will discuss both methods in the section below.

Using SQL Server Management Studio

• First, start the SQL Server Management Studio and connect to the Database engine by using your login credentials.

• Now go to Object Explorer, and right-click on the “Tables” directory in your database, and click on “New” >”Table…” to create a new table.

• After this, a new table file will appear, add all the column details that you want in your new table.
• Now select a column for which you want to add a default value, and go to the “Column Properties” section.
• In the Column Properties section, you have to specify “Default value or Binding” to assign a default value for that particular column.

• In the last, click on the “SAVE” option to save the newly created table having a default value column.

Using Transact-SQL Query

We can easily create a new table having a default value column in sql server 2019 by using the following syntax.

CREATE TABLE table_name (
      column_a datatype,
      column_b datatype DEFAULT default_value);

In the above syntax, we are using the DEFAULT constraint with the column for which we want to add the default value. And after using the DEFAULT constraint, we have to specify the default value for that particular column.

Now to example, consider the following query given below.

CREATE TABLE [SampleTable] (
      id INT,
      Name varchar(10),
      Gender char(8),
      Joining_Date Datetime DEFAULT (getdate())
      );

In the above example, we create a new table named “SampleTable” and have defined 4 columns within this table. After this, we assign the default value for the “Joining_Date” column, and for this, we have defined the GETDATE() function that returns the current DateTime value.

So if we do not assign any value in the Joining_Date column for any record, it will automatically add the current DateTime value in it.

SQL Server add column to existing table

In SQL Server we can either use SQL Server Management Studio or an SQL query to add a new column to an existing table. Add we will cover both the methods in this section.

Using Transact-SQL Query to add column to existing table

Now to add a column to an existing table we have to use the ALTER TABLE command. The ALTER TABLE statement is used to add, delete, or modify columns in an existing table and it is also used to add and remove constraints from a table that already exists.

For adding a new column to an existing table, we can follow the following syntax.

ALTER TABLE table_name
ADD column_name datatype;

In the syntax, we are using the ALTER TABLE statement to specify the table in which we want to add a new column. After this, we are using the ADD statement to specify the column name and column data type.

Now, for example, consider the following query given below.

ALTER TABLE [SampleTable]
ADD Email varchar(255);

In the above example, we are adding a new “Email” column to the “SampleTable” table.

Using SSMS to add column to existing table

• Start the SQL Server Management Studio and connect to the Database engine by using your login credentials.
• Now go to Object Explorer, right-click on the table in which want to add a new column, and click on “Design“.

• Next, click on the first blank cell in the Column Name column and type the column name in the cell. After this, click “Tab” and enter the Data Type.

• After adding all the required columns, click on “SAVE” option.

SQL Server add column after another column

The SQL Server does not allow altering an existing table and adding a new column between the existing ones using any query. For this implementation, we have to use the SQL Server Management Studio.

But before using SQL Server Management Studio directly, we have to disable one option in SQL Server Management Studio, which will prevent saving the changes in the tables.

Here are some of the steps that we need to follow, to disable the option and add column after another column.

• In SQL Server Management Studio first select “Tools” and then click on “Options..“.

• Now under Designer tab click on “Table and Database Designer“.

• Now uncheck the option ‘Prevent saving changes that require table re-creation‘ and click on “OK”.

• Now go to Object Explorer, right-click on the table in which want to add a new column, and click on “Design“.
• Next, choose the column position where you want to add a new column and right-click on that column, and click on “Insert Column“.

• We can even drag the columns to arrange them according to the requirements.
• Now enter the column name and data type and click “SAVE” to save the changes.

SQL Server add column primary autoincrement

In SQL Server, when a new record is entered into a table, auto-increment allows a unique number to be created automatically. And this is usually the primary key field that we want to be produced automatically whenever a new record is inserted.

There 2 ways through which we can add a column as a primary autoincrement key, and we will discuss both in this section.

Using Transact-SQL Query

SQL Server uses PRIMARY KEY keyword to set a column as primary key and IDENTITY keyword to implement the auto-increment feature. We can follow the following syntax to add a new column as a primary auto increment.

CREATE TABLE table_name (
    column_a datatype IDENTITY(seed, increment) PRIMARY KEY,
    column_b datatype
);

The IDENTITY keyword accepts 2 integer values, the first is the seed, which is used to set the initial value that needs to be assigned to the first record. And second is increment which is used to define the value that needs to be added to the previous row value, and the result will be assigned to the current row.

Now, for demonstration, consider the following query given below.

CREATE TABLE SampleTable (
    id int IDENTITY(1,1) PRIMARY KEY,
    LastName varchar(255),
    FirstName varchar(255),
    Age int
);

In the above example, we are creating a table with 4 columns. And we are using IDENTITY and PRIMARY KEY keywords to make the id column an autoincrement primary key.

Using SQL Server Management Studio

• First, we need to create a table and add some columns using SQL Server Management Studio. After this, right-click on the created table and click on “Design”.
• Next, we need to select the column that we want as autoincrement primary key, right-click that column and click on “Set Primary Key”.

• Next, move to the Column Properties section, and set the “Is Identity” property as Yes, and set increment and seed as 1.

• In the last, click on “SAVE” option to save the changes.

SQL Server add column it not exist

It is always better to first check whether a column exists or not before trying to add it to the table. So in this section, we will discuss how to first check if a column already exists and if not, insert a new column to that table.

And for this implementation, we can follow the following syntax.

IF NOT EXISTS (
  SELECT
    *
  FROM
    INFORMATION_SCHEMA.COLUMNS
  WHERE
    TABLE_NAME = 'table_name' AND COLUMN_NAME = 'column_name')
BEGIN
  ALTER TABLE table_name
  ADD column_name data_type
END;

The above query checks if the column already exists in the specified table, and if it doesn’t exist, the query will alter the table and add a column to it.

Now, for example, consider the following query given below.

IF NOT EXISTS (
  SELECT
    *
  FROM
    INFORMATION_SCHEMA.COLUMNS
  WHERE
    TABLE_NAME = 'SampleTable' AND COLUMN_NAME = 'Email')
BEGIN
  ALTER TABLE SampleTable
  ADD Email varchar(255)
END;

In the above example, we are checking if a column named “Email” exists in a table named “SampleTable” or not. And if the Email column doesn’t exist, then we are adding the column to SampleTable.

SQL Server add column bit default 0

The bit data type in SQL Server is an integer data type that can only take one of the following values: 0, 1, or NULL. Additionally, the text values TRUE and FALSE can be translated to bit values of 1 and 0.

And we can easily add a column of a bit data type with a 0 as a default value by using the following query.

--For creating new table
CREATE TABLE table_name (
      column_name BIT DEFAULT (0)
      ); 

--For adding column to an existing table
ALTER TABLE table_name
ADD column_name BIT Default (0)

Now, to define the column of bit data type, we have to use the BIT keyword. And to define zero (0) as a default value, we are using the DEFAULT constraint keyword.

Now, for example, consider the following query given below.

ALTER TABLE SampleTable
ADD Inactive BIT DEFAULT (0)

In the above code, we are using the ALTER TABLE command to add a column named “Inactive” of a bit data type with a default value of 0.

SQL Server add column with not null

• In SQL Server, a column can accept NULL values by default. And to change this column property, to not accept NULL values, we can use the NOT NULL constraint.
• The NOT NULL constraint forces a field to always have a value, which means if we don’t add a value to this field then the record will not be inserted into the table.

We can easily add a not null column to a new or an existing table by using the following query.

--Creating Table with NOT NULL Column 
CREATE TABLE table_name (
    column_name datatype NOT NULL,
);

--Adding NOT NULL Column to an Existing Table 
ALTER TABLE table_name
ADD column_name datatype NOT NULL;

In the above query, we are using the NOT NULL constraint while defining the column that we want to be NOT NULL in the table.

Now, for demonstration, consider the following query given below.

ALTER TABLE SampleTable
ADD Age int NOT NULL;

In the above example, we are defining the Age column as not null by using the NOT NULL constraint. And then, we are adding the column to an existing table by using ALTER TABLE command.

SQL Server add column with foreign key

A FOREIGN KEY is used to connect or link two tables together. It is a field or set of fields in one table that refers to the PRIMARY KEY in a different table. The child table is the table with the foreign key, and the referred or parent table is the table with the primary key.

In SQL Server, to define a column as FOREIGN KEY, we have to use the FOREIGN KEY constraint with that column. We can define FOREIGN KEY either by using SQL query or by using SQL Server Management Studio. And we will discuss both methods in the section below.

Now for demonstration, we are taking two tables, first is an employee table containing basic information related to employee and second is the salary table containing salary details of each employee. And we will try to build a relationship between both the tables using primary and foreign keys.

Employee Table

Salary Table

The id field in the Employee and Salary table is already set to primary key, representing the individual records. And we will set relationships between them by setting Emp_id as a foreign key by using different methods.

Using SQL Server Management Studio

• Right-click the table that will be on the foreign-key side of the relationship in Object Explorer and select “Design”.
• After this, a table in Table Designer will open and right-click the column that you want as a foreign key and select “Relationships..“.

• Now a Foreign Key Relationships dialog box will open, click on ADD option.

• A relationship with a system-provided name in the type FK_<tablename>_<tablename> now appears in the Selected Relationship list, where tablename is the name of the foreign key table.
• Now click on the relationship in the Selected Relationship list.
• Next, click on Tables and Columns Specification in the grid to the right and click on the ellipses (…) to the right of the property.

• Now a Tables and Columns dialog box will open, and we have to choose the table that will be on the primary key side of the relationship from the Primary Key drop-down list.
• Next, select the columns that contribute to the table’s primary key in the grid below. And choose the relevant foreign-key column of the foreign-key table in the adjacent grid cell to the right of each column.

• To establish the relationship, select OK.
• In the end, save your changes for the foreign key relationship.

Using Transact-SQL Query

Now to define a FOREIGN KEY using SQL query we can follow the following syntax.

--While creating new table
CREATE TABLE table_name_a (
    column_a datatype NOT NULL PRIMARY KEY,
    column_b datatype FOREIGN KEY REFERENCES table_name_b(column_name)
);


--While updating existing table
ALTER TABLE table_name_a (
   column_a datatype FOREIGN KEY REFERENCES table_name_b(column_name)
);

In the above syntax, we are using the FOREIGN KEY constraint with the column that we want to define as a foreign key. After this, we are using the REFERENCE keyword to refer to the column in another table, with which we want to define the relationship.

Now, for demonstration, consider the following query given below.

ALTER TABLE Salary
ADD FOREIGN KEY (Emp_id) REFERENCES Employee(id);

In the above example, we are adding the Emp_id column to the Salary table as a foreign key. And the key is referring to the id column in the Employee table.

SQL Server add column description

There are 2 ways through which we can add a description to a column in SQL Server. The first is by using SQL Server Management Studio and the second is by using the SQL query. And we will cover both methods in the section below.

Using SQL Server Management Studio

• In SQL Server Management Studio, first, we have to open the Table Designer by going to the Design option.
• Then first select the column and in the Column properties, we can define the column description in the Description section.
• Next, click on the ellipses (…) to the right of the Description property. Now a new Description property dialog box will open.

• We can define the decription in the empty section and click on “OK”.

• In the last, we have to save the changes.

Using Transact-SQL Query

Now for adding a description for a column using SQL query, we have to execute the sp_addextendedproperty stored procedure in SQL Server. And for this implementation, we can use the following syntax.

EXEC sp_addextendedproperty 
    @name = N'MS_Description', @value = 'your_column_discription',
    @level0type = N'Schema',   @level0name = 'dbo',
    @level1type = N'Table',    @level1name = 'table_name',
    @level2type = N'Column',   @level2name = 'column_name';
GO

The sp_addextendedproperty is used to add a new extended property to a database object. Now, for demonstration, consider the following query given below.

EXEC sp_addextendedproperty 
    @name = N'MS_Description', @value = 'Hey, this is a sample description!',
    @level0type = N'Schema',   @level0name = 'dbo',
    @level1type = N'Table',    @level1name = 'Salary',
    @level2type = N'Column',   @level2name = 'id';
GO

In the above example, we are using the sp_addextendedproperty procedure to add a description for the id column in the Salary table.

Read: SQL Server Row_Number

SQL Server add column to existing table at specific position

In many of the relational database management systems (RDBMS) like- MySQL, we can use the AFTER keyword while adding a new column to an existing table to specify the position. For this, we can follow the following syntax.

ALTER TABLE table_name 
ADD column_name datatype AFTER another_column

But in SQL Server, the above syntax will return an error because the AFTER keyword is not supported in SQL Server. So in SQL Server, we cannot use a query to add a new column at any specific position.

Now, what will be the solution, if a user wants to add a new column in between two existing columns in the table. Simply, we have to use SQL Server Management Studio.

In SQL Server Management Studio we can easily add a new column at a specific position by right-click the position in the table grid and click on the “Insert Column” option. Or we can also drag the column to a specific position, just by selecting and moving them.

For a more detailed guide, you can refer to the “SQL Server add column after another column” topic in this post.

You may like the following sql server tutorials:

• SQL Server Update Trigger Only If Column is Modified
• Identity Column in SQL Server

Thus, in this tutorial, we have learned How to add a column in the SQL Server table, Different examples related to adding a column, and have also covered the following topics.

• SQL Server add column with default value
• SQL Server add column to existing table
• SQL Server add column after another column
• SQL Server add column it not exist
• SQL Server add column primary autoincrement
• SQL Server add column bit default 0
• SQL Server add column with not null
• SQL Server add column description
• SQL Server add column with foreign key
• SQL Server add column to existing table at specific position