---
title: "Add or Remove Identity Property on Column in SQL Server"  
description: "Identity is a property of table that automatically increment integer value of a column. For example a table has column name ‘id’ and this column gener"  
author: "AVADHESH PATEL"  
published: 2012-08-14  
updated: 2020-02-03  
canonical: https://www.mindstick.com/articles/979/add-or-remove-identity-property-on-column-in-sql-server  
category: "database"  
tags: ["database"]  
reading_time: 3 minutes  

---

# Add or Remove Identity Property on Column in SQL Server

**Identity** is a property of table that automatically increment integer value of a column. For example a table has [column name](https://www.mindstick.com/forum/529/rename-table-name-and-column-name-using-sql-query) ‘id’ and this column generated id automatically id value, this done by identity property.\

##### Adding Identity Property to an existing column in a table

However, there is an easy way to accomplish this action. It can be done through SSMS (SQL [Server Management](https://www.mindstick.com/interview/33968/how-to-clear-the-cache-in-sql-server-management-studio-ssms) Studio).

Let’s first see what SSMS does in backend when you add Identity property on an [existing column](https://www.mindstick.com/forum/159775/how-do-you-modify-an-existing-column-s-data-type-in-a-table-using-sql) in any table.

Now, let’s create an example table for better understanding.

```
/*Create table*/create table info(id int primary key,[name] varchar(50) not null,address varchar(50) not null)
```

Take a look at the design of this table in SSMS.

![Add or Remove Identity Property on Column in SQL Server](https://www.mindstick.com/mindstickarticle/1d62601c-a7b5-4d0f-9bdf-34a25bf4fa17/images/483b74b6-a252-4d96-bcb4-81364cb52e34.png)

Now let us make **id**, an Identity column.

This is very easy. All you have to do is just select **Yes** from the drop down list and you are done!

![Add or Remove Identity Property on Column in SQL Server](https://www.mindstick.com/mindstickarticle/1d62601c-a7b5-4d0f-9bdf-34a25bf4fa17/images/bde002b5-1dd6-4525-b3c0-e192912af66a.png)

But before moving further let’s see what T-SQL [SQL Server](https://www.mindstick.com/articles/34/create-table-in-microsoft-sql-server) is using to make this change.

You will notice that T-SQL is used by SQL Server to make this change.

After you make the change for Identity property from **No** to **Yes**, on top in tools box, you will see **Generate Change Script**. This is the T-SQL Script that SQL Server will use to make this change.

![Add or Remove Identity Property on Column in SQL Server](https://www.mindstick.com/mindstickarticle/1d62601c-a7b5-4d0f-9bdf-34a25bf4fa17/images/f4e17ff4-b03a-4870-a465-80f62f2f2796.png)

After saving changes, insert records into table. Here we provide value for name and address column. Id column auto generate number for self.

```
insert into info values('xyz','india')insert into info values('abc','pakistan')
```

##### Output

![Add or Remove Identity Property on Column in SQL Server](https://www.mindstick.com/mindstickarticle/1d62601c-a7b5-4d0f-9bdf-34a25bf4fa17/images/b427ac2b-aa32-4c72-be05-2beb8bbf3ea6.png)

Besides id, name and address column [one column](https://www.mindstick.com/forum/23368/insert-and-fetch-multiple-images-into-one-column-in-mysql) is un-title, this is [clustered index](https://www.mindstick.com/blog/337/clustered-non-clustered-indexing-in-sql-server).

##### Adding Identity Property in a table

We can add **identity** property during creating table via query.

```
create table information(id int identity (1,1),[name] varchar(50) not null,address varchar(50) not null)
```

Removing Identity Property from an existing column in a table

There is no easy way to do this. By design there is no simple way to turn on or turn off the identity feature for an existing column. The only clean way to do this is to create a new table and migrate your data.

To get this script use [Management Studio](https://www.mindstick.com/forum/159668/what-is-the-difference-between-sql-server-and-sql-management-studio) to make the change and then right click in the designer and select "Generate Change Script".

![Add or Remove Identity Property on Column in SQL Server](https://www.mindstick.com/mindstickarticle/1d62601c-a7b5-4d0f-9bdf-34a25bf4fa17/images/b5b95d34-3aa3-4f1a-a2c0-e54e24f65f50.png)

\

```
/* To prevent any potential data loss issues,
you should review this script in detail before running it outside the context
of the database designer.*/BEGIN TRANSACTIONSET QUOTED_IDENTIFIER ONSET ARITHABORT ONSET NUMERIC_ROUNDABORT OFFSET CONCAT_NULL_YIELDS_NULL ONSET ANSI_NULLS ONSET ANSI_PADDING ONSET ANSI_WARNINGS ONCOMMITBEGIN TRANSACTIONGOCOMMITBEGIN TRANSACTIONGOuse avi CREATE TABLE dbo.tmp_info   (   id INT NOT NULL,   name varchar(50) NULL,      address varchar(50) null   )  ON [PRIMARY]GOIF EXISTS(SELECT * FROM dbo.info)    EXEC('INSERT INTO dbo.tmp_info (id, name,address)      SELECT id,
name, address FROM dbo.info WITH (HOLDLOCK TABLOCKX)')GODROP TABLE dbo.Test1GOEXECUTE sp_rename N'dbo.Tmp_Test1', N'Test1', 'OBJECT'GOCOMMIT
```

Now drop the original table and rename the temporary table with original table name.

```
DROP TABLE dbo.infoGOEXECUTE sp_rename N'dbo.tmp_info', N'info', 'OBJECT'GO
```

---

Original Source: https://www.mindstick.com/articles/979/add-or-remove-identity-property-on-column-in-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.