Home > DeveloperSection > Articles > Add or Remove Identity Property on Column in SQL Server

Add or Remove Identity Property on Column in SQL Server


Database Database 
Ratings:
0 Comment(s)
 9699  View(s)
Rate this:

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 ‘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 Studio).

Let’s first see what SSMS does in backend when you add Identity property on an existing column 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

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

But before moving further let’s see what T-SQL 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

 

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

Besides id, name and address column one column is un-title, this is clustered index.

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

/* 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 TRANSACTION

SET QUOTED_IDENTIFIER ON

SET ARITHABORT ON

SET NUMERIC_ROUNDABORT OFF

SET CONCAT_NULL_YIELDS_NULL ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

COMMIT

BEGIN TRANSACTION

GO

COMMIT

BEGIN TRANSACTION

GO

use avi

 

CREATE TABLE dbo.tmp_info

   (

   id INT NOT NULL,

   name varchar(50) NULL,

      address varchar(50) null

   )  ON [PRIMARY]

GO

IF EXISTS(SELECT * FROM dbo.info)

    EXEC('INSERT INTO dbo.tmp_info (id, name,address)

      SELECT id, name, address FROM dbo.info WITH (HOLDLOCK TABLOCKX)')

GO

DROP TABLE dbo.Test1

GO

EXECUTE sp_rename N'dbo.Tmp_Test1', N'Test1', 'OBJECT'

GO

COMMIT

 

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

DROP TABLE dbo.info

GO

EXECUTE sp_rename N'dbo.tmp_info', N'info', 'OBJECT'

GO

 


Don't want to miss updates? Please click the below button!

Follow MindStick