Denali — Day 10: Sequence object

2012/05/10

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.

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)