If you have ever worked with database tables containing an Identity key, you probably already know that there is a high tendency for Identity keys to get missed as part of various insert operations (particularly with bulk uploads). The easy way to get out of this, of course, is to NOT use Identity keys and thereby define your own sequencing for the database table.
But, then again, if you define your own sequencing, that is another hassle that you have to deal with by ensuring that every column that you insert as part of a bulk upload operation has a unique identifier associated with it. Since you do not have to do this while using Identity keys, Identity keys are much more convenient.
Fortunately, there is a way to get the best of both worlds even while using Identity Keys. If your Identity Keys happen to get out of sequence (as they almost always do), you can simply RESEED the Identity key.
What does that mean? It just means that you can get the Identity key to pick up where it last left off. So if a number was missed in the process of a bulk upload operation, you can simply delete the offending records and reseed your table to start at the missed number. For example, if you run a bulk upload operation and somewhere along the line, the numbers 14 and 15 get missed as part of the load operation, you can simply run a delete command to delete all numbers greater than 13, reseed the identity key and then re-load all of the remaining records!
So just how do you RESEED the Identity key?
You just use the following command: DBCC CHECKIDENT ("Person.AddressType", RESEED, 13);
Since the last number that was successfully inserted was 13, you set the RESEED value to 13 so that the subsequent insert will begin inserting at 14 (13 + 1).
That's all there is to it!
If you want to read more about the DBCC CHECKIDENT command, you can read about it here: http://msdn.microsoft.com/en-us/library/ms176057.aspx
But, then again, if you define your own sequencing, that is another hassle that you have to deal with by ensuring that every column that you insert as part of a bulk upload operation has a unique identifier associated with it. Since you do not have to do this while using Identity keys, Identity keys are much more convenient.
Fortunately, there is a way to get the best of both worlds even while using Identity Keys. If your Identity Keys happen to get out of sequence (as they almost always do), you can simply RESEED the Identity key.
What does that mean? It just means that you can get the Identity key to pick up where it last left off. So if a number was missed in the process of a bulk upload operation, you can simply delete the offending records and reseed your table to start at the missed number. For example, if you run a bulk upload operation and somewhere along the line, the numbers 14 and 15 get missed as part of the load operation, you can simply run a delete command to delete all numbers greater than 13, reseed the identity key and then re-load all of the remaining records!
So just how do you RESEED the Identity key?
You just use the following command: DBCC CHECKIDENT ("Person.AddressType", RESEED, 13);
Since the last number that was successfully inserted was 13, you set the RESEED value to 13 so that the subsequent insert will begin inserting at 14 (13 + 1).
That's all there is to it!
If you want to read more about the DBCC CHECKIDENT command, you can read about it here: http://msdn.microsoft.com/en-us/library/ms176057.aspx
No comments:
Post a Comment