SEQUENCE Sample Code from SQL Saturday in Olympia WA
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.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
Leave a Reply