SEQUENCE in SQL Server 2012

Up to SQL Server 2008 R2, to generate the sequential number we have one of the option is identity. As we know, identity is specific to column of a table only SO.........

What if, we want to generate sequential numbers across tables?

What if, we want to get the next sequence number even before inserting the new record?

So SQL Server 2012 (Denali) introduced, “Sequence Object” as the solution of above mention concerns.

WHAT IS SEQUENCE OBJECT?

In one line, The SEQUENCE object is one of the new T-SQL built-in types that automatically generate sequential numbers in specific conditions and settings.

A Sequence object is user-defined schema-bound object type. Sequence object generates the next sequential number as per the definition with which the sequence object has been created. The next sequential number could be either in ascending or descending order at a defined interval, as per definition. Sequence object may reuse/recycle numbers if it reaches the limit, if you have specified to recycle while creating the sequence object.

TSQL statement to create a sequence object:-

CREATE SEQUENCE  sq_Test  AS INT *     START WITH 1       INCREMENT BY 1       MINVALUE 1       MAXVALUE 10000       CYCLE       CACHE 100;   GO

*If no data type is provided, the bigint data type is used as the default.

Description of various clauses of Sequence object:-

·         START WITH 1

o   Identifies the starting number in the sequence

·         INCREMENT BY 1

o   The incrementing value of the sequence

·         MINVALUE

o   The minimum value the sequence can produce

·         MAXVALUE

o   The maximum vale the sequence can produce

·         CYCLE

o   If the MAXVALUE is set with the CYCLE argument provided, when the MAXVALUE is reached the sequence will cycle to the MINVALUE and start all over.

·         CACH 100;

o   If a CACHE argument is provided, SQL Server will cache (store in memory) the amount of values specified.

Once the sequence object has been created, you can use the “”Next Value For”” function, to generate a sequence value from it. It’s a non-deterministic function.

Unlike identity columns, a sequence is created independent of any table. A few interesting differences between sequence object and identity, simultaneously advantages of Sequence object are;

·         A Sequence object is independent of any table, whereas the Identity column property is table specific.

·         Unlike Identity, you can generate a new sequence value before using it in a SQL Statement .

·         You can define both the minimum & maximum values, as well as cycling & cache size options for a sequence.

·         Unlike Identity, a SQL object will increment its value only when it is explicitly called.

·         If we want to share a single series of numbers between multiple tables or multiple columns within a table.

·         The application must restart the number series when a specified number is reached. For example, after assigning values 1 through 10, the application starts assigning values 1 through 10 again.

·         The application requires sequence values to be sorted by another field. The NEXT VALUE FOR function can apply the OVER clause to the function call. The OVER clause guarantees that the values returned are generated in the order of the OVER clause's ORDER BY clause.

·         An application requires multiple numbers to be assigned at the same time. For example, an application needs to reserve five sequential numbers. Requesting identity values could result in gaps in the series if other processes were simultaneously issued numbers. Calling sp_sequence_get_range can retrieve several numbers in the sequence at once.

Limitations:

The NEXT VALUE FOR function cannot be used in the following situations:

·         When a database is in read-only mode.

·         As an argument to a table-valued function.

·         As an argument to an aggregate function.

·         In subqueries including common table expressions and derived tables.

·         In views, in user-defined functions, or in computed columns.

·         In a statement using the DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT operator.

·         In conditional expressions using CASE, CHOOSE, COALESCE, IIF, ISNULL, or NULLIF.

·         In a VALUES clause that is not part of an INSERT statement.

·         In the definition of a check constraint.

·         In a statement using TOP, OFFSET, or when the ROWCOUNT option is set.

·         In the WHERE clause of a statement.

Example:-

-- Create a SEQUENCE object on schema "dbo".
CREATE SEQUENCE test;

-- Verify if the test is created.
SELECT * FROM sys.sequences WHERE name = ‘test’;

-- To generate the next value from your Sequence object

SELECT NEXT VALUE FOR test;

-- To restart a Sequence object to its initial or specified value

ALTER SEQUENCE test   

RESTART;

-- To specify a restart value

ALTER SEQUENCE test   

RESTART WITH 10;

reachable: agrawals1307@gmail.com