And, for SQL Server 2012, back to the future with Sequences. I’ve been dorking with them this morning, please follow along. I’ve created a database called Company:
CREATE SCHEMA Orders;
GO
DROP TABLE Orders.TblOrders;
GO
CREATE TABLE Orders.TblOrders
(PKOrderID int PRIMARY KEY
,FKCustomerID varchar(40) NOT NULL
,OrderQuantity int
,OrderDate datetime2 NOT NULL DEFAULT
GETDATE()
);
GO
CREATE SEQUENCE Orders.SqnceTblOrders
START WITH 1
INCREMENT
BY 1;
GO
I’ve created a schema, table, and a sequence with the above. Check Books Online for all of the sequence options. The essentials for making this thing work appear to be START WITH and INCREMENT BY.
INSERT INTO
Orders.TblOrders (PKOrderID, FKCustomerID, OrderQuantity)
VALUES (NEXT VALUE FOR Orders.SqnceTblOrders, 1, 133);
INSERT INTO
Orders.TblOrders (PKOrderID, FKCustomerID, OrderQuantity )
VALUES (NEXT VALUE FOR Orders.SqnceTblOrders, 1, 345);
INSERT INTO
Orders.TblOrders (PKOrderID, FKCustomerID, OrderQuantity)
VALUES (NEXT VALUE FOR Orders.SqnceTblOrders, 1, 111);
GO
SELECT * FROM Orders.TblOrders;
GO
First thing that I thought of was…remove the NEXT VALUE and simply throw it in a declare:
DECLARE @seqValue int = NEXT VALUE FOR Orders.SqnceTblOrders
INSERT INTO
Orders.TblOrders (PKOrderID, FKCustomerID, OrderQuantity)
VALUES (@seqValue, 1, 111);
GO
SELECT * FROM Orders.TblOrders; GO
One thing that I don’t like is that sequence works like IDENTITY for values in a transaction; if it’s rolled back, when you insert again, it picks back up with the next value. Nope, don’t like that behavior. Run the following:
BEGIN TRANSACTION
INSERT INTO
Orders.TblOrders (PKOrderID, FKCustomerID, OrderQuantity)
VALUES (NEXT VALUE FOR Orders.SqnceTblOrders, 1, 133);
ROLLBACK
INSERT INTO
Orders.TblOrders (PKOrderID, FKCustomerID, OrderQuantity)
VALUES (NEXT VALUE FOR Orders.SqnceTblOrders, 1, 133);
SELECT * FROM Orders.TblOrders;
GO
If, however, you implement the above declare you could possibly save the value if some sort of retry logic is maintained:
DECLARE @seqValue int = NEXT VALUE FOR Orders.SqnceTblOrders
BEGIN TRANSACTION trnOrderInsert
INSERT Orders.TblOrders (PKOrderID, FKCustomerID, OrderQuantity)
VALUES (@seqValue, 1, 111);
SELECT * FROM Orders.TblOrders;
ROLLBACK TRAN trnOrderInsert
INSERT INTO
Orders.TblOrders (PKOrderID, FKCustomerID, OrderQuantity)
VALUES (@seqValue, 1, 111);
SELECT * FROM Orders.TblOrders;
GO
How about more than one on a table? Looks like it works…
ALTER TABLE Orders.TblOrders
ADD RowNumber INT;
CREATE SEQUENCE Orders.SqnceRowNumber
START WITH 1
INCREMENT BY 1;
GO
DECLARE @seqValue int = NEXT VALUE FOR Orders.SqnceTblOrders;
DECLARE @RowNumber int = NEXT VALUE FOR Orders.SqnceRowNumber;
INSERT Orders.TblOrders (PKOrderID, FKCustomerID, OrderQuantity, RowNumber)
VALUES (@seqValue, 1, 111, @RowNumber);
GO
SELECT * FROM Orders.TblOrders;
GO
Maybe as a constraint:
CREATE SEQUENCE Orders.SqnceSequence3
START WITH 1
INCREMENT
BY 1;
GO
ALTER TABLE Orders.TblOrders
ADD SequenceWithConstraint INT DEFAULT NEXT VALUE FOR Orders.SqnceSequence3;
GO
Now insert some data…looks like as a default constraint might be a cool way to throw a sequence in, but again this will lose a bit of control if you are going to use it with multiple tables via a variable as shown above.
DECLARE @seqValue int = NEXT VALUE FOR Orders.SqnceTblOrders;
DECLARE @RowNumber int = NEXT VALUE FOR Orders.SqnceRowNumber;
INSERT Orders.TblOrders (PKOrderID, FKCustomerID, OrderQuantity, RowNumber)
VALUES (@seqValue, 1, 111, @RowNumber);
GO
SELECT * FROM Orders.TblOrders;
GO
One thing to consider maybe…it appears that there is no checking or pairing for a sequence – Books online states the following:
Sequences, unlike identity columns, are not associated with specific tables.
This means that outside of an application you better darn sure be careful which sequence you’re calling, because you can call whichever sequence that you may have previously created:
DROP TABLE #Test
GO
CREATE TABLE #Test
(ID int)
GO
INSERT INTO #Test (ID) VALUES (NEXT VALUE FOR Orders.SqnceTblOrders);
GO
SELECT * FROM #Test;
GO
There’s a big section in BOL with caching. Maybe we’ll look at that one next week.
Thanks for reading,
Lee
-----------------------------
Sequences and IDENTITY…have to keep giving Joe Celko something to bitch about, right?
