Tuesday, 26 September 2017

How to Add or Remove Identity Property on Column from a table in SQL server

I got stuck at a point on removing the identity column from a table in a database, thinking much about T-SQL scripts to remove the identity property. However, there is no direct DDL scripts like ALTER or MODIFY tables to remove the identity property.

Steps to remove the Identity property through GUI in SSMS:

1. Before dropping the identity, please check the table from the database that you are referring to
2. From the “Object Explorer”, expand the database referring to and right click on the table where the identity property needs to be changed.
3.  Select “Design” option from the list, which will open the design of the table as shown below.
4. Under Result section, the design is popped with the details of the table with respect to structure.
5. Now, expand the “Identity Specification” and opt as “Yes/No” in the “Is Identity” option.
6. Click on the “Save” option to effect the changes made

When you click on the "Design option", the below layout pops up:


Take a look at the result section to opt the "Is Identity" changes:
















Additionally, there is an option to generate T-SQL script by the action performed using the GUI in SSMS. Since there is no ready-made script for changing the identity property, a feature in SSMS could be used to generate the T-SQL script.

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

/*creating a DumpTable*/
CREATE TABLE dbo.Tmp_DumpTable
       (
[COUNT] int not null identity(1,1),
fruits nvarchar not null,
vegetables nvarchar not null
       )  ON UserData
       TEXTIMAGE_ON UserData
GO
ALTER TABLE dbo.Tmp_DumpTable SET (LOCK_ESCALATION = TABLE)

GO
/  /*Setting the Identity_Insert to ON*/
SET IDENTITY_INSERT dbo.Tmp_DumpTable ON
GO
IF EXISTS(SELECT * FROM dbo.DumpTable)
       EXEC('INSERT INTO dbo.Tmp_DumpTable (Count,Fruits,Vegetables)
              SELECT Count,Fruits,Vegetables FROM dbo.DumpTable WITH (HOLDLOCK TABLOCKX)')
GO
/  /*Setting the Identity_Insert to OFF*/
SET IDENTITY_INSERT dbo.Tmp_DumpTable OFF
GO

/*Dropping the created DumpTable*/
DROP TABLE dbo.DumpTable
GO
EXECUTE sp_rename N'dbo.Tmp_DumpTable', N'DumpTable', 'OBJECT'
GO

COMMIT

0 comments: