Majority of the most popular databases have some mechanisms to generate numbers that database developers use as values in primary key columns. Oracle has sequences, MySQL has autoincrement, SQL Server has used IDENTITY property for many years. Before SQL Server 2012 there was no other option. Obviously, a developer could implement his/her own mechanism but that does not count.

SQL Server 2012 was the first release that provided another built-in solution â€“ a sequence. SQL Server community was really excited about this new feature as the old IDENTITY property has some limitations which sequence objects do not have. Although usability is important and a sequence is probably a winner in this category, there are concerned voices raised about performance. Is using a sequence as fast as IDENTITY property? Letâ€™s test it out.

Test scenario

IDENTITY property is simple to use. There are no many options â€“ you specify the first number (seed) and a value that is added to the previous value to generate the next one (increment). For this test purpose, I use IDENTITY (1, 1) statement.

A sequence object has more options. Besides the first number and increment it allows cache configuration. Cache was designed for improving performance of sequence numbers generation. Hence, I decided to include different cache settings in the test. Finally, I tested six different cache settings for sequences:

No cache.

Cache 10 â€“ when a sequence object is queried for a new value for the first time, the sequence is incremented by 10 and 1 value is returned to the requestor. If the sequence is queried again, database engine does not need to go to the sequence, because it is already set to 10 â€“ the second value can be retrieved from the cache.It means, generating 1000 numbers requires updating the sequence 100 times.

Besides a test of IDENTITY and six versions of a sequence, there is also a referential test case called simple. It has hardcoded values. I added it to compare an overhead of generating numbers in different ways to not generating numbers at all.

Test results

All in parallel. SQL sessions were started from simple to sequence cache 100k.

Sequentially from sequence cache 100k to simple.

All in parallel started from sequence cache 100k to simple.

The results are in the below table:

Duration of scripts execution in seconds.

Test description

simple

identity

seq no cache

seq cache 10

seq cache 100

seq cache 1k

seq cache 10k

seq cache 100k

Sequentially from simple to seq cache 100k

253

298

515

313

286

306

253

243

All in parallel started from simple to seq cache 100k

1192

1214

1486

1248

1220

1206

1217

1207

Sequentially from seq cache 100k to simple

190

230

361

206

199

187

187

196

All in parallel started from seq cache 100k to simple

1324

1368

1596

1395

1373

1367

1367

1367

Average

740

778

990

791

770

767

756

753

Specific numbers are not important as they may vary from machine to machine. More important are relations (less than, greater than) in the same rows. Greatest numbers in each row are highlighted in red, lowest - in green, intermediate â€“ in yellow/orange. The more green a value is, the fastest method is. One of the first observations is â€“ sequence with no cache is the slowest. It is aligned with a common sense â€“ each time a new number is requested, the database engine needs to read the current sequence value and increment it. For comparison, a sequence with cache 10 does it only every 10 requests.

To have a view from one more perspective, duration is represented by percentage of how much the method is slower comparing to the fastest one in the row.

Percentage values representing how much slower the test was comparing to the fastest in the row.

Test description

simple

identity

seq no cache

seq cache 10

seq cache 100

seq cache 1k

seq cache 10k

seq cache 100k

Sequentially from simple to seq cache 100k

4,12

22,63

111,93

28,81

17,70

25,93

4,12

0,00

All in parallel started from simple to seq cache 100k

0,00

1,85

24,66

4,70

2,35

1,17

2,10

1,26

Sequentially from seq cache 100k to simple

1,60

22,99

93,05

10,16

6,42

0,00

0,00

4,81

All in parallel started from seq cache 100k to simple

0,00

3,32

20,54

5,36

3,70

3,25

3,25

3,25

Average

1,43

12,70

62,55

12,26

7,54

7,59

2,37

2,33

By looking at the average duration (the last row), as it was expected, the fastest was a test with hardcoded values (simple). There was the smallest effort required so there could not have been a surprise. It is also obvious, sequences without a cache are slowest. Additionally, it is clearly visible that sequences with bigger cache are better performing.

