SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Sequence vs Identity Column

Let’s take a look at what a Sequence is in relation to an Identity Column in SQL Server. Did you know Sequence even existed? I didn’t until I was asked about them. It’s amazing how much you can skip over and never notice in SSMS. See this little folder, ever notice it under Programmability in Management Studio. Yep it’s there, SQL Server has this very handy thing called Sequences. Sequences are a relatively new feature that have only existed since SQL Server 2012, but have long existed in Oracle (where there a no identity columns).

What is a Sequence?

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 can be configured to restart (cycle) when exhausted. It’s important to note the sequences can be cached and are not guaranteed to be in sequential order.

The Code

CREATE SEQUENCE MySEQUENCE
    START WITH 1 
    INCREMENT BY 1 ; 
GO

After creation, you can look at the properties in the GUI. Note you can set the Increment by, you can restart the sequence and even set min and max values.

How to Query

To see Sequence properties
SELECT *FROM sys.sequences
WHERE name='MySequence'

To Get Next Value
SELECT NEXT VALUE FOR MySequence;

What’s an Identity Column?

A property of a table that is set by initial seed value (starting value). For each insert it assigns a new incremental value that is added to the identity value of the previous row that was loaded.

The Code

CREATE TABLE MyIndentity
( 

 NameID int IDENTITY(1,1), 
 FirstName varchar (25), 
 MiddleInt char(1), 
 LastName varchar(40) 
);

Note: after the field type IDENTITY, you declare the SEED (1), then INCREMENT Value (1). You can see this in the GUI below for the Column properties.

How to Query

Let’s insert two records and see the NameID Identity column increment.

INSERT MyIndentity 
(FirstName, MiddleInt, LastName) 
VALUES 
('Joe', 'K', 'Smith');

INSERT MyIndentity 
(FirstName, MiddleInt, LastName) 
VALUES 
('Jane', 'L', 'Doe');

SELECT * FROM MyIdentity

Comparing the two

Attribute Sequence Identity
Object Level Database Table
Limit Can set a limit Limited by data type INT vs BIG INT
Values Generated by application call using NEXT VALUE FOR Generated on INSERT on a table
Increments Declared as INCREMENT at setup and can be anything. Can be a negative number to cause the sequence to descend instead of ascending numbers Declared as INCREMENT at setup and can be any positive number, numbers will ascend
Scope Generated outside the scope of a transaction Generated within a Transaction
Number Assignment Sequences can be preallocated (example assign me number 1-25) Cannot be preallocated, assigned in order by INSERT
Gaps Can experience Gaps Can experience Gaps
Uniqueness No, this number can be reset and reused. Often used as Primary Key (you must choose this property to ensure the unique value).

Summary

So, this was just a quick look what a Sequence is compared to an Identity column. Both can be very useful. If you’re looking for a unique value your best bet it to go with an Identity Column and the Primary Key option. If you want just an auto-generated value to be able to use in an application outside of a table a Sequence is a sure bet. Play around with it, I am sure you can come up with a million and one uses for each.

***UPDATE NEW to SQL 2017 ***

Per MSDN there is a new option

IDENTITY_CACHE = { ON | OFF }

Applies to: SQL Server 2017 and Azure SQL Database (feature is in public preview)

Enables or disables identity cache at the database level. The default is ON. Identity caching is used to improve INSERT performance on tables with Identity columns. To avoid gaps in the values of the Identity column in cases where the server restarts unexpectedly or fails over to a secondary server, disable the IDENTITY_CACHE option. This option is similar to the existing SQL Server Trace Flag 272, except that it can be set at the database level rather than only at the server level.

SQLEspresso

I am Monica Rathbun. I’m currently a Sr. Database Administrator/BI Architect at Massimo Zanetti Beverage, USA in Virginia. I’ve been a Lone DBA for 15 years, working with all aspects of SQL Server and Oracle. I am currently the co-leader for the Hampton Roads SQL Server User Group and can be found on Twitter daily as @SQLEspresso. I am passionate about SQL Server and the #SQLFamily, doing anything I can to give back to such a wonderful community. As a new speaker at SQL Saturdays and a new blogger, I hope to earn my place as a valued member. When I’m not busy with work, you will find me playing taxi to my two daughters back and forth to dance classes.

Comments

Leave a comment on the original post [sqlespresso.com, opens in a new window]

Loading comments...