Explore SQL Server 2012: New Sequence Object

The sample code below is a quick run down of the new sequence object introduced in SQL Server 2012.


USE [tsql2012];

-- SEQUENCE

-- using default where does this one start?
--DROP SEQUENCE myTestSequence;
CREATE SEQUENCE myTestSequence;
select NEXT VALUE FOR myTestSequence;

-- using int... where does this one start?
--DROP SEQUENCE myTestSequence2;
CREATE SEQUENCE myTestSequence2
 AS INT;
select NEXT VALUE FOR myTestSequence2;

-- using smallint
--DROP SEQUENCE myTestSequence3;
CREATE SEQUENCE myTestSequence3
 AS SMALLINT;
select NEXT VALUE FOR myTestSequence3;

-- using tinyint... where does this one start?
--DROP SEQUENCE myTestSequence4;
CREATE SEQUENCE myTestSequence4
 AS TINYINT;
select NEXT VALUE FOR myTestSequence4;

-- start it at 0
-- DROP SEQUENCE myTestSequence5;
CREATE SEQUENCE myTestSequence5
 START WITH 0;
select NEXT VALUE FOR myTestSequence5;
DROP SEQUENCE ordersKeySequence;

CREATE SEQUENCE ordersKeySequence
 AS bigint
 START WITH 100
 INCREMENT BY 1 ;
-- Create a table
--DROP TABLE Orders;
CREATE TABLE Orders
 (OrderID int PRIMARY KEY,
 Name varchar(20) NOT NULL,
 Qty int NOT NULL);
GO
-- Insert three records
INSERT Orders (OrderID, Name, Qty)
 VALUES (NEXT VALUE FOR ordersKeySequence, 'Hat', 2) ;
INSERT Orders (OrderID, Name, Qty)
 VALUES (NEXT VALUE FOR ordersKeySequence, 'Shirt', 1) ;
INSERT Orders (OrderID, Name, Qty)
 VALUES (NEXT VALUE FOR ordersKeySequence, 'Shoes', 1) ;
GO

-- View the table
SELECT * FROM Orders ;
GO
-- Another way to do it.
DECLARE @NextID int ;
SET @NextID = NEXT VALUE FOR ordersKeySequence;
INSERT Orders (OrderID, Name, Qty)
 VALUES (@NextID, 'Belt', 2) ;
GO
SELECT * FROM Orders ;
GO
-- the easy way

DROP TABLE Orders;
CREATE TABLE Orders
 (OrderID int PRIMARY KEY DEFAULT NEXT VALUE FOR ordersKeySequence,
 Name varchar(20) NOT NULL,
 Qty int NOT NULL);
GO
-- Insert three records
INSERT Orders (Name, Qty) VALUES ('Hat', 2) ;
INSERT Orders (Name, Qty) VALUES ('Shirt', 1) ;
INSERT Orders (Name, Qty) VALUES ('Shoes', 1) ;
GO

-- View the table
SELECT * FROM Orders ;
GO

-- TRANSACTIONS
-- DROP SEQUENCE myTestSequence6;
CREATE SEQUENCE myTestSequence6
 START WITH 0;
select NEXT VALUE FOR myTestSequence6;

BEGIN TRANSACTION;
select NEXT VALUE FOR myTestSequence6;
COMMIT TRANSACTION;

select NEXT VALUE FOR myTestSequence6;

-- now what happens if we roll it back??
select NEXT VALUE FOR myTestSequence6;

BEGIN TRANSACTION;
select NEXT VALUE FOR myTestSequence6;
ROLLBACK TRANSACTION;

select NEXT VALUE FOR myTestSequence6;

USE tsql2012;

CREATE SEQUENCE withOverSequence
 AS bigint
 START WITH 100
 INCREMENT BY 1 ;
-- SEQUENCEs with the OVER clause
SELECT NEXT VALUE FOR withOverSequence OVER(ORDER BY year),
 *
FROM REVENUE
ORDER BY departmentID, year;



-- RESETTING A SEQUENCE
SELECT NEXT VALUE FOR withOverSequence ;

ALTER SEQUENCE withOverSequence
RESTART WITH 1 ;

SELECT NEXT VALUE FOR withOverSequence ;

USE [tsql2012];

-- SEQUENCE
-- generating letters with a sequence.
DROP SEQUENCE lettersSequence;
CREATE SEQUENCE lettersSequence
 AS tinyint
 --START WITH 65
 MINVALUE 65
 MAXVALUE 90
 CYCLE
 INCREMENT BY 1 ;

SELECT NEXT VALUE FOR lettersSequence;
--DROP SEQUENCE numberSequence;
CREATE SEQUENCE numbersSequence
 AS INT
 START WITH 0;
select NEXT VALUE FOR numbersSequence;

DROP TABLE Students;
CREATE TABLE Students
 (UserID varchar(30) PRIMARY KEY DEFAULT char(NEXT VALUE FOR lettersSequence)
                      + CAST(NEXT VALUE FOR numbersSequence as VARCHAR),
 Name varchar(20) NOT NULL);
GO
-- Insert three records
set nocount on;
INSERT Students (Name) VALUES ('Some Name');
GO 1000
SELECT *
 FROM STUDENTS
 ORDER BY UserID;

Just part of my Whats New in TSQL 2012 presentation at SQL Saturday 166.

SEQUENCE Sample Code from SQL Saturday in Olympia WA

Leave a Reply Cancel reply