SQL Sequences and Create Sequence of Numbers in SQL Server 2012
In SQL Server, t-sql developers sometimes require to create sequence of numbers which will be used in more than one sql tables.
One of the SQL Server 2012 features and enhancements in t-sql development is Sequence Objects.
A SQL Server sequence object generates sequence of numbers just like an identity column in sql tables.
But the advantage of sequence numbers is the sequence number object is not limited with single sql table.
There is no direct relation between tables, table identity columns and number sequences.
This independency gives the power of SQL Server sequence objects first introduced with Microsoft SQL Server 2012 CTP 1 (code name Denali).
On the left the programmability features of a Microsoft SQL Server 2012 database is listed. You can see the Sequences node with two number sequences are created.
On the right Microsoft SQL Server 2008 database programmability features does not have any Sequences node.
SQL programmers can use sequence numbers to create consecutive numbers, or to create a number sequence like a number range.
In "What is new in SQL Server 2011 T-SQL" section, creating sequence numbers is managed by Create Sequence command.
CREATE SEQUENCE [schema_name . ] sequence_name
[ AS { built_in_integer_type | user-defined_integer_type } ]
| START WITH <constant>
| INCREMENT BY <constant>
| { MINVALUE <constant> | NO MINVALUE }
| { MAXVALUE <constant> | NO MAXVALUE }
| { CYCLE | NO CYCLE }
| { CACHE [<constant> ] | NO CACHE }
In order to use number sequences, t-sql developers can use Next Value For function and sp_sequence_get_range function.
The sql sequence Next Value For is used to fetch a single number from the sequence.
On the other hang sql developers can get a range of sequence numbers by using sp_sequence_get_range function.
How to Create Sequence of Numbers in SQL Server 2012
T-SQL developers can use Create Sequence command in order to create sequence numbers in SQL Server 2012 databases.
The below t-sql CREATE SEQUENCE code is showing how to create sequence objects in SQL Server 2012
-- Create sequence of numbers starting from 1 with increment by 1
CREATE SEQUENCE SequenceOfNumbers
START WITH 1
INCREMENT BY 1;
GO
-- Create sequence of numbers starting from -2,147,483,648 with increment by 1
CREATE SEQUENCE SequenceOfIntegers
INCREMENT BY 1;
GO
It is also possible to create sequence of numbers in SQL Server 2012 using SQL Server Management Studio.
How to Read from Sequence of Numbers using Next Value For Function
After creating Sequence object, now we can get the first number in sequence using T-SQL Next Value For function.
SELECT
(NEXT VALUE FOR SequenceOfNumbers) AS [NextVal from SequenceOfNumbers],
(NEXT VALUE FOR SequenceOfIntegers) AS [NextVal from SequenceOfIntegers]
The output from SELECT NEXT VALUE FOR function is the next step from the number sequence object as seen in the below screenshot.
What is important for number sequence objects in SQL2012 is if a start point is not specified with START WITH option, the sequence will generate integers starting from -2147483648.
The upper bound value for number sequences id 2147483647.
SQL Server 2012 Number Sequence Sample SQL Codes
Now let's create two sql tables in our SQL Server 2012 sample databases.
Note if you want to work on a real Microsoft SQL Server 2012 sample database please refer to Download SQL Server 2012 Sample Databases including AdventureWorks Database
-- Create sql table Customers
CREATE TABLE Customers (
CustomerID int Identity (1,1) PRIMARY KEY,
PersonID int,
FirstName nvarchar(100) NOT NULL,
LarstName nvarchar(100) NOT NULL,
);
GO
-- Create sql table Employees
CREATE TABLE Employees (
EmployeeID int Identity(1,1) PRIMARY KEY,
PersonID int,
FirstName nvarchar(100) NOT NULL,
LarstName nvarchar(100) NOT NULL
)
GO
-- Create sequence of numbers for people
CREATE SEQUENCE SequenceOfPerson
START WITH 1
INCREMENT BY 1;
GO
After you create table objects and create sequence object with given t-sql code, we can now insert rows into sql tables and see how NEXT VALUE FOR Sequence object behaves.
-- Insert new rows into sql tables
INSERT INTO Employees (
PersonID, FirstName, LarstName
) VALUES (
NEXT VALUE FOR SequenceOfPerson, 'Eralper', 'YILMAZ'
);
INSERT Customers (PersonID, FirstName, LarstName)
VALUES (NEXT VALUE FOR SequenceOfPerson, 'Bill', 'GATES') ;
INSERT Employees SELECT NEXT VALUE FOR SequenceOfPerson, 'Robert', 'Vieira' ;
INSERT Customers SELECT NEXT VALUE FOR SequenceOfPerson, 'Ken', 'England' ;
SELECT * FROM Employees ;
SELECT * FROM Customers ;
GO
The output of the SELECT statements is predictable.
Another advanced t-sql sample code can be used to insert employees from a Linked SQL Server instance.
The following script is selecting Employee data from Microsoft SQL Server 2008 AdventureWorks sample database using OPENQUERY method.
And inserts employee information into a table with Sequence Number from SQL Sequence object.
INSERT Employees
SELECT
NEXT VALUE FOR SequenceOfNumbers, FirstName, LastName
FROM OPENQUERY([Kodyaz],'
SELECT
FirstName, LastName,
Cast(AdditionalContactInfo as VARCHAR(MAX)) as XML_Data
FROM AdventureWorks.Person.Contact C
INNER JOIN AdventureWorks.HumanResources.Employee E
ON E.ContactId = C.ContactId
'
) e
sp_sequence_get_range
sp_sequence_get_range stored procedure reads a range of numbers from a number sequence object in SQL Server 2012 database.
The range size can be specified using the sp_sequence_get_range @range_size parameter.
When sp_sequence_get_range procedure is executed, sp returns data related with number range requested.
The most important meta data about the read range of numbers include, the number range start number or first number, the last number in the range, increment value in the number range, etc.
Here is the t-sql syntax of sp_sequence_get_range stored procedure which is used to return back a range of integers from a SQL Server 2012 number sequence object.
sp_sequence_get_range [ @sequence_name = ] N'<sequence_name>'
, [ @range_size = ] range_size
, [ @range_first_value = ] range_first_value OUTPUT
[, [ @range_last_value = ] range_last_value OUTPUT ]
[, [ @range_cycle_count = ] range_cycle_count OUTPUT ]
[, [ @sequence_increment = ] sequence_increment OUTPUT ]
[, [ @sequence_min_value = ] sequence_min_value OUTPUT ]
[, [ @sequence_max_value = ] sequence_max_value OUTPUT ]
[ ; ]
Now we can use sp_sequence_get_range to read a range of integers from our previous sample sequence objects.
DECLARE
@sequence_name nvarchar(100) = 'SequenceOfNumbers',
@range_size int = 10,
@range_first_value sql_variant,
@range_last_value sql_variant,
@range_cycle_count int,
@sequence_increment sql_variant,
@sequence_min_value sql_variant,
@sequence_max_value sql_variant;
EXEC sp_sequence_get_range
@sequence_name = @sequence_name,
@range_size = @range_size,
@range_first_value = @range_first_value OUTPUT,
@range_last_value = @range_last_value OUTPUT,
@range_cycle_count = @range_cycle_count OUTPUT,
@sequence_increment = @sequence_increment OUTPUT,
@sequence_min_value = @sequence_min_value OUTPUT,
@sequence_max_value = @sequence_max_value OUTPUT;
SELECT
@range_size AS [Range Size],
@range_first_value AS [Sequence First Value],
@range_last_value AS [Sequence Last Value],
@range_cycle_count AS [Range Cycle Count],
@sequence_increment AS [Sequence Increment],
@sequence_min_value AS [Sequence Min Value],
@sequence_max_value AS [Sequence Max Value];
After you get number range using SQL Server sp_sequence_get_range sequence stored procedure, you can print values from the sequence using a similar t-sql script like shown below.
DECLARE @i int = 1;
WHILE( @range_first_value <= @range_last_value)
BEGIN
PRINT CAST(@i as VARCHAR(5)) + ' ' + CAST(@range_first_value as VARCHAR(10));
SELECT
@i = @i + 1,
@range_first_value = CONVERT(INT, @range_first_value) + CONVERT(INT, @sequence_increment);
END
How to Delete or Remove Number Sequence in SQL Database
In order to delete a sequence or to drop sequence object from SQL Server database, Drop Sequence command can be used like in below t-sql code.
DROP SEQUENCE SequenceOfNumbers