Sequence vs identity

SQL Server 2012 introduced Sequence as a new feature, same as in Oracle and Postgres. Where sequences are preferred over identities? And why do we need sequences?

I think you will find your answer here > > Using the identity attribute for a column, you can easily generate > auto-incrementing numbers (which as often used as a primary key). With > Sequence, it will be a different object which you can attach to a > table column while inserting. Unlike identity, the next number for the > column value will be retrieved from memory rather than from the disk – > this makes Sequence significantly faster than Identity. We will see > this in coming examples.

And here:

> Sequences: Sequences have been requested by the SQL Server community > for years, and it's included in this release. Sequence is a user > defined object that generates a sequence of a number. Here is an > example using Sequence.

and here as well:

> A SQL Server sequence object generates sequence of numbers just like > an identity column in sql tables. But the advantage of sequence > numbers is the sequence number object is not limited with single sql > table.

and on msdn you can also read more about usage and why we need it (here):

> 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 is created independently of the tables by using the CREATE > SEQUENCE statement. Options enable you to control the increment, > maximum and minimum values, starting point, automatic restarting > capability, and caching to improve performance. For information about > the options, see CREATE SEQUENCE. > > Unlike identity column values, which are generated when rows are > inserted, an application can obtain the next sequence number before > inserting the row by calling the NEXT VALUE FOR function. The sequence > number is allocated when NEXT VALUE FOR is called even if the number > is never inserted into a table. The NEXT VALUE FOR function can be > used as the default value for a column in a table definition. Use > sp_sequence_get_range to get a range of multiple sequence numbers at > once. > > A sequence can be defined as any integer data type. If the data type > is not specified, a sequence defaults to bigint.

Sequence and identity both used to generate auto number but the major difference is Identity is a table dependant and Sequence is independent from table.

If you have a scenario where you need to maintain an auto number globally (in multiple tables), also you need to restart your interval after particular number and you need to cache it also for performance, here is the place where we need sequence and not identity.

Although sequences provide more flexibility than identity columns, I didn't find they had any performance benefits.

I found performance using identity was consistently 3x faster than using sequence for batch inserts.

I inserted approx 1.5M rows and performance was:

• 14 seconds for identity
• 45 seconds for sequence

I inserted the rows into a table which used sequence object via a table default:

NEXT VALUE for <seq> for <col_name>

and also tried specifying sequence value in select statement:

SELECT NEXT VALUE for <seq>, <other columns> from <table>

Both were the same factor slower than the identity method. I used the default cache option for the sequence.

The article referenced in Arion's first link shows performance for row-by-row insert and difference between identity and sequence was 16.6 seconds to 14.3 seconds for 10,000 inserts.

The Caching option has a big impact on performance, but identity is faster for higher volumes (+1M rows)

See this link for an indepth analysis as per utly4life's comment.

I know this is a little old, but wanted to add an observation that bit me.

I switched from identity to sequence to have my indexes in order. I later found out that sequence doesn't transfer with replication. I started getting key violations after I setup replication between two databases since the sequences were not in sync. just something to watch out for before you make a decision.

I find the best use of Sequences is not to replace an identity column but to create a "Order Number" type of field.

In other words, an Order Number is exposed to the end user and may have business rules along with it. You want it to be unique, but just using an Identity Column isn't really correct either.

For example, different order types might require a different sequence, so you might have a sequence for Internet Order, as opposed to In-house orders.

In other words, don't think of a Sequence as simple a replacement for identity, think of it as being useful in cases where an identity does not fit the business requirements.

Recently was bit by something to consider for identity vs sequence. Seems MSFT now suggests sequence if you may want to keep identity without gaps. We had an issue where there were huge gaps in the identity, but based on this statement highlighted would explain our issue that SQL cached the identity and after reboot we lost those numbers.

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-2017

Consecutive values after server restart or other failures – SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use its own mechanism to generate key values. Using a sequence generator with the NOCACHE option can limit the gaps to transactions that are never committed.