In my test, IDENTITY had performance similar to a sequence with a cache 10 but it may vary from a test to a test.

Summary

The test showed that a sequence object is a real competitor to IDENTITY. From performance perspective it can be even faster if cache is set properly. Cache about 100 should give an advantage to a sequence. Of course, it all depends on multiple conditions. If you do not generate values frequently, you may not notice a difference anyway no matter what cache size you choose. The decision about the size should also depend on sensitivity of your system on loosing numbers â€“ after restarting SQL Server, cache is lost with all values prepared for usage - it is strongly connected to a cache size.

Nevertheless, I am not going to be afraid of sequences performance even for tables with a high rate of inserts. If I find sequences useful for my cases, I will use them.

Popular tags

E-privacy law

Dear User,

European Union obliged us (EU websites owners) to provide you some information.
Important is that we have never gathered or processed your personal information and we still do not.
But as majority of modern websites we use cookies and we would like to tell you a little more about them.

What are cookies

Cookies are files stored on your disk inside your computer by an internet browser when you visit some websites. They contain randomly generated unique identifiers, anonymous information about your activity on the website and preferences.

Cookies usually do not contain information that allow to track you down. This data is anonymous.

Cookies may be of varios types:

sessional - they live as long as you are on the website

persistent - they are on your disk for a specific amount of time e.g. 30 days

Purpose

We use cookies on our websites to:

enhance our service by gathering statistical data about your activity on our websites - which page you visit, for how long and what time,

make your next visit easier by remembering your choices like accepting usage of cookies

Third party cookies

As we use Google AdSense and Google Analytics on our website, our partner (Google) is using information how you interact with our website to customize and adjust advertisement's content for you personal preferences.

Your rights

You have full rights to request access to this information, to update it, to delete it or to restrict processing it. However, remember that not in all cases it might be possible to fulfil your request in regard information stored in cookies.

You can disable cookies in your browser by following steps that depend on the browser you use - Managing cookies in your browser.
Cookies can be disabled just on our websites by clicking I decline in this message window. However, keep in mind that some functionality may not work properly from technical reasons after you disable cookies for example we will not be able to present you videos from Youtube, show you social media buttons, display personalised advertisements.

Once you make a decision about cookies, you can change it whenever you want by enabling cookies in your browser or clicking Remove cookies or Reconsider cookies buttons on our websites.

EU e-Privacy Directive

Dear User,

European Union obliged us (EU websites owners) to provide you some information.
Important is that we have never gathered or processed your personal information and we still do not.
But as majority of modern websites we use cookies and we would like to tell you a little more about them.

What are cookies

Cookies are files stored on your disk inside your computer by an internet browser when you visit some websites. They contain randomly generated unique identifiers, anonymous information about your activity on the website and preferences.

Cookies usually do not contain information that allow to track you down. This data is anonymous.

Cookies may be of varios types:

sessional - they live as long as you are on the website

persistent - they are on your disk for a specific amount of time e.g. 30 days

Purpose

We use cookies on our websites to:

enhance our service by gathering statistical data about your activity on our websites - which page you visit, for how long and what time,

make your next visit easier by remembering your choices like accepting usage of cookies

Third party cookies

As we use Google AdSense and Google Analytics on our website, our partner (Google) is using information how you interact with our website to customize and adjust advertisement's content for you personal preferences.

Your rights

You have full rights to request access to this information, to update it, to delete it or to restrict processing it. However, remember that not in all cases it might be possible to fulfil your request in regard information stored in cookies.

You can disable cookies in your browser by following steps that depend on the browser you use - Managing cookies in your browser.
Cookies can be disabled just on our websites by clicking I decline in this message window. However, keep in mind that some functionality may not work properly from technical reasons after you disable cookies for example we will not be able to present you videos from Youtube, show you social media buttons, display personalised advertisements.

Once you make a decision about cookies, you can change it whenever you want by enabling cookies in your browser or clicking Remove cookies or Reconsider cookies buttons on our websites.