100

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?

Sleiman Jneidi
  • 22,907
  • 14
  • 56
  • 77
  • After using both, I prefer Identity for global usage in a database. That is, where you need an auto-incrementing number like ObjectID and want to use it across many tables. It gets complicated making a sequence and then using an application (website or app etc) to manage inserting and updating tables based on a sequence number. – volume one Feb 13 '19 at 09:40

6 Answers6

88

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.

Arion
  • 31,011
  • 10
  • 70
  • 88
25

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.

Jean-François Fabre
  • 137,073
  • 23
  • 153
  • 219
user1059637
  • 702
  • 6
  • 5
17

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.

Stagg
  • 2,660
  • 5
  • 34
  • 32
  • What was the cache size of the sequence. – Shannon Severance Oct 23 '14 at 20:31
  • 50, increasing it does make a difference, but I recall identity still was faster. – Stagg Oct 24 '14 at 15:21
  • 3
    http://byobi.com/blog/2012/09/sequence-vs-identity-performance-comparison/ Provides a good detailed comparison of various configurations. Shows that increase in cache size from 50 to 500 yielded approximately a 2x speed difference. – ulty4life Jul 08 '15 at 22:56
  • 1
    Are you suggesting sequences are slower than identity column? I had an opposite impression as sequences are in-memory unlike identity which is fetched from disk. Your findings are quite surprising. Glad that you shared. – RBT Aug 12 '16 at 02:09
  • yes in most cases. See link in comments above, also updated answer with clarification. – Stagg Aug 12 '16 at 08:10
  • My local SQL 2012 populates 1,000,000 records one by one into a #temp table with a single `INT` column in 23 sec with a sequence default, and in 19 sec with identity. It then populates all 1,000,000 into another on-disk table with 2 `INT` columns in 15 sec with a sequence and in 3 seconds with `identity`. Using 1000 cache on the sequence. – ajeh Mar 07 '17 at 19:13
  • @ajeh ... did you have other activity going on while you were running your test? i have to imagine the `identity` option causes some table/page locking, whereas a `sequence` would not. – Matt Felzani Apr 09 '18 at 15:47
  • 1
    with sequence you can optimize batch insert performance by using `alter sequence increment by ...` to simply make room for your new rows and then use base + row_number() or whatever for the actual values. – gordy Sep 12 '18 at 02:14
9

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.

Ken
  • 91
  • 1
  • 1
4

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.

Greg Gum
  • 33,478
  • 39
  • 162
  • 233
1

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://learn.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.

awilbourn
  • 91
  • 1
  • 6
  • 2
    There is a pretty good answer to why you are skipping `IDENTITY` numbers [link](http://sqlblog.com/blogs/kalen_delaney/archive/2014/06/17/lost-identity.aspx) and `SEQUENCE` has the same issue as described here [link](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-2017) but you can limit it by setting a smaller CACHE size but there then there is a trade off with speed. – Mrphin Dec 27 '18 at 16:08
  • The new Sequence feature introduced the bug of gaps in identity columns. You can fix it by disabling identity from using sequences internally by calling `ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF` – Ian Boyd Mar 28 '23 at 14:52