Understanding Sequences and their use – SQL Server 2012

As per MSDN ,

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 may cycle (repeat) as requested. Sequences, unlike identity columns, are not associated with tables. An application refers to a sequence object to receive 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 keys across multiple rows and tables.

A sequence can be created using the CREATE SEQUENCE  statement.

CREATE SEQUENCE [schema_name . ] sequence_name
    [ AS [ built_in_integer_type | user-defined_integer_type ] ]
    [ START WITH <constant> ]
    [ INCREMENT BY <constant> ]
    [ { MINVALUE [  ] } | { NO MINVALUE } ]
    [ { MAXVALUE [  ] } | { NO MAXVALUE } ]
    [ CYCLE | { NO CYCLE } ]
    [ { CACHE [ <constant> ] } | { NO CACHE } ]
    [ ; ]

An example could be found on these statements here in an older post.

When to use sequences :

  • The application requires a number before the insert into the table is made.
  • The application requires sharing a single series of numbers between multiple tables or multiple columns within a table.
  • The application must restart the number series when a specified number is reached. For example, after assigning values 1 through 10, the application starts assigning values 1 through 10 again.
  • The application requires sequence values to be sorted by another field. The NEXT VALUE FOR function can apply the OVER clause to the function call. The OVER clause guarantees that the values returned are generated in the order of the OVER clause’s ORDER BY clause.
  • An application requires multiple numbers to be assigned at the same time. For example, an application needs to reserve five sequential numbers. Requesting identity values could result in gaps in the series if other processes were simultaneously issued numbers. Calling sp_sequence_get_range can retrieve several numbers in the sequence at once.
  • You need to change the specification of the sequence, such as the increment value.
Advertisements

About mytechlifedays

Its been a wonderful learning period over the last 6 years in the IT industry , getting exposed to whole lot of technologies and ideas. The hurdles and the crisis that came along have been wonderful experience ... And now its time to pen them down so that let some others execute faster and easily with these information .....
This entry was posted in Database and tagged , . Bookmark the permalink.

Leave a Reply ! It would be always appreciated ! :)

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s