Tea and SQL

When SQL Server 2012 was announced I noticed that one of the developer features added was the Sequence. It’s actually very exciting and I can stop being picked on by my Oracle friends for FINALLY having this! I wrote a quick demo to take a look and show our developers how it can be used and different aspects of this new option in SQL Server 2012. I will warn you that I submitted a connect item about sequences which was noted as a bug and will be fixed in a future update: https://connect.microsoft.com/SQLServer/feedback/details/721322/sequence-cache-size-unexpected-results

/* create a test database */
CREATE DATABASE Sequence_Test;
GO

USE Sequence_Test;
GO

/* create test tables */
CREATE TABLE Order_Header
(
OrderID		BIGINT NOT NULL,
OrderDate	DATE NOT NULL,
CustomerID	INT NOT NULL
);
GO

CREATE TABLE Order_Detail
(
OrderID	BIGINT NOT NULL,
ProductID	INT NOT NULL,
Amount	INT NOT NULL,
);
GO

CREATE TABLE Products
(
ProductID	INT IDENTITY(1,1) NOT NULL,
ProductName	VARCHAR(256) NOT NULL
);
GO

CREATE TABLE Customers
(
CustomerID	INT IDENTITY(100000, 1) NOT NULL,
FirstName	VARCHAR(60) NOT NULL,
LastName	VARCHAR(60) NOT NULL,
Company		VARCHAR(120) NULL
);
GO

INSERT INTO Customers(FirstName, LastName, Company) VALUES ('Goof', 'Ball', 'GoofyBalls LTD.')
, ('Mickey','Mouse','Disney Corp.')
, ('Agent','Smith','')
GO

INSERT INTO Products(ProductName) VALUES ('Widgets')
,('Gadgets')
,('Pixie Dust')
,('Angry Mob')
,('Grab Bag')
,('Bouncy Balls')
,('Dog Treats')
GO

/* Create the sequence to tie order headers and details together */
CREATE SEQUENCE Orders_Sequence
AS BIGINT
START WITH 1000000
INCREMENT BY 1
MINVALUE 1000000
NO MAXVALUE
NO CYCLE
NO CACHE

GO

/* inspect new sequence */
SELECT * FROM sys.sequences WHERE [name] = 'Orders_Sequence'

/* Use the sequence once and re-check */

DECLARE @SeqNum BIGINT;
SELECT @SeqNum = NEXT VALUE FOR dbo.Orders_Sequence;

INSERT INTO Order_Header(OrderID, OrderDate, CustomerID) VALUES (@SeqNum, GETDATE(), 1);
INSERT INTO Order_Detail(OrderID, ProductID, Amount) VALUES (@SeqNum, 6, 100);
INSERT INTO Order_Detail(OrderID, ProductID, Amount) VALUES (@SeqNum, 7, 250);
GO

/* Inspect Sequence Again */
SELECT * FROM sys.sequences WHERE [name] = 'Orders_Sequence';
GO

/* inspect tables */
SELECT * FROM Order_Header;
SELECT * FROM Order_Detail;
GO

/* make another order */
DECLARE @SeqNum BIGINT;
SELECT @SeqNum = NEXT VALUE FOR dbo.Orders_Sequence;

INSERT INTO Order_Header(OrderID, OrderDate, CustomerID) VALUES (@SeqNum, GETDATE(), 2);
INSERT INTO Order_Detail(OrderID, ProductID, Amount) VALUES (@SeqNum, 1, 100);
INSERT INTO Order_Detail(OrderID, ProductID, Amount) VALUES (@SeqNum, 2, 20);
INSERT INTO Order_Detail(OrderID, ProductID, Amount) VALUES (@SeqNum, 3, 10);
GO

/* Inspect Sequence Again */
SELECT * FROM sys.sequences WHERE [name] = 'Orders_Sequence';
GO

/* inspect tables */
SELECT * FROM Order_Header;
SELECT * FROM Order_Detail;
GO

/* what happens if we rollback a transaction and then run the sequence again?
	are there holes in it like identity? */

BEGIN TRAN
DECLARE @SeqNum BIGINT;
SELECT @SeqNum = NEXT VALUE FOR dbo.Orders_Sequence;

INSERT INTO Order_Header(OrderID, OrderDate, CustomerID) VALUES (@SeqNum, GETDATE(), 3);
INSERT INTO Order_Detail(OrderID, ProductID, Amount) VALUES (@SeqNum, 4, 90000);
ROLLBACK
GO

DECLARE @SeqNum BIGINT;
SELECT @SeqNum = NEXT VALUE FOR dbo.Orders_Sequence;

INSERT INTO Order_Header(OrderID, OrderDate, CustomerID) VALUES (@SeqNum, GETDATE(), 3);
INSERT INTO Order_Detail(OrderID, ProductID, Amount) VALUES (@SeqNum, 4, 500);
GO

/* Inspect Sequence Again */
SELECT * FROM sys.sequences WHERE [name] = 'Orders_Sequence';
GO

/* inspect tables */
SELECT * FROM Order_Header;
SELECT * FROM Order_Detail;
GO

/* As you can see sequences still leaves holes as identity does */
USE master;
GO

DROP DATABASE Sequence_Test;
GO