My bad; what I should be blogging as Part 1, I am doing as PART 2. Yesterday, I blogged on how SEQUENCE objects in DENALI behave within transactions. You can see that here;
Today, I want to show you how you can use SEQUENCE numbers instead of IDENTITY columns to insert global serial values in your tables.
First create the SEQUENCE object:
USE tempdb GO CREATE SEQUENCE dbo.TestID AS INT MINVALUE 1 NO MAXVALUE START WITH 1;
Next, create two tables:
create table t1 ( orderID integer default (next value for dbo.TestID), OrderDesc varchar(50) ) create table t2 ( orderID integer default (next value for dbo.TestID), OrderDesc varchar(50) )
In the above code, you can observe that we have put the NEXT VALUE statement as a default for OrderID column. This is how the next number of a SEQUENCE object would be inserted for every new record.
Let us insert some data and test:
insert into t1 (OrderDesc) values('AMit') GO insert into t2(OrderDesc) values('AMit') GO
Run the above code 3 or 4 times.
And check what has been inserted:
select * from t1 GO select * from t2 GO
Here is the output:
This is how you can use SEQUENCE object as a global serial number generator, common to multiple tables. In my next post I shall try to show you a few more things with SEQUENCE objects.