Denali – Day 10: Sequence object
Sequence is an object introduced in Denali, this is similar to ORACLE sequence, earlier sql server was having identity column which is associated with particular table but now sequence is a separate object which has sequence values and can be ascending or descending order with specified interval.
Bigint is a default data type for sequence but it can be used any numeric data type like tinyint, smallint, int, bigint, decimal and numeric.
Sequence is an external object /entity which can be used for multiple tables and manage efficiently per requirement. It can be used as an identity column as well.
Following are syntax for working on sequence.
Create Sequence:
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 } ]
[ ; ]
Alter Sequence:
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 } ]
[ ; ]
You can alter the sequence parameters like start with and other.
Drop Sequence:
DROP SEQUENCE { [ database_name . [ schema_name ] . | schema_name. ] sequence_name } [ ,...n ]
[ ; ]
Cycle : we can cycle /repeat the sequence
CACHE/NO CACHE:
We can specify the size of the cache(memory) you want to keep the sequence in memory size, specified this size that size of sequence will be in memory for fast access, and once it reaches to that size sequence will stored in system table and next size would be picked. That way sequence is very fast.
If specifying NO CACHE : would be little slow comparatively as it has to do more IO. But less possibility of sequence loss.
It is very easy and can be very dynamic to use. You can play with sequence
Sys.sequences: table
Column Name:
name
object_id
principal_id
schema_id
parent_object_id
type
type_desc
create_date
modify_date
is_ms_shipped
is_published
is_schema_published
start_value
increment
maximum_value
is_cycling
is_cached
cache_size
system_type_id
user_type_id
precision
scale
current_value
is_exhausted
You can get all the existing sequence created by user is stored in system table Sys.sequences,
Next Value For () function:
NEXT VALUE FOR [ database_name . ] [ schema_name . ] sequence_name
[ OVER (<over_order_by_clause>) ]
This function will provide you expected next value for the given sequence
Eg. Select next value for <Sequence Name>
We can stored the value of sequence in a variable and use it whenever required.
Sys.Sp_sequence_get_range:
sp_sequence_get_range [ @sequence_name = ] N’<sequence>’
, [ @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 ]
[ ; ]
Is used to return a range of sequence value from sequence. And work on that values.
Limitation:
Sequence can be very fast as we can store this on CACHE, but it has its own limitations to it when you store the sequence in cache if an system unexpected shutdown then possibility that sequence may miss the sequence values.
Whenever you use Next Value For () function Sys.Sp_sequence_get_range the sequence current_value will change and improper use of these would cause sequence gap.
It cannot be unique by its own, as it can be “Cycle” could have repetitive sequence number.
It is not guaranteed to provide sequence without sequence loss(less for nocach)
Use “Trigger” to control/constraints the sequence.
