Sequence

CREATE SEQUENCE (Transact-SQL)

Applies To: SQL Server 2012, SQL Server 2014, SQL Server 2016 Preview

Creates a sequence object and specifies its properties.

A sequence is a user-defined schema bound object that generates a sequence of numeric values according to the specification with which the sequence was created.

The sequence of numeric values is generated in an ascending or descending order at a defined interval and can be configured to restart (cycle) when exhausted.

Sequences, unlike identity columns, are not associated with specific tables.

Applications refer to a sequence object to retrieve its next value. The relationship between sequences and tables is controlled by the application. User applications can reference a sequence object and coordinate the values across multiple rows and tables.

Unlike identity columns values that are generated when rows are inserted, an application can obtain the next sequence number without inserting the row by calling the NEXT VALUE FOR function.

Use sp_sequence_get_range to get multiple sequence numbers at once.

Syntax:

  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 } ]

    [ ; ]

Arguments:

Sequence_name

Specifies the unique name by which the sequence is known in the database. Type is sysname.

[built_in_integer_type | user-defined_integer_type

A sequence can be defined as any integer type. The following types are allowed.

Tinyint - Range 0 to 255

Smallint - Range -32,768 to 32,767

Int - Range -2,147,483,648 to 2,147,483,647

Bigint - Range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

Decimal and numeric with a scale of 0.

Any user-defined data type (alias type) that is based on one of the allowed types.

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

START WITH <constant>

The first value returned by the sequence object. The START value must be a value less than or equal to the maximum and greater than or equal to the minimum value of the sequence object.

The default start value for a new sequence object is the minimum value for an ascending sequence object and the maximum value for a descending sequence object.

INCREMENT BY <constant>

Value used to increment (or decrement if negative) the value of the sequence object for each call to the NEXT VALUE FOR function.

If the increment is a negative value, the sequence object is descending; otherwise, it is ascending.

The increment cannot be 0.

The default increment for a new sequence object is 1.

[ MINVALUE <constant> | NO MINVALUE ]

Specifies the bounds for the sequence object. The default minimum value for a new sequence object is the minimum value of the data type of the sequence object. This is zero for the tinyint data type and a negative number for all other data types.

[ MAXVALUE <constant> | NO MAXVALUE

Specifies the bounds for the sequence object. The default maximum value for a new sequence object is the maximum value of the data type of the sequence object.

[ CYCLE | NO CYCLE ]

Property that specifies whether the sequence object should restart from the minimum value (or maximum for descending sequence objects) or throw an exception when its minimum or maximum value is exceeded. The default cycle option for new sequence objects is NO CYCLE.

Note that cycling restarts from the minimum or maximum value, not from the start value.

[ CACHE [<constant> ] | NO CACHE ]

Sql Server provides option to specify the CACHING options while Creating/Altering Sequence. Basically Sequence created with CACHE option improves the performance by reducing the number of disk IOs that are required to generate sequence numbers.

SEQUENCE CACHE Property:

[ CACHE [<constant> ] | NO CACHE ]

WHERE:

CACHE [<constant> ]: This property can be specified if we want the Sequence with caching enabled, where <constant> is the CACHE size. If the Sequence is Created without specifying CACHE Property then by DEFAULT Sql Server creates the Sequence with CACHING option enabled and as per MSDN the Database Engine will select a size. However, users should not rely upon the selection of the CACHE size being consistent, so it is always advisable to define the required CACHE size if we want to have Sequence Caching instead of relying on the default.

NO CACHE: We can specify this option if we don’t want the Sequence to use the CACHING.

Let us understand in detail the Performance implications of Creating a Sequence with NO CACHE option or with CACHE enabled option.

SEQUENCE WITHOUT CACHING (i.e. Sequence created with NO CACHE option):

Below diagram demonstrates the steps are performed by Sql Server whenever Next Value is requested from a Sequence object created with NO CACHE option

So, it is clear from the above diagram that, if Sequence object created with NO CACHE option each time a next sequence value is requested it will write the calculated value to the system table before returning it to the calling statement.

SEQUENCE WITH CACHING:

Sequence created with CACHE option improves the performance by minimizing the no of writes to the System table for the generation of the Sequence number. In case of a sequence object created with NO CACHE option each time a next sequence value is requested, it will write the calculated value to the system table before returning it to the calling statement. But if sequence is created with CACHE of Size 50, it will write to the System table only after each 50 sequence values are served.

Internals of Cache management
For a sequence object which is created with CACHE option, it will not pre-allocate the number of sequence numbers specified by the CACHE argument.

Instead CACHE is maintained by two In-Memory variables one for tracking the Sequence Current Value another for the number of values left in the cache. So for sequence CACHE management the memory used will always be two instances of the data type of the Sequence object. For example, if a cache size of 50 is chosen, SQL Server does not keep 50 individual values cached. It only caches the current value and the number of values left in the cache. This means that the amount of memory required to store the cache is always two instances of the data type of the sequence object.

Following steps are performed by Sql Server whenever Next Value is requested from a Sequence object created with CACHING enabled option:

Let us understand this Sequence Cache Management Internals with an example:

Create the Sequence object SequenceExample with CACHE size as 50 as below:

CREATE SEQUENCE dbo.SequenceExample AS INT     START WITH 1     INCREMENT BY 1     CACHE 50; GO

Let us request the first value from the Sequence object SequenceExample and verify that the last cached value 50 (because cache size is 50 and start value is 1 and increment value of the sequence is 1) is written to the system tables on the disk.

--Get First Sequence Value SELECT (NEXT VALUE FOR dbo.SequenceEx) As NextSeqValue --Verify the CACHE size SELECT * FROM SYS.SYSSCHOBJS WHERE ID = OBJECT_ID('dbo.SequenceEx') --Verify the Last CACHED value written to the system table SELECT * FROM SYS.SYSOBJVALUES WHERE OBJID = OBJECT_ID('dbo.SequenceEx')

From the above queries it is clear that when the first value is requested from the sequence, values 1 through 50 are made available from the two in-memory variables. The last cached value 50 is written to the system tables on the disk.

Now let us verify whether next sequence value 2 through 50 are made available from the two in-memory variables and no updates happen to the system table by using the below queries.

From the above queries results it is clear that no updates happened to the system tables when next sequence value is requested. This holds for all the next values till the sequence value 50.

By using the below queries read the sequence values from 3 through 50.

--Get sequence values from 3 to 50 SELECT (NEXT VALUE FOR dbo.SequenceEx) As NextSeqValue GO 48

Now the request for the next sequence value i.e. 51 will cause the cache to be allocated again. The new last cached value 100 will be written to the system tables. Let us verify this using the below queries:

From the results of the above queries it is clear that the request for the sequence value 51 causes the cache to be allocated again. The new last cached value 100 is written to the system tables.

If the Database Engine is stopped after you use 55 numbers, the next intended sequence number in memory 56 is written to the system tables, replacing the previously stored number 100 before database engine is stopped.

After SQL Server restarts and a sequence number is needed, the starting number is read from the system table 56. The cache amount of 50 numbers (56-106) is allocated to memory and the new last cached value 106 is written to the system tables.

If the Database Engine stops abnormally say for example due to power failure (To simulate this you can go to task manager and kill the Sql Server process, please don’t try this on production database). As the last sequence value we have read is the 56, so next sequence value expected is 57 but instead of 57 it will return 107. Any sequence numbers allocated to memory (but never requested by a user or application) are lost. This functionality may leave gaps, but guarantees that the same value will never be issued two times for a single sequence object unless it is defined as CYCLE or is manually restarted.

Metadata

For information about sequences, query sys.sequences.

Security

Permissions

Requires CREATE SEQUENCE, ALTER, or CONTROL permission on the SCHEMA.

Members of the db_owner and db_ddladmin fixed database roles can create, alter, and drop sequence objects.

Members of the db_owner and db_datawriter fixed database roles can update sequence objects by causing them to generate numbers.

The following example grants the user AdventureWorks\Larry permission to create sequences in the Test schema.

GRANT CREATE SEQUENCE ON SCHEMA::Test TO [AdventureWorks\Larry]

Ownership of a sequence object can be transferred by using the ALTER AUTHORIZATION statement.

If a sequence uses a user-defined data type, the creator of the sequence must have REFERENCES permission on the type.

Audit

To audit CREATE SEQUENCE, monitor the SCHEMA_OBJECT_CHANGE_GROUP.

Examples:

Creating a sequence that increases by 1

CREATE SEQUENCE Test.CountBy1  

    START WITH 1  

    INCREMENT BY 1 ;  

GO 

Creating a sequence that decreases by 1

CREATE SEQUENCE Test.CountByNeg1  

    START WITH 0  

    INCREMENT BY -1 ;  

GO  

Creating a sequence that increases by 5

CREATE SEQUENCE Test.CountBy1  

    START WITH 5  

    INCREMENT BY 5 ;  

GO  

Creating a sequence that starts with a designated number

CREATE SEQUENCE Test.ID_Seq  

    START WITH 24329  

    INCREMENT BY 1 ;  

GO  

Creating a sequence using default values

        CREATE SEQUENCE Test.TestSequence ; 

Execute the following statement to view the properties of the sequence.

        SELECT * FROM sys.sequences WHERE name = 'TestSequence';  

A partial list of the output demonstrates the default values.

start_value	-9223372036854775808
increment	1
mimimum_value	-9223372036854775808
maximum_value	9223372036854775807
is_cycling	0
is_cached	1
current_value	-9223372036854775808

Creating a sequence with a specific data type

CREATE SEQUENCE SmallSeq  

    AS smallint ;  

Creating a sequence using all arguments

CREATE SEQUENCE Test.DecSeq 

    AS decimal(3,0)  

    START WITH 125 

    INCREMENT BY 25  

    MINVALUE 100 

    MAXVALUE 200 

    CYCLE 

    CACHE 3 

; 

Execute the following statement to see the first value; the START WITH option of 125.

SELECT NEXT VALUE FOR Test.DecSeq; 

Execute the statement three more times to return 150, 175, and 200.

Execute the statement again to see how the start value cycles back to the MINVALUE option of 100.

Execute the following code to confirm the cache size and see the current value.

SELECT cache_size, current_value  

FROM sys.sequences 

WHERE name = 'DecSeq' ; 

Using Sequence in an Insert Statement

CREATE TABLE dbo.Employee(ID INT,Name VARCHAR(100))

GO

--Insert records into Employee table with Sequence object

INSERT INTO dbo.Employee VALUES

(NEXT VALUE FOR DBO.SequenceExample,'BASAVARAJ BIRADAR'),

GO

-- CHECK THE RECORDS INSERTED IN THE TABLE

SELECT * FROM dbo.Employee WITH(NOLOCK)

GO

Associate Sequence object to a table

CREATE TABLE dbo.Customer

(ID INT DEFAULT(NEXT VALUE FOR DBO.SequenceExample),

                               Name VARCHAR(100))

GO

INSERT INTO dbo.Customer(Name)

VALUES('PINKU BIRADAR'),

('MONTY BIRADAR')

GO

-- CHECK THE RECORDS INSERTED IN THE TABLE

SELECT * FROM dbo.Customer WITH(NOLOCK)

GO

Getting Next Sequence Value in A SELECT Statement

SELECT (NEXT VALUE FOR DBO.SequenceExample)

                            AS SequenceValue

GO 3

Getting Sequence Next Value in a variable

DECLARE @EmpID AS INT

        = NEXT VALUE FOR DBO.SequenceExample

SELECT @EmpID AS 'Employee Id'

Re-Setting the Sequence Number

ALTER SEQUENCE DBO.SequenceExample RESTART WITH 1 ; GO -- Verify whether sequence number is re-set SELECT (NEXT VALUE FOR DBO.SequenceExample)                            AS SequenceValue GO

 

How to get the current value of the Sequence

SELECT Current_Value

FROM SYS.Sequences

WHERE name='SequenceExample'

GO

 

Limitations:

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

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

SELECT (NEXT VALUE FOR DBO.SequenceExample)

UNION

SELECT (NEXT VALUE FOR DBO.SequenceExample)

GO

RESULT:

Msg 11721, Level 15, State 1, Line 1

NEXT VALUE FOR function cannot be used directly in a statement that uses a DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT operator.

 

2) In a statement using the ORDER BY clause unless NEXT VALUE FOR … OVER (ORDER BY …) is used.

SELECT NAME, (NEXT VALUE FOR DBO.SequenceExample) SeqValue

FROM dbo.Employee

ORDER BY NAME

GO

RESULT:

Msg 11723, Level 15, State 1, Line 1

NEXT VALUE FOR function cannot be used directly in a statement that contains an ORDER BY clause unless the OVER clause is specified.

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

SELECT TOP 10  (NEXT VALUE FOR DBO.SequenceExample)

GO

RESULT:

Msg 11739, Level 15, State 1, Line 1

NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, or the query contains TOP or OFFSET.

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

DECLARE @v INT SELECT ISNULL(@v,(NEXT VALUE FOR DBO.SequenceExample)) GO

RESULT:

Msg 11741, Level 15, State 1, Line 2
NEXT VALUE FOR function cannot be used within CASE, CHOOSE, COALESCE, IIF, ISNULL and NULLIF.

5) In the WHERE clause of a statement.

