Tanubhrt.This.Side

SEQUENCE is used to generate automatically incrementing number but unlike IDENTITY it is not associated with any tables. The sequence of numeric values is generated in ascending or descending order at a defined interval and can be configured to restart cycle when exhausted.

Like IDENTITY, sequence can be generated using INT data types. The data types allowed in SEQUENCE are –

•         Tinyint
•          Smallint
•          Int
•          Bigint
•          Decimal and numeric with a scale of 0

• Any user defined data type that is based on allowed types

If no data type is provided, the default data type is BIGINT.

Unlike IDENTITY, where we need to insert the next value to find it out, in SEQUENCE an application can find out the next SEQUENCE number by calling the NEXT VALUE FOR function.

We can use sp_sequence_get_range to find out the range of the sequence number, which is not possible using IDENTITY.

UNDERSTANDING SEQUENCE

#Creation of SEQUENCE

CREATE SEQUENCE MySequence

START WITH 1,

INCREMENT BY 1;

The above query will create a sequence which will start with 1 and the value is incremented by 1. Type of the sequence name is sysname.

SELECT NEXT VALUE FOR MySequence AS ‘THE NEXT’

Very simple concept à Using SEQUENCE, you are creating a sequence and defining the starting and the value by which the initial value will get incremented. Now, when you are running this command over and over again, the next value for the SEQUENCE will get printed.

• We can even alter the SEQUENCE that we have once created, but the point is that when doing so, we need to make sure that we are not using start with. Since with alter, you cannot use START WITH. However, you can alter the range or the step by which the value is getting incremented

ALTER SEQUENCE MySequence

INCREMENT BY 5;

• Now, we can even create a SEQUENCE that is used for decrementing values. For that, there is no special function like decrement, however you can use INCREMENT with negative values to decrement the counter.

CREATE SEQUENCE MySeqDecrement

START WITH 934

INCREMENT BY -3;

• We can even create a SEQUENCE using a specific data type as  –

CREATE SEQUENCE MySeqDataType

AS SMALL INT;

This creates a sequence which has values with range of small int.

CONCEPT OF CACHE IN SEQUENCE

To improve performance, SQL server pre-allocates the number of sequence numbers specified by the CACHE argument.

For example, a new sequence is created with a starting value of 1 and a cache size of 15. When the first value is needed, values 1 through 15 are made available from memory. The last cached value is written to the system tables on the disk. When all 15 numbers are used, the next request will cause the cache to be allocated again. The new last cached value will be written to the system table.

If the Database engine is stopped after you use 22 numbers, the next intended sequence number in memory is written to the system tables, replacing the previously stored number.

After SQL server restarts and a sequence number is needed, the starting number is read from the system tables (23). The cache amount of 15 numbers (23-38) is allocated to memory and the next non-cache number (39) is written to the system tables.

If the database engine stops abnormally for an event such as a power failure, the sequence number restarts with the number read from the system table (39). 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 objects unless it is defined as CYCLE or manually restarted.

The cache is maintained in memory by tracking the current value (the last value issued) and the number of values left in the cache. Therefore, the amount of memory used by the cache is always two instances of the data type of the sequence object.

NO CACHE

Setting the cache argument to NO CACHE writes the sequence value to the system tables every time that a sequence is used. This might slow performance by increasing disk access, but reduces the chance of unintended gaps. Gaps can still occur if numbers are requested using the NEXT VALUE FOR or sp_sequence_get_range functions, but then the numbers are either not used or are used in uncommitted transactions.

When a sequence object uses the CACHE option, if you restart the sequence object, or alter the INCREMENT, CYCLE, MINVALUE, MAXVALUE, or the cache size properties, it will cause the cache to be written to the system tables before the change occurs. Then the cache is reloaded starting with the current value (i.e. no numbers skipped). Changing the cache size takes effect immediately.

CACHE OPTION WHEN THE CACHED VALUES ARE AVAILABLE

The following processes occur every time that a sequence object is requested to generate the next value for the CACHE option if there are unused values available in the memory cache for the sequence object.

•       The next value for the sequence object is calculated.
•       The new current value for the sequence object is updated in memory.
•       The calculated value is returned to the calling function

CACHE option when the CACHE is exhausted

The following process occurs every time a sequence object is requested to generate the next value for the CACHE option if the cache has been exhausted-

•        The next value for the cache is generated
•       The last value for the cache is calculated
•       The system table row for the sequence object is locked and the value calculated in step 2 is written to the system table. A cache-exhausted event is fired to notify the user of the new persisted value.

NO CACHE OPTION

The following process occurs every time that a sequence object is requested to generate the next value for the NO CACHE option.

•        The next value for the sequence object is calculated.
•      The new current value for the sequence object is written to the system table.
•       The calculated value is written to the system table.

Source -: https://msdn.microsoft.com/en-us/library/ms123401.aspx

Happy Learning 🙂