RSS

SQL Server 2012 New Feature – The Sequence Object

23 Jul
SQL Server 2012 New Feature – The Sequence Object

One of the cool new features with SQL 2012 is the Sequence Object.  One of the worst parts of this feature is that whenever I mention it’s a cool new feature a lot of people say “that has been in Oracle forever.” Patience is a virtue!!

Anyway, so I like to consider the Sequence Object as a “Global Identity” that can be shared by many objects in the same database, or even across databases on the same server (I have not tried it via Linked Servers because I only have one test SQL 2012 server). You can take values from the Sequence Object and insert them into tables or you can have a column in a table default to a value from the Sequence Object. A warning to the wise: If you assign a default value for the table as a Sequence Object then you cannot drop it because of dependencies. That is actually a pretty good security measure.

I have included a code snippet so that you can play around with this new feature and I’ve put a few screen shots to show how you can manage it via the GUI. Make sure you run the code section by section; I have included a lot of comments to let you know what to expect while running it.

The code is a bit too long so I pasted it below the screen shots. You can also download the .SQL file by clicking here. I would recommend downloading the .SQL file if you plan to test this out since all the quotes will get messed up if you copy it from this page and paste it to a new query window. I’m still trying to figure out how to fix that.

Description of Screen shots:

Screen shot 1: Locating Sequence Object using GUI. Right click on the name of the object to see options. Properties (highlighted in red), dependencies (highlighted in green)

Screen shot 2: Properties screen. Click “Restart sequence” (highlighted in red) and then OK to restart the sequence. You can change the sequence min and max values and the incremental rate (highlighted in orange). Current value is displayed as the last line on the screen.

Screen shot 3: Dependencies screen. You can see what objects depend on the Sequence Object. The blue box shows tables that are dependent on it (these are the ones I created in the demo script attached).

 


/*Create a Test Database to play around in*/

CREATE DATABASE Test_DB

USE Test_DB
GO

/*Create the Test Sequence Object*/

CREATE SEQUENCE dbo.TestSequence
AS INT

MINVALUE 1
NO MAXVALUE
START WITH 1

/*Create a Test table to test the sequence with*/
/*This first set of test tables has the NEXT value for the Sequence object set to the default value of the ID column.
It will behave like an Identity column but instead, take its next value from the sequence which is value that can be shared across different tables*/

USE Test_DB

CREATE TABLE Test1
(
ID INT DEFAULT (NEXT value for dbo.TestSequence),
RandomText VARCHAR(50)
)

CREATE TABLE Test2
(
ID INT DEFAULT (NEXT value for dbo.TestSequence),
RandomText VARCHAR(50)
)

/*Inserting data into Table1 then Table2*/
INSERT INTO Test1(RandomText) VALUES (‘Insert1’)
INSERT INTO Test1(RandomText) VALUES (‘Insert2’)
INSERT INTO Test2(RandomText) VALUES (‘Insert3’)
INSERT INTO Test2(RandomText) VALUES (‘Insert4’)

/*Inserting data alternating between Table1 and Table2*/
INSERT INTO Test1(RandomText) VALUES (‘Insert5’)
INSERT INTO Test2(RandomText) VALUES (‘Insert6’)
INSERT INTO Test1(RandomText) VALUES (‘Insert7’)
INSERT INTO Test2(RandomText) VALUES (‘Insert8’)

/*Check out how the data looks*/

SELECT * FROM Test1
SELECT * FROM Test2

/*Reset Sequence back to 1*/
ALTER SEQUENCE [dbo].[TestSequence]
RESTART WITH 1

GO
/*Inserting data into Table1 then Table2*/
INSERT INTO Test1(RandomText) VALUES (‘Insert9’)
INSERT INTO Test1(RandomText) VALUES (‘Insert10’)
INSERT INTO Test2(RandomText) VALUES (‘Insert11’)
INSERT INTO Test2(RandomText) VALUES (‘Insert12’)
/*Inserting data alternating between Table1 and Table2*/
INSERT INTO Test1(RandomText) VALUES (‘Insert13’)
INSERT INTO Test2(RandomText) VALUES (‘Insert14’)
INSERT INTO Test1(RandomText) VALUES (‘Insert15’)
INSERT INTO Test2(RandomText) VALUES (‘Insert16’)

/*Check out how the data looks; sequence number should have reset*/
SELECT * FROM Test1
SELECT * FROM Test2
/*There should be duplicate IDs since the Sequence was reset*/

/*Manually increment sequence*/
SELECT NEXT VALUE FOR TestSequence
INSERT INTO Test1(RandomText) VALUES (‘Manually Incremented’)

/*Check out how the data looks*/
/*There should be an ID number that was skipped*/
SELECT * FROM Test1
SELECT * FROM Test2

/*Using Sequence in insert without the table being bound by the sequence object*/
CREATE TABLE Test3
(
ID int,
RandomText varchar(50)
)

/*Programmatically insert the next sequence in the insert statement*/
INSERT INTO test3 VALUES (Next Value for TestSequence,’Insert1′)
INSERT INTO test3 VALUES (Next Value for TestSequence,’Insert2′)
INSERT INTO test3 VALUES (Next Value for TestSequence,’Insert3′)
INSERT INTO test3 VALUES (Next Value for TestSequence,’Insert4′)

/*Check out how the data looks*/
/*The IDs in this table should continue where they left off for the inserts in Table 1 and 2*/
SELECT * FROM Test3

/*This statement will not work because the Sequence Object is being referenced by two tables as being the
default value*/
DROP SEQUENCE dbo.TestSequence

/*Clean up*/
USE Master
DROP DATABASE Test_DB

Advertisements
 

Tags: , , ,

4 responses to “SQL Server 2012 New Feature – The Sequence Object

  1. Jay

    August 21, 2012 at 3:13 PM

    Will this sequence object work in SQL cluster enviroment? Thanks

     
    • Ayman El-Ghazali

      August 21, 2012 at 4:08 PM

      Yes, because failover clustering provides high availability for the entire SQL instance which includes all databases (this is where the sequence object is stored).

       
  2. oracle dba

    August 30, 2012 at 6:01 AM

    Oh that’s good.. This sequence object also work in sql cluster environment… Very nice…

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: