SQL Server Portal

In my earlier article, I wrote about sequence. In this article, we will learn how to convert an identity column to sequence, step by step. Original script is available here (MSDN)
Given below is the example that we need to create, to understand the steps :

Create table dbo.[Student]
(
[StudentID] int identity(1,1),
[StudentName] varchar(50)
CONSTRAINT PK_Student_StudentID PRIMARY KEY CLUSTERED
([StudentID] ASC)
)
GO
Insert into dbo.Student values ('Imran'),('Bob'),('Sandra')
GO
Select * from dbo.Student

Step :1
First of all, we need to add another column in the table with the same data type as identity column.

ALTER TABLE dbo.Student ADD StudentIDNew int NULL
GO

Step :2
Update the IDs from identity column to the newly added column.

UPDATE dbo.Student
SET [StudentIDNew] = [StudentID]
GO

Step :3
Drop the primary key constraint from the identity column of the table.

ALTER TABLE dbo.Student
DROP CONSTRAINT PK_Student_StudentID;
GO

Step :4
Drop the identity column from the column.

ALTER TABLE dbo.Student
DROP COLUMN [StudentID] ;
GO

Step :5
Rename the new column name to the old identity column name.

EXEC sp_rename 'dbo.Student.StudentIDNew',
'StudentID', 'COLUMN';
GO

Step :6
Change the new identity column to NOT NULL.

ALTER TABLE dbo.Student ALTER COLUMN [StudentID] int NOT NULL ;
GO

Step :7
Add primary key constraint back to the table.

ALTER TABLE dbo.Student
ADD CONSTRAINT PK_Student_StudentID PRIMARY KEY CLUSTERED
([StudentID] ASC) ;
GO

Step :8
Now, we need to create the sequence but before sequence creation, we need to know the last ID of the identity column.

SELECT MAX(StudentID) FROM dbo.Student ;
GO

Step :9
Create the sequence using the maximum ID +1 and the same data type of the identity column.

CREATE SEQUENCE dbo.StudentSeq
AS int
START WITH 4
INCREMENT BY 1 ;
GO

Step :10
Make the sequence as a default value of the column.

ALTER TABLE dbo.Student
ADD CONSTRAINT Const_StudentSeq DEFAULT (NEXT VALUE FOR dbo.StudentSeq)
FOR StudentID;
GO

Step :11
Insert few records and check whether the table is properly converted from identity to sequence or not.

Insert into Student (StudentName) Values ('Mark')
Insert into Student (StudentName) Values ('Peter')
Select * from dbo.Student
--OUTPUT

Shall discuss how to change a table from identity to sequence automatically in my next post.

Read Full Post »