Using an ID with an autonumber will suddenly stop producting sequential numbers for the sake of performance, regardless of actual metrics. As you can imagine, this has caused havoc. And yes, our DBAs just updated to it, which was what caused me to research this. Now I have to explain to accounting why Microsoft decided there should be giant gaps between invoice numbers.

If MS said specifically in the docs that autonumbers may not guarantee sequential numbers when they first invented it, perhaps we wouldn't be in this mess. Then again, the previous developers would have used it anyway and I'd be in the same situation.

It's funny you should mention Oracle. In Oracle, if you create a sequence and specify a CACHE value, all values it caches for future inserts are lost when the server crashes / is shut down in an unusual way. And yes, this is documented.

Oh right, if you don't specify NOCACHE, Oracle caches 20 by default, so even if you didn't specify a CACHE value, 20 will be lost.

Yes, that's exactly what Microsoft did, although from what i read, SEQUENCE columns are still OK.

And yeah... 20 sounds sensible for such purposes. I have no idea why MS thought that caching 1K-10K at a time on infrequently-used tables was a great idea. I don't even know if you can configure this. At least there's a flag to disable it.

Apparently, the requirements for our legacy system stated that the invoices MUST start at 2140. Why? Because that's how many manual invoices they had written out before the system was made, of course! But rather than start the index seed with 2041, they manually add this magic number inside the code.

When the Dead do walk seek water's run, for this the Dead will always shun.swift river's best or broadest lake, to ward the Dead and haven make.if water fails thee, fire's thy friend, if neither guards it will be thy end.

how can i say no when my own creation agrees with me without me telling it to?

Recategorized, because requiring sequentials ids may be a WTF but changing the behavior of autonumbers is the real WTF

When the Dead do walk seek water's run, for this the Dead will always shun.swift river's best or broadest lake, to ward the Dead and haven make.if water fails thee, fire's thy friend, if neither guards it will be thy end.

If you were an accountant and knew nothing about databases, but you saw a massive gap in invoice numbers, wouldn't you be concerned? Also, it is a legal requirement here for VAT invoices to be issued sequentially. Whoops.

Setting the cache value to 1 could still cause a gap of 1 if the server restarts, from what I can see.

Setting the cache value to 1 could still cause a gap of 1 if the server restarts, from what I can see.

I'm pretty sure that Oracle's line (and presumably MS take the same one) is that sequences are for creating unique values and not necessarily in a way that's gapless.
If you roll back a transaction, for example, you'll have a gap.

:fa_link: The world doesn't contain enough faces and palms to even begin to offer a reaction to that sentence

Also, it is a legal requirement here for VAT invoices to be issued sequentially. Whoops.

That's insane.

Fortunately, there's always tradeoffs. Create a new table with an integer field and one row. You need a new invoice number? Lock the record exclusively, increment the field, release the record. Sure, it's a potential hot spot, but what did people do before sequences? Oh yeah, this.

If your server is rebooting often enough to get anywhere near 2,147,483,647, you have a more serious issue to deal with.

Some people--who knows why--put smaller ranges on their sequences. If it's only a million[1] or so and you get a 10000[2] skip with every reboot, you will run out, assuming you didn't set up the sequence to roll over.

Also, it is a legal requirement here for VAT invoices to be issued sequentially.

I don't know where "here" is for you, but here (Germany) they only have to be unique, not consecutive. You can have number groups for different departments, for instance. (See http://www.iww.de/sh/quellenmaterial/id/35679 for details)

