SQL Server Portal

Sequence object is one of the nice features shipped with SQL Server 2012. It gives much more flexibility than identity column. Due to its flexibility, I usually recommend to use it instead of identity. But recently I came across with a so called issue related to sequence object. The issue is that sequence object does not enforce uniqueness by itself.

Let me create a sample to demonstrate the issue step by step.
Step 1:
Create a sample table. Given below is the script.

USE [AdventureWorks2012]
GO
--DROP TABLE [dbo].[tbl_sample]
--GO
--Create a sample Table
CREATE TABLE [dbo].[tbl_sample]
(
[ID] [int] NULL,
[Name] [varchar](100) NULL
) ON [PRIMARY]
GO

Step 2 :
Create a sequence object with CYCLE property.

--Create a sample Sequence object
--DROP SEQUENCE [dbo].[Seq_Sample]
--GO
CREATE SEQUENCE [dbo].[Seq_Sample]
AS [tinyint]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 2
CYCLE
GO
--

Step 3 :
Insert few records in the above sample table using sequence object.

USE [AdventureWorks2012]
GO
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-1')
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-2')
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-3')
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-4')
GO

Step 4 :
Browse the table. Given below is the script.

USE [AdventureWorks2012]
GO
SELECT * FROM [tbl_sample]
GO
--OUTPUT

As you can see in the above result set, there are duplicate IDs in the ID column. Ooopsss it does not enforce uniqueness by default. So what is the solution ?

SOLUTION :
Let me explain the solution step by step.

Step 1 :
Create a sample table. Given below is the script.

USE [AdventureWorks2012]
GO
--DROP TABLE [dbo].[tbl_sample]
--GO
--Create a sample Table
CREATE TABLE [dbo].[tbl_sample]
(
[ID] [int] NULL,
[Name] [varchar](100) NULL
) ON [PRIMARY]
GO

Step 2 :
Create a unique index on column ID of sample table.

USE [AdventureWorks2012]
GO
CREATE UNIQUE INDEX AK_tbl_sample_ID
ON dbo.tbl_sample (ID)
GO

Step 3 :
Create a sequence object with NO CYCLE property.

USE [AdventureWorks2012]
GO
--Create a sample Sequence object
--DROP SEQUENCE [dbo].[Seq_Sample]
--GO
CREATE SEQUENCE [dbo].[Seq_Sample]
AS [tinyint]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 4
NO CYCLE
GO
--

Step 4 :
Insert few records in the above sample table using sequence object to check whether the uniqueness is enforced or not.

USE [AdventureWorks2012]
GO
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-1')
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-2')
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-3')
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-4')
GO
--OUTPUT

Step 5 :
Browse the table again. Given below is the script.

USE [AdventureWorks2012]
GO
SELECT * FROM [tbl_sample]
GO
--OUTPUT

Conclusion :
Whenever you need to enforce uniqueness in the table using sequence object, make sure you have a UNIQUE INDEX on that particular column of the table. Also make sure that SEQUENCE object has NO CYCLE property.

Read Full Post »