SQL Sequence Vs Identity Column

Let’s take a look at what a Sequence is with 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 noticed 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 has only existed since SQL Server 2012 but has long existed in Oracle (where there are 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.

CREATESEQUENCEMySEQUENCESTARTWITH1INCREMENTBY1;GO

After creation, you can look at the properties in the GUI. Note you can set the increment by restarting 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.

CREATETABLEMyIndentity(NameIDintIDENTITY(1,1),FirstNamevarchar(25),MiddleIntchar(1),LastNamevarchar(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.

INSERTMyIndentity(FirstName,MiddleInt,LastName)VALUES('Joe','K','Smith');INSERTMyIndentity(FirstName,MiddleInt,LastName)VALUES('Jane','L','Doe');SELECT*FROMMyIdentity

Comparing the two

IDENTITY_CACHE = {ON| OFF }