(Edit: Quick googling shows that in the UK they have to be sequential. That's what it says in the German tax law, too, but it was overruled by the decision linked above.)

Still, a WTF on MS' part. Although they are right in saying that there was never any guarantee for IDENTITY columns to be gapless - rolled back INSERTs for instance have always left gaps.

If MS said specifically in the docs that autonumbers may not guarantee sequential numbers when they first invented it, perhaps we wouldn't be in this mess.

They can't put in the 57834743847438 trillion things it doesn't do, only the things it does. "Autonumber can't be used to serve ice cream to nurses in Sudan. Autonumber can't be used to serve ice cream to doctors in Nebraska."

In SQL Server 2012 the implementation of the identity property has been changed to accommodate investments into other features. In previous versions of SQL Server the tracking of identity generation relied on transaction log records for each identity value generated. In SQL Server 2012 we generate identity values in batches and log only the max value of the batch. This reduces the amount and frequency of information written to the transaction log improving insert scalability.

What the Belgium were they thinking?! Generate identity values in batches?! How do you know which ones are available then?!

Edit: Ah, it seems they track the available, but reserved IDs in a cache, which is what causes the problem during a failover or reboot event. Still a big Belgiuming stupid idea.

Right; this is the exact same issue as SQL generally serving up unordered results in the same order. Also the exact same issue as Java having the wrong "java.home" path for decades, and ... a lot of the other shit we discuss here.

The spec doesn't matter in a world where 90% of programmers are just cowboy-coders, "looks like it works, therefore it does."

If you're designing programming tools, you need to actually extend effort to make sure things that don't have a specific guarantee actually go out of their way to demonstrate that during the course of software development.

Some people--who knows why--put smaller ranges on their sequences. If it's only a million[1] or so and you get a 10000[2] skip with every reboot, you will run out, assuming you didn't set up the sequence to roll over.

A load of people are angry because a feature that wasn't guaranteed to produce consecutive numbers doesn't produce consecutive numbers.

That's like being angry at the sea for being salty.

More like: It's worked this way for 14 years (for those of using using a small single-server installation, which is most businesses). Now it doesn't. Those of us who were using it that way now need to re-engineer our software to work differently to adjust to this change.

If you were an accountant and knew nothing about databases, but you saw a massive gap in invoice numbers, wouldn't you be concerned? Also, it is a legal requirement here for VAT invoices to be issued sequentially. Whoops.

The remedy is of course to add another column in the table and increment your invoice numbers there as you generate them. Although that's simple enough, if you discovered this is needed after upgrading from MS SQL 2008 to 2012, you have a mess on your hands. Rollback!!

They can't put in the 57834743847438 trillion things it doesn't do, only the things it does...Autonumber can't be used to serve ice cream to nurses in Sudan

Of course not. But many developers used those up-until-now sequential IDs for other things (like invoice numbers) and now will need to do some hasty fixing. I find it hard to believe that since SQL Server 2000 Microsoft hasn't encountered anyone who uses autonumber that way and therefore couldn't possibly have anticipated that this latest change might break those customers?

That being said, a cursory Google search of "SQL sequential IDs best practices" yields a number of responses on how to do this; none of the top 3 recommend using the autonumber, interestingly enough.

Lesson learned: assuming this version of the program you're using behaves a certain way will stay that way in future versions is asking for trouble. When it's a common assumption, a lot of people (including yours truly) will initially react negatively to the change, right or wrong.

My takeaway on this specifically: when the time comes time to upgrade from 2008 to 2012, I'll be asking some questions along these lines to ensure we don't suffer the same fate as some others are experiencing here.

Which if you are using proper tools isn't going to happen, but if you are interfacing with either internal hell stews of cobbled together by another department or shitty but cheap (at least according to the salesman) tool that the businessy people bought then reverse engineering time comes up pretty often.

Maybe. But plenty of documentation (even with "proper" tools) is difficult to interpret when you get to edge cases, or cases that the original developers and/or documentation writer didn't think of. Or don't go into consequences that are often less than obvious, though logically consistent. Shit happens. Everyone makes mistakes.

yeah. if it wasn't for that big blue search button i would have missed the spoinered image too.

@pjh, can we get a border around spoiler tags like we have around images to make them easier to see when this sort of image is posted? maybe in a gold to compliment the light blue of the images?

When the Dead do walk seek water's run, for this the Dead will always shun.swift river's best or broadest lake, to ward the Dead and haven make.if water fails thee, fire's thy friend, if neither guards it will be thy end.