Technical Article

Using Seqence Container instead of Identity

,

This script is an example of how to use the Sequence container, which is similar to Identity. 

The Sequence number can be used just like identity, but the best advantage is visible when we don't want to on or off the identity property when we need to add explicity value in the column.

IF NOT EXISTS (SELECT TOP 1 NULL FROM sys.sequences where name = 'test01')
CREATE SEQUENCE [dbo].[test01] 
 AS [dbo].[HVCIDdt]
 START WITH 1
 INCREMENT BY 1
 MINVALUE -9999999999999999
 MAXVALUE 9999999999999999
 CACHE 
GO


IF NOT EXISTS (SELECT TOP 1 NULL FROM sys.tables where name = 'T1')
CREATE TABLE dbo.T1
(
ID [HVCIDdt]NOT NULL DEFAULT (NEXT VALUE FOR dbo.[test01]),
 Value INT
)
GO

INSERT INTO T1(Value)
SELECT 1

INSERT INTO T1(Value)
SELECT 2

INSERT INTO T1(Value)
SELECT 3

select * from T1

Rate

2.75 (4)

Share

Share

Rate

2.75 (4)