SQL Server Portal

A couple of month ago, I migrated one of my client’s data from SQL Server earlier versions to SQL Server 2012. The most important part was, I changed all the identity columns of the tables from identity to sequence in the entire database, here is the solution. But another important aspect that I did not discuss is that, whenever you migrate or create a new sequence column make sure that it is not be changeable like identity columns. In the identity column it is implemented by default but in sequence column, you must implement it. But how ?

First of all let me update an identity column of a table to show how identity columns are prevented from being changed (by default). Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
UPDATE HumanResources.Department
SET [DepartmentID]=17
WHERE [DepartmentID]=16
--OUTPUT

Msg 8102, Level 16, State 1, Line 1
Cannot update identity column ‘DepartmentID’.

Solution :

By default, sequence columns are not prevented from being changed, you need to manually control this aspect. Let me show you the solution step by step.

Step 1 :
Let me create a table to demonstrate the solution.

USE tempdb
GO
--DROP TABLE tbl_sample_Seq
--GO
CREATE TABLE tbl_sample_Seq
(
[ID] int,
[Name] varchar(50)
)
GO

Step 2 :
Once you created the table, create a sequence object to insert [ID]s in the table.

USE tempdb
GO
--DROP SEQUENCE [dbo].[Seq_Student]
--GO
CREATE SEQUENCE [dbo].[Seq_Student]
AS [int]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 1000
GO

Step 3 :
Once you created the sequence object, insert few records using sequence object in the table. Given below is the script.

USE tempdb
GO
INSERT INTO tbl_sample_Seq VALUES (NEXT VALUE FOR [Seq_Student],'Imran')
INSERT INTO tbl_sample_Seq VALUES (NEXT VALUE FOR [Seq_Student],'Bob')
INSERT INTO tbl_sample_Seq VALUES (NEXT VALUE FOR [Seq_Student],'Alexander')
INSERT INTO tbl_sample_Seq VALUES (NEXT VALUE FOR [Seq_Student],'Hosanna')
INSERT INTO tbl_sample_Seq VALUES (NEXT VALUE FOR [Seq_Student],'William')
GO

Step 4 :
Let me browse the table and show the records that have been inserted successfully.

USE tempdb
GO
SELECT * FROM tbl_sample_Seq
GO
--OUTPUT

Step 5 :
Lets update the sequence column. Do not DO this step in your production database. This step is just to demonstrate the issue.

USE tempdb
GO
UPDATE tbl_sample_Seq SET [ID]=33 WHERE [ID]=3
GO
--OUTPUT

(1 row(s) affected)

Step 6 :
Ooops….in the above step the [ID] column of the table got modified. Let me create an update trigger on the [ID] column of the table, so that we can prevent sequence values from being changed. This is the most important step while implementing sequence object in a table. Do not skip it.

USE tempdb
GO
CREATE TRIGGER trg_update
ON tbl_sample_Seq
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;

DECLARE @OldID int
DECLARE @NewID int

SELECT @OldID =[ID] FROM deleted
SELECT @NewID =[ID] FROM inserted

--Print @OldID
--Print @NewID

IF @OldID <> @NewID
BEGIN
RAISERROR('Failed', 16, 1);
ROLLBACK TRANSACTION
END
END
GO

Step 7 :
Once the update trigger has been created, lets update the sequence column ([ID]) again.

USE tempdb
GO
UPDATE tbl_sample_Seq SET [ID]=33 WHERE [ID]=3
GO
--OUTPUT

Msg 50000, Level 16, State 1, Procedure trg_update, Line 19
Failed
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

Conclusion :
Whenever you implement sequence object in any table, make sure you create the trigger to prevent sequence values from being changed.

Let me know if you came across this issue and its solutions.

Read Full Post »