Sequence

Overview

Introduced in SQL Server 2012
User-defined object which generates sequence of numbers according to the property (Ascending/Descending/Cycle, etc..)
It can be defined as any Integer datatype, if not specified, default is BigInt

Sequence vs Identity

	Identity	Sequence
Association	With table	with database, can be used for multiple tables
Generation	generated only when we insert the data in table	can be generated even before inserting data using NEXT VALUE FOR
Cycling	not feasible	feasible, will restart the number once specified number is reached
Reserving number	not feasible, if we insert data from multiple places, it will take the number whatever is next	feasible, we can reserver 5 sequential numbers before inserting the data and later we can use them
Gaps	Generally no unless we have deliberately changed the Identity number, otherwise records will be in a sequence without gaps	One of the purpose of sequence is for multiple tables, gaps are usual

Usage

• Creating a sequence

CREATE SEQUENCE Schema.SequenceName

    AS int

    START WITH 1

    INCREMENT BY 1 ;

• Using sequence while inserting data

--Create the Test schema

CREATE SCHEMA Test ;

GO

-- Create a table

CREATE TABLE Test.Orders

    (OrderID int PRIMARY KEY,

    Name varchar(20) NOT NULL,

    Qty int NOT NULL);

GO

-- Create a sequence

CREATE SEQUENCE Test.CountBy1

    START WITH 1

    INCREMENT BY 1 ;

GO

-- Insert three records

INSERT Test.Orders (OrderID, Name, Qty)

    VALUES (NEXT VALUE FOR Test.CountBy1, 'Tire', 2) ;

INSERT test.Orders (OrderID, Name, Qty)

    VALUES (NEXT VALUE FOR Test.CountBy1, 'Seat', 1) ;

INSERT test.Orders (OrderID, Name, Qty)

    VALUES (NEXT VALUE FOR Test.CountBy1, 'Brake', 1) ;

GO

• Calling NEXT VALUE FOR before inserting the data

DECLARE @NextID int ;

SET @NextID = NEXT VALUE FOR Test.CountBy1;

-- Some work happens

INSERT Test.Orders (OrderID, Name, Qty)

    VALUES (@NextID, 'Rim', 2) ;

• Using Sequence in multiple tables

CREATE SCHEMA Audit ;

GO

CREATE SEQUENCE Audit.EventCounter

    AS int

    START WITH 1

    INCREMENT BY 1 ;

GO

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

CREATE TABLE Audit.ErrorEvents

(

    EventID int PRIMARY KEY CLUSTERED

        DEFAULT (NEXT VALUE FOR Audit.EventCounter),

    EventTime datetime NOT NULL DEFAULT (getdate()),

    EquipmentID int NULL,

    ErrorNumber int NOT NULL,

    EventDesc nvarchar(256) NULL

) ;

GO

CREATE TABLE Audit.StartStopEvents

(

    EventID int PRIMARY KEY CLUSTERED

        DEFAULT (NEXT VALUE FOR Audit.EventCounter),

    EventTime datetime NOT NULL DEFAULT (getdate()),

    EquipmentID int NOT NULL,

    StartOrStop bit NOT NULL

) ;

GO

INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)

    VALUES (248, 0) ;

INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)

    VALUES (72, 0) ;

INSERT Audit.ProcessEvents (EventCode, Description)

    VALUES (2735,

    'Clean room temperature 18 degrees C.') ;

INSERT Audit.ProcessEvents (EventCode, Description)

    VALUES (18, 'Spin rate threashold exceeded.') ;

INSERT Audit.ErrorEvents (EquipmentID, ErrorNumber, EventDesc)

    VALUES (248, 82, 'Feeder jam') ;

INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)

    VALUES (248, 1) ;

INSERT Audit.ProcessEvents (EventCode, Description)

    VALUES (1841, 'Central feed in bypass mode.') ;

-- The following statement combines all events, though not all fields.

SELECT EventID, EventTime, Description FROM Audit.ProcessEvents

UNION SELECT EventID, EventTime, EventDesc FROM Audit.ErrorEvents

UNION SELECT EventID, EventTime,

CASE StartOrStop

    WHEN 0 THEN 'Start'

    ELSE 'Stop'

END

FROM Audit.StartStopEvents

ORDER BY EventID ;

EventID	EventTime	Description
1	2013-07-28 06:10:36.673	Start
2	2013-07-28 06:10:36.677	Start
3	2013-07-28 06:10:36.677	Clean room temperature 18 degrees C.
4	2013-07-28 06:10:36.680	Spin rate threashold exceeded.
5	2013-07-28 06:10:36.680	Feeder jam
6	2013-07-28 06:10:36.680	Stop
7	2013-07-28 06:10:36.680	Central feed in bypass mode.

We can also see that we can define NEXT VALUE FOR as a default constraint, so that way we don't need to use it while inserting the data

• Repeating Sequence

CREATE SEQUENCE CountBy5

   AS tinyint

    START WITH 1

    INCREMENT BY 1

    MINVALUE 1

    MAXVALUE 5

    CYCLE ;

GO

SELECT NEXT VALUE FOR CountBy5 AS SurveyGroup, Name FROM sys.objects ;

GO

SurveyGroup	Name
1	sysrscols
2	sysrowsets
3	sysclones
4	sysallocunits
5	sysfiles1
1	sysseobjvalues
2	syspriorities

• Generating Sequence using OVER clause

SELECT NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) AS NutID, ProductID, Name, ProductNumber FROM Production.Product

WHERE Name LIKE '%nut%' ;

• Resetting the sequence

ALTER SEQUENCE Samples.IDLabel

RESTART WITH 1 ;

Offset & Fetch

Overview

This is the new feature introduced in SQL 2012.

OFFSET Specifies the number of rows to skip
FETCH Specifies the number of rows to return

Basically by using OFFSET & FETCH, we can get selected result in a particular order while sorting the data. So these are used along with ORDER By clause.

Usage

Here is the basic query.

SELECT TOP 5 * FROM Students ORDER BY StudentName

StudentID	StudentName	City
5	Dipa	Hyderabad
6	Kamlesh	Mumbai
8	Lalu	Hyderabad
9	Mahesh	Ahmedabad
4	Maulik	Redmond

Now, I want the result starting from 3rd row and I want to get total 4 rows starting from 3rd row, basically while sorting, I want to ignore first 2 rows, get only 4 rows and also want to ignore rest of the rows, then this is how I can achieve this

SELECT * FROM Students ORDER BY StudentName OFFSET 2 ROWS FETCH NEXT 4 ROWS ONLY

StudentID	StudentName	City
8	Lalu	Hyderabad
9	Mahesh	Ahmedabad
4	Maulik	Redmond
2	Megha	Redmond

I can also use variables instead of static value in OFFSET and FETCH

DECLARE @OFFSET INT = 2

DECLARE @FETCH INT = 3

SELECT * FROM Students ORDER BY StudentName OFFSET @OFFSET ROWS FETCH NEXT @FETCH + 1 ROWS ONLY

It will give me same result what we got earlier.

Limitation

• ORDER BY is mandatory
• OFFSET is mandatory with FETCH.. You cannot use only 1
• TOP cannot be combined with OFFSET & FETCH

OFFSET/FETCH vs ROW_NUMBER()

• First of all query is much simpler while using OFFSET/FETCH than ROW_Number, here is the comparison. Below query will give the same result with the query what we used earlier.

;WITH CTE AS

(

      SELECT      ROW_NUMBER() OVER(ORDER BY StudentName) RowNumber, *

      FROM  Students

)

SELECT      *

FROM  CTE

WHERE RowNumber BETWEEN 3 AND 6

• And if we look at the execution plan, then we can see, there is lot more going on while using ROW_Number than Offset/Fetch