Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Identity Seeding Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, November 30, 2012 8:48 AM
 SSCrazy Group: General Forum Members Last Login: 2 days ago @ 7:51 AM Points: 2,671, Visits: 6,753
 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
Post #1391386
 Posted Friday, November 30, 2012 9:35 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 7:06 PM Points: 7,945, Visits: 8,369
 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'S iomadh doigh a th’ air cu a mharbhadh gun a thachdadh le ìme
Post #1391425
 Posted Friday, November 30, 2012 9:37 AM
 SSC Eights! Group: General Forum Members Last Login: Today @ 8:43 AM Points: 815, Visits: 3,826
 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...
Post #1391426
 Posted Friday, November 30, 2012 9:50 AM
 SSCrazy Group: General Forum Members Last Login: Wednesday, November 20, 2013 10:00 AM Points: 2,719, Visits: 4,724
 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/ _____________________________________________"The only true wisdom is in knowing you know nothing""O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"(So many miracle inventions provided by MS to us...)How to post your question to get the best and quick help
Post #1391428
 Posted Friday, November 30, 2012 9:50 AM
 SSCrazy Group: General Forum Members Last Login: 2 days ago @ 7:51 AM Points: 2,671, Visits: 6,753
 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
Post #1391430
 Posted Friday, November 30, 2012 9:53 AM
 SSCrazy Group: General Forum Members Last Login: 2 days ago @ 7:51 AM Points: 2,671, Visits: 6,753
 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...I don't think that's the way I'd set that up. I'd be more likely (if it was important to keep the ID similar for some reason) to have a second column for a sub-ID and use both columns for the key. That way you can have an 11 person family and it doesn't matter. --------------------------------------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
Post #1391432
 Posted Friday, November 30, 2012 12:36 PM
 SSCertifiable Group: General Forum Members Last Login: Wednesday, November 06, 2013 6:20 PM Points: 5,742, Visits: 6,230
 Typically I do it for merge replication. Considering how much I try to avoid that particular method of replication, I don't do it much. - Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake. For better assistance in answering your questions | Forum NetiquetteFor index/tuning help, follow these directions. |Tally TablesTwitter: @AnyWayDBA
Post #1391507
 Posted Monday, December 03, 2012 8:53 AM
 SSCertifiable Group: General Forum Members Last Login: 2 days ago @ 5:24 AM Points: 6,976, Visits: 6,126
 I've definitely had to seed at a different number, sometimes at zero, sometimes with multiple zeros after the starting number (Invoicing). The only time I've used different increments, though, is in discussing Identity with other people. I've never had to use an increment other than 1 in RL work.EDIT: To clarify the increment statement, my environment contains all sorts of different systems run by different OSs. Which means having to comply with certain unchangable sized datatypes. Mainframe, for instance, has some fields which are 12 digits long. We can't go smaller for data that feeds to that system or the mainframe will freak out. So we start at 100,000,000,000 (without the commas of course) for certain types of values. (This is a whitewashed example). Brandie Tarvin, MCITP Database AdministratorWebpage: http://www.BrandieTarvin.netLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1392008
 Posted Monday, December 03, 2012 5:55 PM
 SSC-Dedicated Group: General Forum Members Last Login: Yesterday @ 11:46 AM Points: 34,581, Visits: 28,767
 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...Having just been through a similar nightmare...WHHHHAAAATTTT??? They didn't see the painfully obvious advantages of storing such unpredictable data as full elemental XML with multiple hierarchical levels that would handle things correctly ad spontaneously even if one of the children decided to have a sex change or wanted to become the father of his mother's children thereby becoming the brother of his own children?Even if they messed up there, they really could have benefitted from EDI here.Can't.... hold.... it... in.... GAAAAHHHH!!!! SPOM!!! ROFLMAO!!! --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." "Change is inevitable. Change for the better is not." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1392204
 Posted Monday, December 03, 2012 6:11 PM
 SSC-Dedicated Group: General Forum Members Last Login: Yesterday @ 11:46 AM Points: 34,581, Visits: 28,767
 To answer the original question, yes... we start most tables off at 1000 with the understanding that 0 through 10 are typically reserved for "very special use" and 11 through 1000 are reserved for "other things that may come up".I've also had to do things like what JasonA and Steve Thompson had to go through (although I fought tooth and nail to not have it so).I've also reseeded an IDENTITY PK back to the beginning to "freeze" inserts on a table without the use of a trigger. --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." "Change is inevitable. Change for the better is not." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1392206

 Permissions