11

And they seem to be fairly large skips, in the thousands even. Here's an example:

Exhibit A

The last Ids of this table go above the 30.000 mark even though there are less than a thousand rows in the table.

  • What is causing this?
  • How can I prevent this in the future?
  • How can I stop my current table from continuing on this dark road?

EDIT: This is happening in an old, small desktop application of mine and I have not ever deleted rows from this table nor have I used any transactions and rollbacks.

Alternatex
  • 1,505
  • 4
  • 24
  • 47
  • 4
    [Stop making assumptions about `IDENTITY`](https://sqlblog.org/2009/10/12/bad-habits-to-kick-making-assumptions-about-identity), stop worrying about gaps - they are really **NOT** a problem! – marc_s May 16 '15 at 13:25
  • 1
    Why would you want to prevent this? You don't have a problem, so there is nothing to fix. –  May 16 '15 at 15:02
  • @a_horse_with_no_name Accountants, and government auditors, don't like when there are missing items. It triggers investigations about what they did with those invoices, sales orders, documents, cheques. And since it worked perfectly as documented (in SQL 2000 BoL), it's frustrating that it got broken with out way to opt-out of the "improvement". – Ian Boyd Feb 23 '22 at 19:20
  • 2
    @IanBoyd there are no words to explain my anger regarding the comments like "this is not a problem", "stop worrying about it". Auditors encountered this in our reports and they triggered an investigation to find out who had access in our database. And here I am today, trying to explain the auditors that this is a feature of the database management system and most likely Microsoft did something wrong here (wish me luck since I am the only one who has access). – late1 Oct 08 '22 at 14:29

4 Answers4

23

This is by design in SQL server. SQL Server guarantees that the next number generated will be unique and incremental, it does not guarantee it will be in sequence.

This started in SQL 2012. Simply restart the service to repro the issue. It is well know change in behavior, it has always been documented this way, and is marked by design. Its just an unexpected change in behavior

Greg
  • 3,861
  • 3
  • 23
  • 58
11

What is causing this?

A couple of potential causes come to mind:

  • Rows were deleted?
  • The results you're looking at aren't sorted by id?
  • Identifiers were allocated in a transaction which wasn't committed?
  • The database engine allocated potential identifiers as an internal performance tuning and that allocation was lost (unexpected server re-start, for example)?

There could be more potential causes I'm not thinking of.

How can I prevent this in the future?

Depends on the cause. But it's kind of a moot point, really. Why would you need to "prevent this"? What exactly is wrong with this situation? Identifiers don't need to be consecutive, they just need to be unique. (And preferably sequential for an index, otherwise the database will have to re-build the index.)

How can I stop my current table from continuing on this dark road?

The dark road of... generating unique identifiers? Well, I guess you could manually supply unique identifiers. GUIDs are good for that sort of thing. There are pros and cons, though. (I don't know if recent implementations have improved this, but historically GUIDs don't make for a good clustered index.)

David
  • 208,112
  • 36
  • 198
  • 279
  • I was fairly sure setting my column to auto-increment with `Identity Increment` and `Identity Seed` to 1 would result in sequential numbers being generated. Am I mistaken? – Alternatex May 16 '15 at 13:25
  • @Alternatex: You're not mistaken at all. It seems like sequential numbers *are* being generated. But there are things which could happen outside of the generation of that sequence (I've thought of four) which could cause the data you're showing in the question. – David May 16 '15 at 13:27
  • The only possible thing that could've happened is your 4th assumption. So I guess I'll need to dig a little deeper. – Alternatex May 16 '15 at 13:38
  • 1
    @Alternatex: Feel free to dig deeper if you want, but I really don't see why this is even a problem. The identity still identifies the records. If some other part of the system assumes consecutive identifiers then that's what's broken and needs to be corrected. It's not a valid assumption to make and the database engine makes no such guarantee. – David May 16 '15 at 13:40
2

It could be caused by deletion and rollback, when you inserted data are rejected by constraints or triggers. You can not rely on it.

Tim3880
  • 2,563
  • 1
  • 11
  • 14
1

I suspect that either the rows got deleted or insert statements were done that incremented the counter but they were in a transaction that was then rolled back.

Looking at this related answer, it looks like it could also be caused by SQL Server caching future identity values and then getting restarted before it has had the opportunity to use them.

Identity increment is jumping in SQL Server database

Community
  • 1
  • 1
John Hodge
  • 1,645
  • 1
  • 13
  • 13