Identity Column in SQL Server

In this article, you will learn what is Identity column in SQL Server. We will see how to use it and also see some examples.

An identity column is a column in the SQL Server table that generates keys automatically for the records in the table.

• How to set identity column in SQL Server
• How to set identity column in SQL Server using the query
• How to set identity on an existing column in SQL Server
• How to update identity column in SQL Server
• How to insert values into a table with an identity column in SQL Server
• How to reset identity column in SQL Server
• Add identity to an existing column in SQL Server without dropping the column
• Remove identity from a column in SQL Server
• Remove identity from a column in SQL Server Example

How to set identity column in SQL Server

In this section, we will learn how to set identity column in SQL Server.

• To create an identity column in SQL Server, you need to use the Identity Specification property while creating the table.
• The Identity Specification property takes two values:
  • seed: The initial value from where you want to start the count of the identity column
  • increment: The increment to be done after each value of the identity column
• Let us create a table with an identity column in SQL Server Management Studio.
• Open SQL Server Management Studio and connect to the SQL Server database engine.
• In the Object Explorer window, navigate to the Databases in which you want to create a table and right-click on Tables, click on New, and then Table.

• Now enter the column names along with their respective data types.
• While creating the table make sure the data type of the column which you want to set as an identity column should be of integer type.
• Click on the column which you want to set as the identity column and navigate to the Column Properties tab below.
• Navigate to the Identity Specification option and double click to modify the column’s identity specifications.
• Change the (Is Identity) property to Yes and specify the Identity Increment and Identity Seed and save the table.

• Once you have set the identity column in the SQL Server table, you can try to insert rows into the table.

• You will notice that you are not allowed to insert values into the identity column.
• In our example, the EmpID column is the identity column.

Thus, you might have understood how to set an identity column in a SQL Server table with the help of SQL Server Management Studio.

Read: Delete Duplicate Rows in SQL Server

How to set identity column in SQL Server using the query

In the above section, we learned to set identity columns with SQL Server Management Studio. However, you can also set an identity column with the help of a SQL query.

One can do this with the help of the IDENTITY() function. We use this function in the CREATE TABLE statement when we create a new table. We specify this function in the column that we want to set as an identity column. You can follow the steps below to set an identity column in the SQL Server table:

• Write the query to create a new table.
• Specify the IDENTITY() function in the CREATE TABLE statement.

CREATE TABLE <table name>(
<identity column> int IDENTITY(1,1),
<column 2> <data type>,
<column 3> <data type>,
.
.
<column n> <data type>
)

• Once the table is created, try to insert some values.
• Let us see an example also.
• We have created a table IdentityTable in which we have defined the EmpID column as an identity column.

USE[master]
GO
DROP TABLE IF EXISTS dbo.TableIdentity
CREATE TABLE dbo.TableIdentity(
EmpID int IDENTITY(3,2),
EmpName nchar(20),
EmpDep nchar(10)
)

• We defined 3 as the seed value and 2 as the increment value.
• Now we will insert some values into the table.

USE [master]
GO
INSERT INTO dbo.TableIdentity(EmpName, EmpDep)
VALUES('Luca', 'Finance'),
	  ('Donald', 'Sales'),
	  ('George', 'Marketing'),
	  ('Mark', 'Sales')
SELECT * FROM dbo.TableIdentity

• You can observe that we didn’t specify the values for the EmpID column but still it have values because of its identity property.
• Also, the values started from 3 and there is an increment of 2 after every record inserted.

Let us discuss one more example. I will create another table with an identity column. Suppose the table name is dbo.Student.

USE [BackupDatabase]
GO
DROP TABLE IF EXISTS dbo.Students
CREATE TABLE dbo.Students(
Student_id int IDENTITY(10,1),
Student_name nchar(30),
Student_stream nchar(20),
City nchar(20)
)

• This time the seed value is 10 i.e. the identity values will start from 10 and the increment value is 1.
• Now let us insert some values into it and see the output.

USE BackupDatabase
GO
INSERT INTO dbo.Students(Student_name, Student_stream, City)
VALUES('Robert', 'Computer Science', 'Houston'),
	  ('Oliver', 'Electronics', 'Chicago'),
	  ('Benjamin', 'Electronics', 'New York'),
	  ('Peter', 'Mechanical', 'Boston')
SELECT * FROM dbo.Students

You can see that we did not insert any values into the Student_id field because it is an identity column.

This is how you set an identity column in SQL Server 2019.

Read: SQL Server Create Temp Table

How to set identity on an existing column in SQL Server

In this section, will discuss the IDENTITY property of an existing column in an SQL Server table.

If you want to set IDENTITY property on an existing column in SQL Server, you have to drop the column and create it again. We will understand this method with an example.

• Consider a table TableIdentity.

• If we want to make the column EmpID an identity column, we will drop this column and create it again with the IDENTITY property.
• The following query will drop the EmpID column.

USE [master]
GO
ALTER TABLE dbo.TableIdentity
DROP COLUMN EmpID

• After dropping the column, add the same column with the ALTER statement.
• Specify the IDENTITY option along with the seed and the increment value.

USE [master]
GO
ALTER TABLE dbo.TableIdentity
ADD EmpID int IDENTITY(3,2)
SELECT * FROM dbo.TableIdentity

• If you observe the output, you can see that the EmpID column is now an identity column and it is having the expected key values.

Hope you have understood the things that should be kept in mind while setting the Identity property.

Read: SQL Server Row_Number

How to update identity column in SQL Server

In this section, you will learn how you can update the identity column in SQL server.

If you are saying that you want to update the identity column, you mean to say one of two things:

1. You want to update the Identity properties.
2. You want to update the values in identity column of the table

We will discuss both cases on by one.

1. Update the Identity properties: You can update the Identity properties by using the DBCC CHECKIDENT function. Using this function, update the seed value. This means you can decide again from where you want to start the identity value count. You can do this as:

DBCC CHECKIDENT('tableName', RESEED, NEW_RESEED_VALUE)

• For example, if you have a table named Orders, and you want to give identity values from 1000, you will write the query for the table as:

DBCC CHECKIDENT('dbo.Orders', RESEED, 1000)

How to insert values into table with identity column in SQL Server

In this section, you will learn how you can insert values into a table with the identity column in SQL Server.

When you insert values into a table having an identity column, you do not need to specify the values for the identity column. Let us understand this with an example.

• Consider the table IdentityTable having three columns.

• In the above table, the EmpID column is the identity column.
• If we want to insert a record in the table, we do not need to specify the value for the identity column i.e. EmpID.

USE [master]
GO
INSERT INTO dbo.IdentityTable(EmpName, EmpDep)
VALUES('Carlos','Sales')
SELECT * FROM dbo.IdentityTable

In this section, you learned how you can insert a record in a table having an identity column. However, we didn’t discuss how we can manually insert data in the identity column. You can learn this in our IDENTITY_INSERT in SQL Server article.

How to reset identity column in SQL Server

In this section, you will learn how you can reset the identity column in a SQL Server table. There are two ways in which you would want to do this.

One method is to drop the column and create it again specifying the Identity property and deciding the seed and the increment value. You can learn this thing in the above How to set identity on existing column in SQL Server section.

The other method is to use the DBCC utility. Using this utility you can define the new value from where you want to start the identity values. This method is suitable when you have a certain number of records in your table and you want to change the initial seed value of the Identity property.

• You can use the DBCC CHECKIDENT() function to set the new seed value as:

DBCC CHECKIDENT('tableName', RESEED, NEW_RESEED_VALUE)

• For example, if you have the IdentityTable table and you have already assigned the identity values from 1 to 10.

• If you want to start the next count from 100, you will write the query as:

DBCC CHECKIDENT('dbo.Student', RESEED, 99)

• After running this query, your count will start from 100 if you insert any record into it.
• Let us try to insert a record.

USE [master]
GO
INSERT INTO dbo.IdentityTable(EmpName, EmpDep)
VALUES('Carry','Marketing')
SELECT * FROM dbo.IdentityTable

Hence, you might have understood how you can reset the identity column in a SQL Server table.

Read: SQL Server drop table if exists

Add identity to existing column in SQL Server without dropping column

In SQL Server, you have to define the identity column when you create the table or you add a column in the existing table.

You cannot add the Identity property to an existing column without dropping it. However, you can learn how you can add the Identity property to an existing column in the above sections of this article.

Remove identity from column in SQL Server

In this section, we will discuss the removal of the Identity property from a column in SQL Server.

You cannot remove the identity property directly from the column. If you have defined the IDENTITY when you created the table, you cannot remove it. However, there are some alternatives to do that.

1. Drop the column and add it again with the ALTER statement. You can create the column again without mentioning the identity column. It is a very feasible method that you would like to use mostly.
2. Drop the whole table and create it again. Use this method if you have the backup of the data and the size is also small so that it is feasible to create the table again.
3. Create a duplicate table and change the meta-information about the tables. You can also use this method. In this method, you do not have to do much.
   1. Create a new table with the same specifications.
   2. Run the following query:
      • ALTER TABLE [Original Table] SWITCH TO [New Table]
   3. This query will interchange the metadata of both tables.
   4. Your original table will have no data. All that data will be transferred to the new table.
   5. Drop the old table and rename the new table to the old table name.

Remove identity from a column in SQL Server Example

In the above section, we discussed how we can remove Identity property from a column in SQL Server. We will see an example of the above method in detail.

• We have a table named IdentityTable. It has an identity column EmpID.
• We will create a new table named IdentityTable1 having exactly the same structure but no identity column defined.
• Then we will interchange their meta-information.

ALTER TABLE dbo.IdentityTable SWITCH TO dbo.IdentityTable1

• After running this query, you verify that IdentityTable has no data in it and all the data is transferred to the IdentityTable1.
• After the data transfer, drop the table IdentityTable.
• Then rename the IdentityTable to IdentityTable1 using the stored procedure sp_rename.

EXEC sp_rename 'IdentityTable1', 'IdentityTable'

• Now you have completed the procedure and you will have the same table with all your data conserved and the identity property is also removed.

You may like the following database tutorials:

• MySQL vs SQL Server – Key Differences in Details
• How to export data from SQL Server to Excel
• SQL Server Substring Function [9 Examples]

• How to set identity column in SQL Server
• How to set identity column in SQL Server using a query
• How to set identity on an existing column in SQL Server
• How to update identity column in SQL Server
• How to insert values into a table with an identity column in SQL Server
• How to reset identity column in SQL Server
• Add identity to an existing column in SQL Server without dropping a column
• Remove identity from a column in SQL Server
• Remove identity from a column in SQL Server Example