Home > SQL Server "Denali" > SEQUENCE–How to create Identity Key across tables in SQL Server “Denali” ?

SEQUENCE–How to create Identity Key across tables in SQL Server “Denali” ?


SQL Server “Denali” introduces a new feature called “SEQUENCE”. SEQUENCE is a user-defined object that generates a sequence of numeric values according to specified options.

Its not bound to a table like IDENTITY, You can use SEQUENCE across tables

Supported Data Types for creating SEQUENCE are as follows:

  • TinyInt
  • SmallInt
  • Int
  • BigInt
  • Decimal
  • Numeric
  1. /* Create Sequence With Start Values */
  2. CREATE SEQUENCE RunningNumbers
  3.     START WITH 1
  4.     INCREMENT BY 1;
  5. GO

 

The above code creates a simple SEQUENCE with starting number as 1 and increments by 1

To get the next number in SEQUENCE, you need to use “NEXT VALUE” option

  1. SELECT (NEXT VALUE FOR RunningNumbers) AS [RunningNumbers];

image

The below code might syntactically look same as the SEQUENCE "- RunningNumbers, but if you look at the output, its entirely different

  1. /* Create Sequence Without Start Values */
  2. CREATE SEQUENCE RunningNumbers2
  3.     INCREMENT BY 1;
  4. GO

  1. SELECT (NEXT VALUE FOR RunningNumbers2) AS [RunningNumbers2];

If you see the output from RunningNumbers2, you will be getting a Negative value, its due to not specifying the Start number, so make sure you specify a Positive Start Number if you don’t want your table keys to Negative

image

Other options available in Creating Sequence are as follows:

  1.  
  2. CREATE SEQUENCE RunningNumbers3
  3.    AS tinyint
  4.     START WITH 1
  5.     INCREMENT BY 1
  6.     MINVALUE 1
  7.     MAXVALUE 5
  8.     CYCLE ;
  9. GO

 

You can specify Minimum Value and Maximum Value and whether to Cycle the numbers when reaching maximum value or not

 

  1. SELECT NEXT VALUE FOR RunningNumbers3 AS ID, Name FROM sys.objects ;
  2. GO

 

If you see the below output, for every 5 records, the IDs are recycling and starts from 1 again

image

Restarting the Sequence

To restart the Sequence, you need to ALTER the sequence with RESTART option and start value

  1. /* Restart the Sequence */
  2.  
  3. ALTER SEQUENCE [dbo].[RunningNumbers2]
  4. RESTART WITH 1

  1.  
  2. SELECT (NEXT VALUE FOR RunningNumbers2) AS [RunningNumbers2];

Since we restarted the Sequence with 1, Now we are getting output as 1 for RunningNumber2

image

Find the available Sequences in a database

  1. SELECT
  2.     object_id, name, type_desc, start_value, current_value, increment,
  3.     minimum_value, maximum_value, is_cycling, is_cached, cache_size
  4. FROM sys.sequences

You can Sys.Sequence system view to query the list of available Sequences in a database

image

How to use it as Identity across tables ?

Create tables for each reqion

  1. — Create tables
  2. CREATE TABLE Orders_West
  3.     (OrderID int PRIMARY KEY,
  4.     Name varchar(20) NOT NULL,
  5.     Qty int NOT NULL);
  6. GO
  7.  
  8.  
  9. CREATE TABLE Orders_East
  10.     (OrderID int PRIMARY KEY,
  11.     Name varchar(20) NOT NULL,
  12.     Qty int NOT NULL);
  13. GO
  14.  
  15. CREATE TABLE Orders_South
  16.     (OrderID int PRIMARY KEY,
  17.     Name varchar(20) NOT NULL,
  18.     Qty int NOT NULL);
  19. GO
  20.  
  21. CREATE TABLE Orders_North
  22.     (OrderID int PRIMARY KEY,
  23.     Name varchar(20) NOT NULL,
  24.     Qty int NOT NULL);
  25. GO

 

Insert data to table using Sequences

  1. — Insert Four records
  2. INSERT Orders_West (OrderID, Name, Qty)
  3.     VALUES (NEXT VALUE FOR RunningNumbers2, 'Apples', 2) ;
  4. INSERT Orders_East (OrderID, Name, Qty)
  5.     VALUES (NEXT VALUE FOR RunningNumbers2, 'Oranges', 1) ;
  6. INSERT Orders_South (OrderID, Name, Qty)
  7.     VALUES (NEXT VALUE FOR RunningNumbers2, 'Grapes', 1) ;
  8. INSERT Orders_North (OrderID, Name, Qty)
  9.     VALUES (NEXT VALUE FOR RunningNumbers2, 'Banana', 1) ;
  10.  
  11. GO

 

Query the inserted data

  1. SELECT * FROM Orders_West
  2. UNION
  3. SELECT * FROM Orders_East
  4. UNION
  5. SELECT * FROM Orders_North
  6. UNION
  7. SELECT * FROM Orders_South

 

If you see the Union output from 4 different tables, we are able to create a identity column across 4 tables, This will be very helpful when you horizontally partition data across tables

image

Delete the Sequences

  1. DROP SEQUENCE RunningNumbers;
  2. DROP SEQUENCE RunningNumbers2;

I hope you all find this information about Sequences useful and informative !!!

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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: