#SQLNewBlogger Week 2 – Teach Something New

Introduction to sequence objects

This is the 2nd of 4 posts for the #SQLNewBlogger challenge by Ed Leighton-Dick (@eleightondick / http://www.edleightondick.com)
My first post for this challenge can be found at Eye of the tiger.

In my first post, I stated one of the reasons I wanted to start a blog was to participate in #TSQL2sday. This month Mike Donnelly (@SQLMD / https://sqlmd.wordpress.com) is hosting #TSQL2sday #65. Mike intentionally tied in with the #SQLNewBlogger challenge. The subject is simple; learn something new and then write a blog post explaining it.

In SQL 2012 the Sequence object was introduced to enhance some of the limitations of the IDENTITY column property. The IDENTITY column property applies to a column in a table and is generally used as a surrogate key in the table. Some characteristics of the IDENTITY property:
* Must be a integer or numeric/decimal with scale of 0.
* Optional seed and increment (beginning value and increment), with default values of 1,1.
* Only 1 column per table is allowed this property
* Value is automatically generated and assigned when row is created in the table
* Value is NOT guaranteed to be unique (however, you can add the PRIMARY KEY or UNIQUE constraints to the column)
* You may override the auto generated value
* When running DELETE from the table, the value of the IDENTITY is NOT affected
* When running TRUNCATE on the table, the value of the IDENTITY is reset to the initial seed value
* Reseed with DBCC CHECKIDENT(Table_Name, RESEED, new_reseed_value) to reset the current value
* IDENTITY does NOT support cycling (the next insertion after you hit the max value for the data type will result in an error and you will need to reseed before an insertion will work)
* IDENTITY column can have gaps (numbers may be missing)

The sequence object is an independent database object . It is NOT tied to a specific column. The value generated may be used in any table in the database. This allows for unique keys across multiple tables. Sequence objects are:
* Independent database objects
* User defined
* Schema Bound
* Generally more flexible than IDENTITY as it isn’t tied to a specific column
* Sequence object can have gaps
* Counter can cycle (once it reaches the max value, it starts over with the min value)
* Note: cycle if min value is NOT defined it will start over with negative numbers (smallest number for the data type and increment)
* Default data type is bigint
* Once created, you can’t change the data type
* Once created, you may change any other property
* The next X values are held in memory (defined with CACHE X option)
* 2 values are held in memory 1) Current sequence value 2) how many values are left
* Caching option controls how often the current sequence value written to disk
* NO CACHE option means write to disk for each request
* Performance should benefit from holding values using memory, however, you should test
* Risk is losing a range up to the size of the cache value in the event of error or shutdown

sys.sequences is a view to query the properties defined in the current database.

-- To create a sequence:
CREATE SEQUENCE dbo.MyFirstSequence
    START WITH 1
    INCREMENT BY 1;
GO

-- To drop a sequence;
DROP SEQUENCE dbo.MyFirstSequence;
GO

-- To see sequences in the current database:
SELECT * FROM sys.sequences;

While this post is an introduction, I would have liked to put some additional time into learning more about this and post some additional code to show more options and examples. But I now have my first #TSQL2sday post submitted!