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:

    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 still better than Oracle, right?



  • Suddenly SELECT MAX(id) + 1 doesn’t sound as bad as it is...



  • @LoremIpsum said:

    It's still better than Oracle, right?

    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.



  • 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.



  • @LoremIpsum said:

    they manually add this magic number inside the code.

    TRWTF. Wonder how many bugs this has / will cause over the years.


  • sockdevs

    can't decide if this really belongs in sidebar or not.....

    @accalia thinks it probably does....



  • @accalia said:

    can't decide if this really belongs in sidebar or not.....

    @accalia thinks it probably does....

    @SockBot concurs

    <!-- Posted by SockBot 0.12.5 "Guileful Ginny" on Thu Oct 23 2014 10:46:55 GMT-0400 (EDT)-->


  • @accalia said:

    can't decide if this really belongs in sidebar or not.....

    @accalia thinks it probably does....

    Aaaaand why not @Zoidberg?

    <!-- Posted by SockBot 0.12.5 "Guileful Ginny" on Thu Oct 23 2014 10:47:55 GMT-0400 (EDT)-->

  • sockdevs

    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


  • Discourse touched me in a no-no place

    @LoremIpsum said:

    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.

    @powerlord said:

    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.



  • @LoremIpsum said:

    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.



  • @accalia said:

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

    FTFY



  • @redwizard said:

    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



  • @LoremIpsum said:

    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.


  • Discourse touched me in a no-no place

    @LoremIpsum said:

    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.

    @LoremIpsum said:

    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.


  • Discourse touched me in a no-no place

    @loopback0 said:

    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.)


  • sockdevs

    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.


  • @RaceProUK said:

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

    This.



  • @FrostCat said:

    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?


  • Discourse touched me in a no-no place

    @RaceProUK said:

    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


  • Discourse touched me in a no-no place

    @boomzilla said:

    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?



  • @FrostCat said:

    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.


  • area_deu

    @LoremIpsum said:

    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?


  • Discourse touched me in a no-no place

    @loopback0 said:

    for fairness

    Pffft, like I care about that. I just want to not leave holes in the sequence.



  • @LoremIpsum said:

    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?



  • @LoremIpsum said:

    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."


  • mod

    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.



  • @blakeyrat said:

    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.


  • mod

    @loopback0 said:

    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.



  • @abarker said:

    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.


  • sockdevs

    @FrostCat said:

    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™!



  • @mott555 said:

    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.


  • Discourse touched me in a no-no place

    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.



  • @RaceProUK said:

    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:

    @LoremIpsum said:

    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!!

    @blakeyrat said:

    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.



  • @redwizard said:

    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.



  • @blakeyrat said:

    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?



  • @tarunik said:

    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.



  • @blakeyrat said:

    [T]he difference between being a software developer and code monkey.

    would be (IMHO) attempting to do

    @blakeyrat said:

    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:

    @tarunik said:

    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.



  • @locallunatic said:

    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.


  • Discourse touched me in a no-no place

    @redwizard said:

    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?


  • Discourse touched me in a no-no place

    It's spoilered. Which is flakey IME.



  • @PJH said:

    Which is flakey IME.

    I see.


  • sockdevs

    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?


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.