Coder Perfect

Sequence vs identity


Sequence is a new feature in SQL Server 2012, much like it is in Oracle and Postgres. Where do sequences take precedence over individual identities? And why do we need sequences in the first place?

Asked by Sleiman Jneidi

Solution #1

I believe you’ll find the answer here.

And here:

as well as here:

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

Answered by Arion

Solution #2

Both Sequence and Identity are used to produce auto numbers, but the main difference is that Identity is table dependent, whereas Sequence is not.

If you have a case where you need to keep an auto number worldwide (in various tables), as well as resume your interval after a specific number and cache it for performance, this is when sequence, not identity, comes into play.

Answered by user1059637

Solution #3

Despite the fact that sequences offer more flexibility than identity columns, I saw no performance advantages.

For batch insertion, I observed that utilizing identity was consistently 3x faster than using sequence.

I inserted approx 1.5M rows and performance was:

I used a table default to insert the rows into a table that used a sequence object:

NEXT VALUE for col name>’seq>’seq>’seq>’seq>’seq>’seq>’seq >’s

Also, in the select statement, I tried specifying the sequence value:

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

Both methods took the same amount of time as the identical technique. For the sequence, I utilized the default cache setting.

The performance for row-by-row inserts with a mismatch between identity and sequence was 16.6 seconds to 14.3 seconds for 10,000 inserts, according to the article linked in Arion’s first link.

Although the Caching option has a significant impact on performance, identification is faster for larger quantities. (+1 million rows)

According to utly4life’s comment, this link contains an in-depth analysis.

Answered by Stagg

Solution #4

I realize this is an older post, but I wanted to make an insight that struck me.

To get my indexes in order, I switched from identity to sequence. Later on, I discovered that sequence does not transmit during replication. Because the sequences were out of sync, I started obtaining key violations after setting up replication across two databases. Just something to keep in mind before making a decision.

Answered by Ken

Solution #5

The ideal usage of Sequences, in my opinion, is to establish a “Order Number” sort of field rather than replacing an identification column.

In other words, an Order Number is visible to the end user and may be associated with business rules. You want it to be unique, but utilizing an Identity Column alone isn’t the best solution.

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.

To put it another way, don’t think of a Sequence as a simple identity replacement; rather, consider of it as a tool for when an identity doesn’t meet the needs of the organization.

Answered by Greg Gum

Post is based on