Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Reseeding Identity values Expand / Collapse
Author
Message
Posted Sunday, September 23, 2007 10:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, May 9, 2011 2:49 AM
Points: 256, Visits: 61
Comments posted to this topic are about the item Reseeding Identity values
Post #401596
Posted Wednesday, December 23, 2009 3:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 18, 2014 2:06 PM
Points: 37, Visits: 176
I believe this will produce errors on tables with existing data.

From BOL on DBCC CHECKIDENT:

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, 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.



Post #838778
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse