Sunday, 16 December 2012

How to modify the existing column property as IDENTITY


We have 3 options to modify the existing column as IDENTITY in SQL Server.

          1. Through SQL Server Management Studio, we can modify it.
          2. Create a new table with IDENTITY Column & drop the existing table
-- In this Approach we can retain the existing data values on the newly created identity column
          3. Create a new column with IDENTITY & drop the existing column
-- In this approach we can’t retain the existing data values on the newly created identity column

Option:1 ==> Through SQL Server Management Studio, we can modify it.

1.   Connect to your DB
2.   Show the table/column names in object explorer window
3.   Right click on column name
4.   Select modify
5.   In column properties tab

1)   Alter "identity specification"/"is identity" to YES
2)   Set increment/seed properties as desired

Note:  if we leave it at default 1, 1 it will automatically insert the next row with the appropriate next value (not 1)

Option:2 ==> create a new table with IDENTITY Column & drop the existing table

1. Create a new table with the desired layout with IDENTITY Column
2. Move the old table’s data into this new table
3. Drop the old table
4. Rename the new table to the old table
5. Add back any constraints and/or indexes


Tested Code:

Create table emp(empid int, empname varchar(50))
Go
Insert into emp values(1, 'James')
Insert into emp values(2, 'Carolin')
Insert into emp values(3, 'Lewis')
Insert into emp values(4, 'Tornato')
Insert into emp values(1, 'Mary')
Insert into emp values(8, 'Tony')
Go
Select * from emp
Go

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
CREATE TABLE Tmp_emp
      (
      empid  int NOT NULL IDENTITY (1, 1),
      empname varchar(50)
      )  ON [PRIMARY]
GO
SET IDENTITY_INSERT Tmp_emp ON
GO
IF EXISTS(SELECT * FROM emp )
       EXEC('INSERT INTO Tmp_emp  (empid , empname)
            SELECT empid ,empname FROM emp  WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT Tmp_emp OFF
GO
DROP TABLE emp
GO
EXECUTE sp_rename N'Tmp_emp ', N'emp ', 'OBJECT'
GO
COMMIT

Option:3 ==> create a new column with IDENTITY & drop the existing column

Alter Table emp Add empid_new Int Identity(1,1)
Go
Alter Table emp Drop Column empid
Go
Exec sp_rename N'emp.empid_new ', N'empid ','COLUMN'