SELECT * FROM dbo.Employee WHERE ID = (NEXT VALUE FOR DBO.SequenceExample) GO

RESULT:

Msg 11720, Level 15, State 1, Line 3
NEXT VALUE FOR function is not allowed in the TOP, OVER, OUTPUT, ON, WHERE, GROUP BY, HAVING, or ORDER BY clauses.

Below table lists few more situations where NEXT VALUE FOR function cannot be used:

Sl. No.	Limitation/Restriction of using NEXT VALUE FOR function
1	In views, in user-defined functions, or in computed columns.
2	As an argument to a table-valued function.
3	As an argument to an aggregate function.
4	In the following clauses: FETCH, OVER, OUTPUT, ON, PIVOT, UNPIVOT, GROUP BY, HAVING, COMPUTE, COMPUTE BY, or FOR XML.
5	When a database is in read-only mode.
6	In subqueries including common table expressions and derived tables.
7	In a MERGE statement. (Except when the NEXT VALUE FOR function is used in a default constraint in the target table and default is used in the CREATE statement of the MERGE statement.)
8	In the definition of a check constraint.
9	In the definition of a rule or default object. (It can be used in a default constraint.)
10	As a default in a user-defined table type.
11	In a VALUES clause that is not part of an INSERT statement.

Below table lists out the list of error message that Sql Server may return while working with sequences:

We can use query like below to get this list:

SELECT message_id 'ERROR No.',text 'ERROR Description' FROM SYS.messages WHERE language_id = 1033   AND message_id between 11700 and 11742

 

RROR No.	ERROR Description
11700	The increment for sequence object ‘%.*ls’ cannot be zero.
11701	The absolute value of the increment for sequence object ‘%.*ls’ must be less than or equal to the difference between the minimum and maximum value of the sequence object.
11702	The sequence object ‘%.*ls’ must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, or any user-defined data type that is based on one of the above integer data types.
11703	The start value for sequence object ‘%.*ls’ must be between the minimum and maximum value of the sequence object.
11704	The current value ‘%.*ls’ for sequence object ‘%.*ls’ must be between the minimum and maximum value of the sequence object.
11705	The minimum value for sequence object ‘%.*ls’ must be less than its maximum value.
11706	The cache size for sequence object ‘%.*ls’ must be greater than 0.
11707	The cache size for sequence object ‘%.*ls’ has been set to NO CACHE.
11708	An invalid value was specified for argument ‘%.*ls’ for the given data type.
11709	The ‘RESTART WITH’ argument cannot be used in a CREATE SEQUENCE statement.
11710	Argument ‘START WITH’ cannot be used in an ALTER SEQUENCE statement.
11711	Argument ‘AS’ cannot be used in an ALTER SEQUENCE statement.
11712	Argument ‘%.*ls’ can not be specified more than once.
11714	Invalid sequence name ‘%.*ls’.
11715	No properties specified for ALTER SEQUENCE.
11716	NEXT VALUE FOR function does not support the PARTITION BY clause.
11717	NEXT VALUE FOR function does not support the OVER clause in default constraints, UPDATE statements, or MERGE statements.
11718	NEXT VALUE FOR function does not support an empty OVER clause.
11719	NEXT VALUE FOR function is not allowed in check constraints, default objects, computed columns, views, user-defined functions, user-defined aggregates, user-defined table types, sub-queries, common table expressions, or derived tables.
11720	NEXT VALUE FOR function is not allowed in the TOP, OVER, OUTPUT, ON, WHERE, GROUP BY, HAVING, or ORDER BY clauses.
11721	NEXT VALUE FOR function cannot be used directly in a statement that uses a DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT operator.
11722	NEXT VALUE FOR function is not allowed in the WHEN MATCHED clause, the WHEN NOT MATCHED clause, or the WHEN NOT MATCHED BY SOURCE clause of a merge statement.
11723	NEXT VALUE FOR function cannot be used directly in a statement that contains an ORDER BY clause unless the OVER clause is specified.
11724	An expression that contains a NEXT VALUE FOR function cannot be passed as an argument to a table-valued function.
11725	An expression that contains a NEXT VALUE FOR function cannot be passed as an argument to an aggregate.
11726	Object ‘%.*ls’ is not a sequence object.
11727	NEXT VALUE FOR functions for a given sequence object must have exactly the same OVER clause definition.
11728	The sequence object ‘%.*ls’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.
11729	The sequence object ‘%.*ls’ cache size is greater than the number of available values; the cache size has been automatically set to accommodate the remaining sequence values.
11730	Database name cannot be specified for the sequence object in default constraints.
11731	A column that uses a sequence object in the default constraint must be present in the target columns list, if the same sequence object appears in a row constructor.
11732	The requested range for sequence object ‘%.*ls’ exceeds the maximum or minimum limit. Retry with a smaller range.
11733	Parameter ‘%.*ls’ must be a positive integer.
11734	NEXT VALUE FOR function is not allowed in the SELECT clause when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement.
11735	The target table of the INSERT statement cannot have DEFAULT constraints using the NEXT VALUE FOR function when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement.
11736	Only one instance of NEXT VALUE FOR function per sequence object is allowed in SET or SELECT with variable assignment.
11737	NEXT VALUE FOR function does not support the ROWS and RANGE clauses.
11738	The use of NEXT VALUE FOR function is not allowed in this context.
11739	NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, or the query contains TOP or OFFSET.
11740	NEXT VALUE FOR function cannot be used in a default constraint if ROWCOUNT option has been set, or the query contains TOP or OFFSET.
11741	NEXT VALUE FOR function cannot be used within CASE, CHOOSE, COALESCE, IIF, ISNULL and NULLIF.
11742	NEXT VALUE FOR function can only be used with MERGE if it is defined within a default constraint on the target table for insert actions.

 

NEXT VALUE FOR:

Applies To: SQL Server 2014, SQL Server 2016 Preview

Generates a sequence number from the specified sequence object.

Syntax:

NEXT VALUE FOR [ database_name . ] [ schema_name . ]  sequence_name

   [ OVER (<over_order_by_clause>) ]

Arguments:

database_name

                    The name of the database that contains the sequence object.

schema_name

                    The name of the schema that contains the sequence object.

sequence_name

                    The name of the sequence object that generates the number.

over_order_by_clause

Determines the order in which the sequence value is assigned to the rows in a partition. For more information, see OVER Clause (Transact-SQL).

 

ALTER SEQUENCE:

Modifies the arguments of an existing sequence object. If the sequence was created with the CACHE option, altering the sequence will recreate the cache.

The data type cannot be changed by using the ALTER SEQUENCE statement. To change the data type, drop and create the sequence object.

ALTER SEQUENCE [schema_name. ] sequence_name

    [ RESTART [ WITH <constant> ] ]

    [ INCREMENT BY <constant> ]

    [ { MINVALUE <constant> } | { NO MINVALUE } ]

    [ { MAXVALUE <constant> } | { NO MAXVALUE } ]

    [ CYCLE | { NO CYCLE } ]

    [ { CACHE [ <constant> ] } | { NO CACHE } ]

    [ ; ]

RESTART [WITH <constant>]

The next value that will be returned by the sequence object. If provided, the RESTART WITH value must be an integer that is less than or equal to the maximum and greater than or equal to the minimum value of the sequence object. If the WITH value is omitted, the sequence numbering restarts based on the original CREATE SEQUENCE options.

 

The MINVALUE for ascending sequences and the MAXVALUE for descending sequences cannot be altered to a value that does not permit the START WITH value of the sequence. To change the MINVALUE of an ascending sequence to a number larger than the START WITH value or to change the MAXVALUE of a descending sequence to a number smaller than the START WITH value, include the RESTART WITH argument to restart the sequence at a desired point that falls within the minimum and maximum range.

DROP SEQUENCE

Removes a sequence object from the current database.

DROP SEQUENCE 

[ IF EXISTS ] { [ database_name . [ schema_name ] . | schema_name. ]    

sequence_name } [ ,...n ]

[ ; ]

Remarks

After generating a number, a sequence object has no continuing relationship to the number it generated, so the sequence object can be dropped, even though the number generated is still in use.

A sequence object can be dropped while it is referenced by a stored procedure, or trigger, because it is not schema bound. A sequence object cannot be dropped if it is referenced as a default value in a table. The error message will list the object referencing the sequence.

To list all sequence objects in the database, execute the following statement.

SELECT sch.name + '.' + seq.name AS [Sequence schema and name] 

    FROM sys.sequences AS seq

    JOIN sys.schemas AS sch

        ON seq.schema_id = sch.schema_id ;

GO

Security

Permissions

                    Requires ALTER or CONTROL permission on the schema.

Audit

                    To audit DROP SEQUENCE, monitor the SCHEMA_OBJECT_CHANGE_GROUP.

SEQUENCE	IDENTITY
Sequence object is introduced in Sql Server 2012	Identity Column property is introduced in Sql Server 6.0
Sequence is a user-defined database object and as name suggests it generates sequence of numeric values according to the properties with which it is created	Identity property is a table column property. It is also used to generate a sequence of numbers according to the properties with which it is created
Sequence object can be shared across multiple tables	Identity property is tied to a Table
Sequence object can be used to generate database-wide sequential number across multiple tables.	Identity property can be used to generate a sequence numbers at a table level
A sequence is created independently of the tables by using the CREATE SEQUENCE statement	Identity property can be specified for a table column in CREATE TABLE or ALTER TABLE statement
Syntax: 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 } ] [ ; ]	Syntax: IDENTITY [ (seed , increment) ]
Below is simple example of creating a sequence with Initial value 1 and Increment by 1 CREATE SEQUENCE   [DBO].[SequenceExample] AS INT  START WITH 1  INCREMENT BY 1 GO	Below is an example of creating a customer table with identity column Id with initial value as 1 and increment by 1 CREATE TABLE dbo.Customer ( Id INT IDENTITY(1,1),   Name  NVARCHAR(50) ) GO
We can get the next sequence value by using NEXT VALUE FOR function without needing to insert a record to the table Example: Getting Next Sequence Value in A SELECT Statement without inserting a record SELECT (NEXT VALUE FOR DBO.SequenceExample) AS seqValue	Only way to generate the next identity value is by inserting a record to the table in which the identity column is defined. Example: Insert a record to get the next identity value INSERT INTO dbo.Customer (Name) VALUES('Basavaraj Biradar') GO SELECT SCOPE_IDENTITY() GO SELECT * FROM dbo.Customer
We can use a script like below to get the sequence object’s current value: SELECT Current_Value FROM Sys.Sequences WHERE name='SequenceExample'	We can use a script like below to get the identity columns current value (i.e. last identity value generated as a result of insert): SELECT IDENT_CURRENT('Customer')  AS 'Identity Current value'
Sequence object provides an option to reset the current sequence value as-well as the increment step size ALTER SEQUENCE  dbo.SequenceExample RESTART WITH 20 INCREMENT BY 2 GO	Column identity property current value can be reseeded but not it’s increment step size Example: The following script resets the Customer tables current identity value to 20. DBCC CHECKIDENT('Customer', RESEED,20)
Sequence object provides an option to define the maximum sequence value. If it is not specified, by default it takes the maximum value of the Sequence object data type. Example: Below script creates a sequence object with maximum value as 2 CREATE SEQUENCE   [dbo].[SequenceMax] AS INT  START WITH 1  INCREMENT BY 1  MAXVALUE 2 GO Once the Sequence maximum value is reached the request for the next sequence value results in the following error message: Msg 11728, Level 16, State 1, Line 2 The sequence object ‘SequenceMax’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.	Identity column property doesn’t provide an option to define the maximum identity value, it always to takes maximum value corresponding to the identity columns data type
Sequence object provides an option of automatic restarting of the Sequence values. If during Sequence object creation the CYCLE option is specified, then once the sequence object reaches maximum/minimum value it will restarts from the specified minimum/maximum value. Example: Create  a sequence object with CYCLE option which starts with 1 and when Sequence max value 2 is reached it will restart with minimum value 1. CREATE SEQUENCE [dbo].[SeqCycle]  AS INT START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2 CYCLE GO Let us check how the Sequence values are recycled by calling the NEXT VALUE FOR function for the 3 times as below: SELECT (NEXT VALUE FOR   dbo.SeqCycle) AS SeqValue GO 3	Identity property doesn’t provides an option for the automatic restarting of the identity values
Sequence object provides sp_sequence_get_range to get multiple sequence numbers at once.	Identity column property doesn’t provide an option to get multiple values.
Sequence object provides an option to enable caching, which greatly increases the performance by reducing the disk IO required to generate the next sequence value. CACHE property allows to define cache size, by default caching is disabled. To Sequence CACHE management and internals you may like to go through the article: Sequence Cache management and Internals	Identity property doesn’t provide an option to enable/diable the cache management and also to define the cache size.

sp_sequence_get_range