May 9, 2011 at 9:43 pm
Comments posted to this topic are about the item Identity Reseed
May 9, 2011 at 11:34 pm
Nice and easy, thanks.
I was looking for a gotcha, but couldn't really find one.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 9, 2011 at 11:51 pm
Good question , thanks
May 10, 2011 at 4:36 am
Thank you Steve
M&M
May 10, 2011 at 4:52 am
Thanks, nice to have one that relies on logic as well as "book learning" 😉
May 10, 2011 at 6:07 am
Taking it one step further, there's additional knowledge to be had from this question. Since this table lacks a primary key, it is possible to stick yourself with duplicate IDs after reseeding, which could be disastrous.
Know what you're getting into before reseeding an identity column.
May 10, 2011 at 6:24 am
I agree there is more to discuss. As BOL says:
Current identity value is set to the new_reseed_value. If no rows have been inserted to the table since it was created, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. Otherwise, the next row inserted uses new_reseed_value + 1. If the value of new_reseed_value is less than the maximum value in the identity column and a PRIMARY KEY or UNIQUE constraint exists on the identity column, error message 2627 will be generated on subsequent references to the table.
What BOL does NOT say is that SQL Server will be perfectly happy assigning a duplicate ID if there is no primary or unique constraint.
I have occasionally used identity fields where they were not the primary key and from now on I am putting a unique constraint on them.
May 10, 2011 at 8:00 am
Oops, lucked into the right answer there, I was thinking reseed was setting the increment, and went with 3 because it was 1+2.
Nice question!
I want my points removed! 😛
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
May 10, 2011 at 9:25 am
Nice and easy.. Thanks Steve.
May 10, 2011 at 9:25 am
Not as easy for some ouf us as it looks. The real "gotcha" here is that the RESEED option sets the current value of the column's internal counter while the SEED parameter of the IDENTITY property (in the create table statement) specifies the first value to assign.
To be explicit, consider that in this example, the "ID" column was defined with a seed value of 1 and that's what the first row used. The RESEED parameter is 2, but the next row inserted uses 3.
May 10, 2011 at 9:42 am
john.arnott (5/10/2011)
To be explicit, consider that in this example, the "ID" column was defined with a seed value of 1 and that's what the first row used. The RESEED parameter is 2, but the next row inserted uses 3.
That got me too.
What use is there for reseeding in real-world applications?
May 10, 2011 at 10:01 am
Toreador (5/10/2011)What use is there for reseeding in real-world applications?
BOL gives the example of a counter reaching the limit for a column (e.g. over 2.147 billion for an integer). RESEED would let you re-use lower values.
I can imagine also that it may also be handy while running multiple iterations of a test scenario where you've deleted the rows from the previous run and want the new run to start in the same place.
May 10, 2011 at 10:09 am
A few scenarios. I have reached the 2b limit before and needed to reseed data. The table held a rolling 50-100k rows, but lots of inserts every day (and deletes).
You also could potentially delete a bunch of data, perhaps a bad data load, and want to reseed. Not necessary, but sometimes people want to clean up the system and keep the identities contiguous.
May 10, 2011 at 11:04 am
Nice question thanks! I do wish that RESEED worked like the SEED in the column definition, so that you are always setting what the next value is that will be used.
May 10, 2011 at 11:27 am
steven.malone (5/10/2011)
I agree there is more to discuss. As BOL says:Current identity value is set to the new_reseed_value. If no rows have been inserted to the table since it was created, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. Otherwise, the next row inserted uses new_reseed_value + 1. If the value of new_reseed_value is less than the maximum value in the identity column and a PRIMARY KEY or UNIQUE constraint exists on the identity column, error message 2627 will be generated on subsequent references to the table.
What BOL does NOT say is that SQL Server will be perfectly happy assigning a duplicate ID if there is no primary or unique constraint.
I have occasionally used identity fields where they were not the primary key and from now on I am putting a unique constraint on them.
BOL actually does mention this potential problem:
http://msdn.microsoft.com/en-us/library/ms176057%28v=SQL.90%29.aspx
DBCC CHECKIDENT ( table_name, RESEED, new_reseed_value )
Current identity value is set to the new_reseed_value. If no rows have been inserted to the table since it was created, or all rows have been removed by using the TRUNCATE TABLE statement, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. Otherwise, the next row inserted uses new_reseed_value + the current increment value.
If the table is not empty, setting the identity value to a number less than the maximum value in the identity column can result in one of the following conditions:
If a PRIMARY KEY or UNIQUE constraint exists on the identity column, error message 2627 will be generated on later insert operations into the table because the generated identity value will conflict with existing values.
If a PRIMARY KEY or UNIQUE constraint does not exist, later insert operations will result in duplicate identity values.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply