November 30, 2012 at 9:53 am
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
November 30, 2012 at 12:36 pm
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.
Never 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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 3, 2012 at 8:53 am
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).
December 3, 2012 at 5:55 pm
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...
<Irony=ON>
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.
<Irony=OFF>
Can't.... hold.... it... in.... GAAAAHHHH!!!! SPOM!!! ROFLMAO!!! :-):-D:-P:hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
December 3, 2012 at 6:11 pm
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
Change is inevitable... Change for the better is not.
December 4, 2012 at 4:15 am
I've had to reseed a few times. Mainly because of data cleanup (someone screwed something up awful and things had to be fixed).
December 5, 2012 at 7:41 am
We configure tables to start with a higher seed value when creating a new database for an existing Customer with the intention of loading their older data into the new tables. We want to retain all old ID values and their FK relationships as is.
Hakim Ali
www.sqlzen.com
December 5, 2012 at 7:47 am
hakim.ali (12/5/2012)
We configure tables to start with a higher seed value when creating a new database for an existing Customer with the intention of loading their older data into the new tables. We want to retain all old ID values and their FK relationships as is.
Sure, but if you're loading the old data into the new tables, you can just declare normally and once you've added the data, it'll continue from the highest value. I don't think that works if you redefine the identity once there's data in the table though.
--------------------------------------
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
December 5, 2012 at 7:51 am
Sure, but if you're loading the old data into the new tables, you can just declare normally and once you've added the data, it'll continue from the highest value. I don't think that works if you redefine the identity once there's data in the table though.
In our scenario, we want to add "new" configuration data into the blank table before we load the Customer's older data in. Therefore, we seed the identity comfortably higher than the Customer's existing max ID, add the new records, and then later just import the older data with identity insert on. When all is said and done and cleaned up, it does continue from the highest value.
Hakim Ali
www.sqlzen.com
December 5, 2012 at 7:53 am
hakim.ali (12/5/2012)
Sure, but if you're loading the old data into the new tables, you can just declare normally and once you've added the data, it'll continue from the highest value. I don't think that works if you redefine the identity once there's data in the table though.
In our scenario, we want to add "new" configuration data into the blank table before we load the Customer's older data in. Therefore, we seed the identity comfortably higher than the Customer's existing max ID, add the new records, and then later just import the older data with identity insert on. When all is said and done and cleaned up, it does continue from the highest value.
Ah, I figured you did it by loading existing data first. Makes sense to seed higher if you're loading later.
--------------------------------------
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
December 5, 2012 at 10:00 am
Stefan Krzywicki (12/5/2012)
hakim.ali (12/5/2012)
Sure, but if you're loading the old data into the new tables, you can just declare normally and once you've added the data, it'll continue from the highest value. I don't think that works if you redefine the identity once there's data in the table though.
In our scenario, we want to add "new" configuration data into the blank table before we load the Customer's older data in. Therefore, we seed the identity comfortably higher than the Customer's existing max ID, add the new records, and then later just import the older data with identity insert on. When all is said and done and cleaned up, it does continue from the highest value.
Ah, I figured you did it by loading existing data first. Makes sense to seed higher if you're loading later.
Maybe even if you are loading existing data first - there may be gaps in the old identity sequence, so that the loading has to be done using identity insert and that won't set the seed to anything useful. In that case initialising the seed to a useful value in the declaration of the table is less effort than initialising it to the wrong value in the declaration and then correcting it with a call to dbcc checkident after loading the old data.
Tom
December 5, 2012 at 1:18 pm
Stefan Krzywicki (11/30/2012)
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.
We have several tables doing this, for example on a Guarantor or Obligor table I'll have the primary account ID and a ObligorID or GuarantorID which is seeded at 1 for each account. So Account 123 Guarantor 1, Guarantor 2, Account 124 Guarantor 1, Guarantor 2, and so forth The AccountID and GuarantorId together make-up the primary key for this table.
It's simpler to do this than to depend on a unique key for a few reasons.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply