Interview Questions and Answers
Difference between Sequence Object and Identity Column
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:
Where:
Start with: Initial value to start with sequence. Increment by: Step by which the values will get incremented or decremented. Minvalue: Minimum value of the sequence. Maxvalue: Maximum value of the sequence. Cycle / No Cycle: To recycle the sequence once it reaches to the maximum or minimum (if increment by is a negative number). Cache / No Cache: To pre-allocate the number of sequences specified by the given value. | Syntax:
Where:
Seed: Initial value to start with Increment: Step by which the values will get incremented or decremented each time. | ||
Below is simple example of creating a sequence with Initial value 1 and Increment by 1
| Below is an example of creating a customer table with identity column Id with initial value as 1 and increment by 1
| ||
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
| 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
| ||
We can use a script like below to get the sequence object’s current value:
| 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):
| ||
Sequence object provides an option to reset the current sequence value as-well as the increment step size
| 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.
| ||
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
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.
Let us check how the Sequence values are recycled by calling the NEXT VALUE FOR function for the 3 times as below:
| 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. |
Post a Comment
0 Comments