Expecting Sequential IDs is Sexy, According to everyone except OP
-
DBAs updating to the latest version of SQL Server 2012 started getting this issue back in July:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/cf63d145-7084-4371-bde0-eb3b917c7163/identity-big-jump-100010000-a-feature?forum=transactsqlUsing 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 still better than Oracle, right?
-
Suddenly
SELECT MAX(id) + 1doesn’t sound as bad as it is...
-
It's still better than Oracle, right?
It's funny you should mention Oracle. In Oracle, if you create a sequence and specify a
CACHEvalue, 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 aCACHEvalue, 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.
-
Just start your invoices at 1000. Problem solved.
-
Well I still wouldn't use that... The risk of concurrency issues and duplicate numbers is far worse than gaps.
-
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.
-
they manually add this magic number inside the code.
TRWTF. Wonder how many bugs this has / will cause over the years.
-
can't decide if this really belongs in sidebar or not.....
@accalia thinks it probably does....
-
can't decide if this really belongs in sidebar or not.....
@accalia thinks it probably does....
@SockBot concurs
-
can't decide if this really belongs in sidebar or not.....
@accalia thinks it probably does....
Aaaaand why not @Zoidberg?
-
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
-
Now I have to explain to accounting why Microsoft decided there should be giant gaps between invoice numbers.
In the age of computers, perhaps it's time to move beyond the idea that invoice number must be sequential.
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.
Specify a cache value of 1.
-
The risk of concurrency issues and duplicate numbers is far worse than gaps.
But..but...didn't you read? PERFORMANCE is MOST IMPORTANT!! So said the authority at Microsoft!
/sarcasm
This is what happens when they let the code monkeys in to do the designing...
-
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.
-
Recategorized, because requiring sequentials ids may be a WTF but changing the behavior of expected autonumbers is the real WTF
FTFY
-
This is what happens when they let people like Atwood set the default configuration settings, and also apply those settings to existing instances when they are updated.
FTFY
-
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.
-
If you were an accountant and knew nothing about databases, but you saw a massive gap in invoice numbers, wouldn't you be concerned?
Well hopefully the the programmers would be able to explain it.
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 you roll back a transaction, for example, you'll have a gap.
Exactly (although frankly you would think you'd be able to roll back the sequences too.)
-
So let me get this straight:
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.
Still, I did find the following somewhat amusing:
My personal concern is you will eventually run out of identity numbers quickly if your server reboots often
If your server is rebooting often enough to get anywhere near 2,147,483,647, you have a more serious issue to deal with.
-
A load of people are angry because a feature that wasn't guaranteed to produce consecutive numbers doesn't produce consecutive numbers.
This.
-
Exactly (although frankly you would think you'd be able to roll back the sequences too.)
But after your transaction got a value, someone else did too, and now you have to roll back your transaction. What do you do?
-
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.
[1] asspull
[2] not asspull
-
What do you do?
Roll back HIS transaction, too, of course. Why should that son of a bitch get to keep his transaction if I can't keep mine?
-
Roll back HIS transaction, too, of course. Why should that son of a bitch get to keep his transaction if I can't keep mine?
And roll back the transaction before yours, for fairness. Even if it's committed, that's what flashback is for.
-
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.
-
Aren't you guys the same people who said SQL queries should return data in random order if no ORDER BY clause is defined?
-
for fairness
Pffft, like I care about that. I just want to not leave holes in the sequence.
-
Using an ID with an autonumber will suddenly stop producting sequential numbers for the sake of performance, regardless of actual metrics.
Has that ever been a guarantee?
-
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."
-
This seems like the root of the problem:
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.
-
Has that ever been a guarantee?
Oracle have never guaranteed it, I assume MS take the same approach considering that guaranteeing it would be practically impossible.
-
Oracle have never guaranteed it, I assume MS take the same approach considering that guaranteeing it would be practically impossible.
Reading the comments on the first link, one of the official MS posters said they never really guaranteed it, but that the issue is much less pronounced with the old method.
-
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.
-
Reading the comments on the first link, one of the official MS posters said they never really guaranteed it, but that the issue is much less pronounced with the old method.
Yeah, fair enough, the impression from the OP here though was that Microsoft suddenly decided to stop generating sequential numbers when in reality this has always happened.
How did this surprise DBAs? Surely DBAs should know about this.
-
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.
[1] asspull
[2] not asspull
Sounds like a job for ClueMan™ and his ClueBat™!
-
Aren't you guys the same people who said SQL queries should return data in random order if no ORDER BY clause is defined?
I was about to mention that. Very similar.
-
Ha. It's NEVER consistently produced sequential numbers if you're using transactions, have multiple connections, etc. I have gaps of millions in some of my tables.
So what I'm seeing here is that Microsoft is changing it to work like sequences, I.e. the way every other database has always done serial numbering.
-
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.
Case in point:
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.
-
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.
You code to the docs, not to the behavior.
You want your code to work because it works, not because it just happens to accidentally do the right thing most of the time in most situations kinda.
That's the difference between being a software developer and code monkey.
-
You code to the docs, not to the behavior.
You want your code to work because it works, not because it just happens to accidentally do the right thing most of the time in most situations kinda.
That's the difference between being a software developer and code monkey.
Until the docs are missing, or worse yet, dead wrong. Then it's reverse-engineering time!
Filed under: I have extended a system with no documentation and no source code. Have you?
-
Until the docs are missing, or worse yet, dead wrong. Then it's reverse-engineering time!
Very true, I pointed out incorrect doucmentation for Salesforce about 3 years ago, looks like they finally got around to fixing it.
-
[T]he difference between being a software developer and code monkey.
would be (IMHO) attempting to do
You code to the docs, not to the behavior.
but here in the real world rather than ideals land that you tend to argue from you can't always do so and deliver a "solution" that meets business needs. Mostly due to:
Until the docs are missing, or worse yet, dead wrong.
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.
-
Which if you are using proper tools isn't going to happen
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.
-
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.
Guess what was result #1 at the time of posting this?
[spoiler]
[/spoiler]
-
A blank image?
-
It's spoilered. Which is flakey IME.
-
-
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?
Lights, camera, New York!
