InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 20 Jun 2013 01:56:12 GMT20http://www.sqlservercentral.com/Forums/Topic1390601-391-1.aspx[quote][b]Stefan Krzywicki (11/30/2012)[/b][hr][quote][b]jasona.work (11/30/2012)[/b][hr]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...[/quote]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.[/quote]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.Wed, 05 Dec 2012 13:18:42 GMTsamalexhttp://www.sqlservercentral.com/Forums/Topic1390601-391-1.aspx[quote][b]Stefan Krzywicki (12/5/2012)[/b][hr][quote][b]hakim.ali (12/5/2012)[/b][hr][quote]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.[/quote]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.[/quote]Ah, I figured you did it by loading existing data first. Makes sense to seed higher if you're loading later.[/quote]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.Wed, 05 Dec 2012 10:00:44 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1390601-391-1.aspx[quote][b]hakim.ali (12/5/2012)[/b][hr][quote]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.[/quote]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.[/quote]Ah, I figured you did it by loading existing data first. Makes sense to seed higher if you're loading later.Wed, 05 Dec 2012 07:53:40 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic1390601-391-1.aspx[quote]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.[/quote]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.Wed, 05 Dec 2012 07:51:24 GMThakim.alihttp://www.sqlservercentral.com/Forums/Topic1390601-391-1.aspx[quote][b]hakim.ali (12/5/2012)[/b][hr]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.[/quote]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.Wed, 05 Dec 2012 07:47:50 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic1390601-391-1.aspxWe 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.Wed, 05 Dec 2012 07:41:42 GMThakim.alihttp://www.sqlservercentral.com/Forums/Topic1390601-391-1.aspxI've had to reseed a few times. Mainly because of data cleanup (someone screwed something up awful and things had to be fixed).Tue, 04 Dec 2012 04:15:21 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic1390601-391-1.aspxTo 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.Mon, 03 Dec 2012 18:11:24 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1390601-391-1.aspx[quote][b]jasona.work (11/30/2012)[/b][hr]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...[/quote]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:Mon, 03 Dec 2012 17:55:44 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1390601-391-1.aspxI'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).Mon, 03 Dec 2012 08:53:29 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic1390601-391-1.aspxTypically I do it for merge replication. Considering how much I try to avoid that particular method of replication, I don't do it much.Fri, 30 Nov 2012 12:36:26 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1390601-391-1.aspx[quote][b]jasona.work (11/30/2012)[/b][hr]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...[/quote]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.Fri, 30 Nov 2012 09:53:12 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic1390601-391-1.aspx[quote][b]L' Eomot Inversé (11/30/2012)[/b][hr]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).[/quote]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.Fri, 30 Nov 2012 09:50:54 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic1390601-391-1.aspx[quote][b]jasona.work (11/30/2012)[/b][hr]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...[/quote]You will have trouble with that:[url]http://www.people.co.uk/news/uk-world-news/2011/04/17/meet-britain-s-biggest-family-102039-23066431/[/url]:hehe:Fri, 30 Nov 2012 09:50:01 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1390601-391-1.aspxIf 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...Fri, 30 Nov 2012 09:37:56 GMTjasona.workhttp://www.sqlservercentral.com/Forums/Topic1390601-391-1.aspxOnce 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).Fri, 30 Nov 2012 09:35:08 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1390601-391-1.aspx[quote][b]Koen Verbeeck (11/30/2012)[/b][hr][quote][b]Stefan Krzywicki (11/30/2012)[/b][hr][quote][b]Koen Verbeeck (11/30/2012)[/b][hr]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.[/quote]Why didn't you want it as a bigint?[/quote]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.[/quote]I thought that might be it. Wasn't sure if there was a reason other than space.Fri, 30 Nov 2012 08:48:39 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic1390601-391-1.aspx[quote][b]Stefan Krzywicki (11/30/2012)[/b][hr][quote][b]Koen Verbeeck (11/30/2012)[/b][hr]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.[/quote]Why didn't you want it as a bigint?[/quote]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.Fri, 30 Nov 2012 08:45:04 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1390601-391-1.aspx[quote][b]Koen Verbeeck (11/30/2012)[/b][hr]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.[/quote]Why didn't you want it as a bigint?Fri, 30 Nov 2012 08:36:54 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic1390601-391-1.aspxI 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.Fri, 30 Nov 2012 08:34:24 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1390601-391-1.aspx[quote][b]Steve Thompson-454462 (11/30/2012)[/b][hr]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).[/quote]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...Fri, 30 Nov 2012 08:18:49 GMTsamalexhttp://www.sqlservercentral.com/Forums/Topic1390601-391-1.aspxI 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).Fri, 30 Nov 2012 08:09:32 GMTSteve Thompson-454462http://www.sqlservercentral.com/Forums/Topic1390601-391-1.aspxOnce. 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.Fri, 30 Nov 2012 08:01:56 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1390601-391-1.aspxReplication...Thu, 29 Nov 2012 09:42:16 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1390601-391-1.aspxYup, 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 :)Thu, 29 Nov 2012 09:23:38 GMTsamalexhttp://www.sqlservercentral.com/Forums/Topic1390601-391-1.aspxHi,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,SimonThu, 29 Nov 2012 09:01:41 GMTs_osborne2http://www.sqlservercentral.com/Forums/Topic1390601-391-1.aspxJust curiousAnyone 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?Thu, 29 Nov 2012 08:25:40 GMTStefan Krzywicki