-1

I have encountered an issue with the Table ID (PrimaryKey) in SQL Server, as it is not increasing in the expected sequential order. Could you please provide insight into why this might be happening?

ID
---
1
2
3
4
1010
alireza rayani
  • 31
  • 8
  • 1
    `IDENTITY` column values are incremented in sequence but there may be gaps. In short, you shouldn't care since the purpose is a surrogate key. – Dan Guzman Dec 28 '19 at 20:47
  • 1
    Please do not post a screenshot without a description. No one on this site are willing to spend time on the question, on that, you did not spent a minute. Write text description of your problem. Post some errornous data. Post schemes, tables, what you have tried. Do not throw some random text with links. – user14063792468 Dec 28 '19 at 21:08
  • 1
    And please read the https://stackoverflow.com/help/how-to-ask topic. – user14063792468 Dec 28 '19 at 21:09
  • 1
    What you see there is a *documented*, **intentional**, and expected feature of the `IDENTITY` column. The value of an `IDENTITY` is meaningless other than to be always incrementing and be unique. – Thom A Dec 28 '19 at 21:24
  • 1
    Also, it's interesting that you're concerned about the skip from 6 to 1010, but not from 4 to 6. – Thom A Dec 28 '19 at 21:28
  • 1
    Does this answer your question? [SQL Server: arbitrary auto-increment of primary key](https://stackoverflow.com/questions/18444407/sql-server-arbitrary-auto-increment-of-primary-key) – Stephen C Dec 28 '19 at 22:05
  • This is a(n easily found) faq. Always read the manual re functionality you are using. Please don't ask duplicate questions. Before considering posting please read the manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. – philipxy Apr 23 '20 at 08:35

1 Answers1

4

IDENTITY and SEQUENCE generation is optimized for speed. So SQL Sever only saves the last value generated to disk every 1000 values (by default). In case of an unplanned shutdown (or even a planned shutdown for SQL Server 2012) the sequence generation will show this 1000-value gap.

You can disable the caching of values with a database option, or set Trace Flag 272. But writing every generated value to the database can limit the throughput of your application.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67