Problem :
In earlier versions of SQL Server, to Generate sequence of numeric values for a Surrogate primary key or otherwise we used to use Identity property on a Numeric Column. The Identity column was tightly coupled with the Table and it had its own advantage and disadvantage. The main issue was if you want to have same Sequence to be shared accross many tables or columns , then we had to go for a workaround. In SuperType subtype kind of relations we used to face this problem of sharing same sequence across multiple tables.
Solution
In SQL Server 2011, Microsoft has added a new Object called Sequence which is more or less similar to Oracle Sequence object.
What is Sequence in SQL Server?
A sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle (repeat) as requested. Generally it is used to create auto generated Primary key (surrogate key) value.
Create Sequence
As usual, you have TSQL command and GUI. TSQL Syntax is as follows
CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH
[ INCREMENT BY
[ { MINVALUE [
[ { MAXVALUE [
[ CYCLE | { NO CYCLE } ]
[ { CACHE [
[ ; ]
You can refer this Link for more detail on Syntax.
Eg. Creating a Sequence with all arguments
CREATE SEQUENCE [dbo].Seq_EmployeeID
START WITH 0
INCREMENT BY 2
MINVALUE 0
MAXVALUE 2000
CYCLE
CACHE 20
Eg. Creating Table with Sequence as Default value for PK. This is another way of usage.
CREATE TABLE Audit.ProcessEvents
(
EventID int PRIMARY KEY CLUSTERED
DEFAULT (NEXT VALUE FOR Audit.EventCounter),
EventTime datetime NOT NULL DEFAULT (getdate()),
EventCode nvarchar(5) NOT NULL,
Description nvarchar(300) NULL
) ;
GO
Other system Functions and Objects related to Sequence
NEXT VALUE FOR : Generates a sequence number from the specified sequence object. The NEXT VALUE FOR function can be used in stored procedures and triggers. This function also have few limitations you may want to read the details here
Sys.Sequence : Contains a row for each sequence object in a database. For more info refer this link
sp_sequence_get_range: Returns a range of sequence values from a sequence object. The sequence object generates and issues the number of values requested and provides the application with metadata related to the range.
Modify Sequence
ALTER SEQUNCE : Modifies the arguments of an existing sequence object. If the sequence was created with the CACHE option, altering the sequence will recreate the cache. For more info refer this link
Drop a sequence
DROP SEQUENCE : After generating a number, a sequence object has no continuing relationship to the number it generated, so the sequence object can be dropped, even though the number generated is still in use. For more info refer this link
Important Architectural Consideration and Limitations
(a) By default Sequence uses BIGINT Datatype and it take 8 Byte. If the requirement is to store small number, then change the datatype to TinyInt, Smallint or Int accordingly
(b) If the starting value is not defined while creating, then SQL Server set it to the lowest value what that datatype can support. So by default if the datatype is BIGINT the starting number will be (-9,223,372,036,854,775,808)
(c) The Increment cannot be 0. If the increment is a negative value, the sequence object is descending; otherwise, it is ascending. By default the increment is 1
(d) If the requirement is to migrate Oracle Sequence to SQL Server, then select the datatype as Numeric(28,0)
(e) Specify CYCLE/NOCYCLE to define the behavior if t the sequence number reaches the maximum value
(f) SQL Server as of CTP 3 does not support Current Value
(g) Cache option is different from Oracle. In oracle by default database caches 20 Sequence but in SQL Server database engine select the size. If the cache option is enabled without specifying a cache size, the Database Engine will select a size. However, users should not rely upon the selection being consistent. Microsoft might change the method of calculating the cache size without notice.
(h) Sequence objects support ownership chaining. If the sequence object has the same owner as the calling stored procedure, trigger, or table (having a sequence object as a default constraint), no permission check is required on the sequence object. If the sequence object is not owned by the same user as the calling stored procedure, trigger, or table, a permission check is required on the sequence object
(i) Altering a column and Adding Identity is not possible in SQL Server. In such scenario if adding a new column with Identity , dropping the old column and renaming the new column is not possible then may be adding SEQUENCE as a default can be a workaround
How does it differ from Identity Column?
Sequences, unlike identity columns, are not associated with tables. An application refers to a sequence object to receive its next value. The relationship between sequences and tables is controlled by the application. User applications can reference a sequence object and coordinate the values keys across multiple rows and tables.
Unlike identity columns, whose values cannot be changed, sequence values are not automatically protected after insertion into the table. To prevent sequence values from being changed, use an update trigger on the table to roll back changes.
Performance Comparison of Sequence and Identity
Aaron Bertrand has covered this in detail. Check this Blog
Other than this I did test one more case, Table with Identity and Table with Sequence as Default for PK. In the table where sequence was used, I did not provide value for the PK column so that default gets fired. In this case Identity table insert perform almost 60% Better from a table with Sequence as default. Script to Reproduce the scenario I tested is as follows :-
-- Table with sequence as default
CREATE TABLE [dbo].[SequencePerf](
[EventID] [int] NOT NULL,
[Description] [nvarchar](300) NULL,
PRIMARY KEY CLUSTERED
(
[EventID] ASC
)
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SequencePerf] ADD DEFAULT (NEXT VALUE FOR [Seq_EventId]) FOR [EventID]
GO
CREATE TABLE [dbo].[IdentityPerf](
[EventID] [int] IDENTITY(1,1) NOT NULL,
[Description] [nvarchar](300) NULL,
PRIMARY KEY CLUSTERED
(
[EventID] ASC
)
) ON [PRIMARY]
GO
-- Run both the insert in single batch and see the cost it take. In my case it was 71:29
Insert [SequencePerf] (Description)
Select top 100000 o.name From sys.objects o,sys.columns
Insert [IdentityPerf] (Description)
Select top 100000 o.name From sys.objects o,sys.columns
Different Scenario of Sequence Usage in SQL Server
Create database [TestDenali]
GO
USE [TestDenali]
GO
CREATE SEQUENCE [dbo].[Seq_EventId]
AS [bigint]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
CYCLE
CACHE 200
GO
CREATE TABLE [dbo].[SequenceTest](
[EventID] [int] NOT NULL ,
[Description] [nvarchar](300) NULL,
PRIMARY KEY CLUSTERED
(
[EventID] ASC
)
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SequenceTest] ADD DEFAULT (NEXT VALUE FOR [Seq_EventId]) FOR [EventID]
GO
-- Not providing EventID value so that Default
INSERT INTO dbo.[SequenceTest](Description) select top 100 Name from sys.objects
-- Inserting bulk number of records , getting sequence in Select
INSERT INTO dbo.[SequenceTest] ([EventID],description)
SELECT top 100 NEXT VALUE FOR Seq_EventId AS SecondUse, Name from sys.objects
-- Insert a single row
INSERT [SequenceTest] ([EventID],description)
VALUES (NEXT VALUE FOR Seq_EventId, 'Test') ;
-- Usage with SELECT INTO
SELECT NEXT VALUE FOR Seq_EventId AS EventID, Description
INTO [SequenceTest_clone]
FROM [SequenceTest] ;
-- Temp table
SELECT NEXT VALUE FOR Seq_EventId AS EventID, Description
INTO [#SequenceTest_clone]
FROM [SequenceTest] ;
-- Creating Temp table with Sequence as Defualt
CREATE TABLE #SequenceTest (
[EventID] [int] NOT NULL DEFAULT (NEXT VALUE FOR Seq_EventId) ,
[Description] [nvarchar](300) NULL,
PRIMARY KEY CLUSTERED
(
[EventID] ASC
)
) ON [PRIMARY]
INSERT #SequenceTest ([EventID],description)
VALUES (NEXT VALUE FOR Seq_EventId, 'Test') ;
INSERT #SequenceTest ( description)
VALUES ( 'Test') ;
Select *from #SequenceTest
-- Creating Table Variable with Sequence as Default
Declare @SequenceTest TABLE (
[EventID] [int] NOT NULL DEFAULT (NEXT VALUE FOR Seq_EventId) ,
[Description] [nvarchar](300) NULL)
INSERT @SequenceTest ([EventID],description)
VALUES (NEXT VALUE FOR Seq_EventId, 'Test') ;
INSERT @SequenceTest ( description)
VALUES ( 'Test') ;
Select *From @SequenceTest
-- Table Valued Parameter
CREATE TYPE [dbo].[SequenceTestTableType] AS TABLE
(
[EventID] INT DEFAULT (NEXT VALUE FOR Seq_EventId), description VARCHAR(128)
)
/*
Msg 11719, Level 15, State 1, Line 3
NEXT VALUE FOR function is not allowed in check constraints, default objects, computed columns, views,
user-defined functions, user-defined aggregates, sub-queries, common table expressions, or derived tables.
*/
Create view vwTestSequence
as
SELECT top 10000 NEXT VALUE FOR Seq_EventId AS SecondUse,description FROM dbo.Sequencetest
/*
Msg 11719, Level 15, State 1, Procedure vwTestSequence, Line 4
NEXT VALUE FOR function is not allowed in check constraints, default objects, computed columns, views,
user-defined functions, user-defined aggregates, sub-queries, common table expressions, or derived tables.
*/
-- Capture the Inserted Sequencenumber using OUTPUT. In SuperType-Subtype scenario may be usefule
Declare @tab table (Eventid int)
Insert [SequencePerf] (Description) OUTPUT inserted.[EventID] Into @tab
Select top 100000 o.name From sys.objects o,sys.columns
SELECT *From @tab
Reference
http://sqlblog.com/blogs/aaron_bertrand/archive/2010/11/11/sql-server-11-denali-using-sequence.aspx
http://blogs.msdn.com/b/ssma/archive/2011/07/12/converting-oracle-sequence-using-ssma-for-oracle-v5-1.aspx
Posts
