Identity Seeding

  • Just curious

    Anyone found a reason to declare an Identity column that wasn't (1,1)?

    Sure, legacy data can cause you to start the seed higher, but you don't really NEED to make it different. And does anyone ever increment by more than 1?

    --------------------------------------
    When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
    --------------------------------------
    It’s unpleasantly like being drunk.
    What’s so unpleasant about being drunk?
    You ask a glass of water. -- Douglas Adams

  • Hi,

    Yes i used to work with a credit card processing system that used to increment by 10 and started seeding at 10000. I don't know why that was chosen as the start no. and i can't really divulge why they incremented by 10 but it was necessary.

    Thanks,

    Simon



    MCSE: Data Platform
    MCSE: Business Intelligence
    Follow me on Twitter: @WazzTheBadger
    LinkedIn Profile: Simon Osborne

  • Yup, a few times. I've had to create tables that needed to start the identity at a specific value, for example if we are moving to a new system and not importing old data yet we want to pick-up where the old ID's left off, changing the seed is critical.

    As for increment, I think the only time I've had to do that is when I'm building a table of ordered items where Date doesn't work as a good Order identifier and I want to leave room to add items in between. If you ever did any old-school BASIC programming with line numbers, using 10, 20, 30 etc was critical as there was always something that needed to be added. Same thing here.

    So yup, on my end I've had to change the Seed and Increment a fair number of times 🙂

  • Replication...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Once. Had an app that was freaked that they were going to run out of numbers so we seeded them to max negative value so that they could increment all the way through that to the max positive value.

    They didn't really need it, but it sure made them happy.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I haven't done this myself, but I've seen discussions about self-managing identities in replicated systems. For example, if you have two offices and want to make sure that identities are unique , you can set it up so that one uses odd numbers (1, 2), the other uses even (2, 2).

  • Steve Thompson-454462 (11/30/2012)


    I haven't done this myself, but I've seen discussions about self-managing identities in replicated systems. For example, if you have two offices and want to make sure that identities are unique , you can set it up so that one uses odd numbers (1, 2), the other uses even (2, 2).

    Interesting idea... On one of the projects I'm working on now one of my initial ideas was to have unique ID's across every table (didn't want to use GUID due to overhead) so I seeded each table's PK from a unique 8 digit number. So for example UserID would be 10000000, AddressID would be 20000000 and so forth, and the thought was the first two digits of the ID would identify which type it was. It actually became more confusing for everyone, so I just went back to seeding everything from 1 to keep it simple. But I still like the idea of unique ID's across every table...

  • I once had a fact table that could have more than 2 billion rows, but still less than a bigint, so I started with a negative seed so I could keep my identity column as an int.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (11/30/2012)


    I once had a fact table that could have more than 2 billion rows, but still less than a bigint, so I started with a negative seed so I could keep my identity column as an int.

    Why didn't you want it as a bigint?

    --------------------------------------
    When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
    --------------------------------------
    It’s unpleasantly like being drunk.
    What’s so unpleasant about being drunk?
    You ask a glass of water. -- Douglas Adams

  • Stefan Krzywicki (11/30/2012)


    Koen Verbeeck (11/30/2012)


    I once had a fact table that could have more than 2 billion rows, but still less than a bigint, so I started with a negative seed so I could keep my identity column as an int.

    Why didn't you want it as a bigint?

    Because it takes up more bytes than an int?

    On 2^31-1 rows (the maximum if you only take positive identity values) you take up 8,5 GB. If you take a bigint, for the same data, you consume another 8,5GB.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (11/30/2012)


    Stefan Krzywicki (11/30/2012)


    Koen Verbeeck (11/30/2012)


    I once had a fact table that could have more than 2 billion rows, but still less than a bigint, so I started with a negative seed so I could keep my identity column as an int.

    Why didn't you want it as a bigint?

    Because it takes up more bytes than an int?

    On 2^31-1 rows (the maximum if you only take positive identity values) you take up 8,5 GB. If you take a bigint, for the same data, you consume another 8,5GB.

    I thought that might be it. Wasn't sure if there was a reason other than space.

    --------------------------------------
    When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
    --------------------------------------
    It’s unpleasantly like being drunk.
    What’s so unpleasant about being drunk?
    You ask a glass of water. -- Douglas Adams

  • Once I included an identity with increment 0 in one of the questions in a set designed to discover whether job applicants had two brain cells to rub together. Does that count as using something other than (1,1), or would that be misusing? The result was rather disappointing, only about 10% said anything like "you can't do that" or "that's an invalid increment".

    A couple of times I've used integer identity(-2147483648,1) for table which would have rather a lot of inserts (rather a lot of deletes too - the actual row count at any point of time would be much less than 2 billion, but the identity range needed to be that big).

    Tom

  • If our devs had used the patient ID value as the identity, it would have been a (10,10) seed. The way the current billing application handles patient IDs is increments of 10, then if it's a family practice, family members get added using the base +1. So wife (primary policy holder) might be patid 1270, the husband (on the wifes policy) would be 1271, and the three kids would be 1272-1274.

    Luckily, we've never run into anyone who would use all 10 values...

  • jasona.work (11/30/2012)


    If our devs had used the patient ID value as the identity, it would have been a (10,10) seed. The way the current billing application handles patient IDs is increments of 10, then if it's a family practice, family members get added using the base +1. So wife (primary policy holder) might be patid 1270, the husband (on the wifes policy) would be 1271, and the three kids would be 1272-1274.

    Luckily, we've never run into anyone who would use all 10 values...

    You will have trouble with that:

    http://www.people.co.uk/news/uk-world-news/2011/04/17/meet-britain-s-biggest-family-102039-23066431/

    :hehe:

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • L' Eomot Inversé (11/30/2012)


    Once I included an identity with increment 0 in one of the questions in a set designed to discover whether job applicants had two brain cells to rub together. Does that count as using something other than (1,1), or would that be misusing? The result was rather disappointing, only about 10% said anything like "you can't do that" or "that's an invalid increment".

    A couple of times I've used integer identity(-2147483648,1) for table which would have rather a lot of inserts (rather a lot of deletes too - the actual row count at any point of time would be much less than 2 billion, but the identity range needed to be that big).

    No, no. I like that. Good interview question.

    This is just idle musing on my part. I wanted to find how other people use something basic that has a changeable, but rarely used component.

    I think general discussions of this sort can be good for learning and exploring the tech. Also lets you think more about what you do routinely.

    --------------------------------------
    When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
    --------------------------------------
    It’s unpleasantly like being drunk.
    What’s so unpleasant about being drunk?
    You ask a glass of water. -- Douglas Adams

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply