header image
 

SQL 2005 Change Identity column current value

How do I… Reseed a SQL Server identity column? | Servers and Storage | TechRepublic.com

method 1

<code>DBCC CHECKIDENT  (   <em>tablename  </em>  [, [NORESEED | RESEED [, <em>newreseedvalue</em>]]]  )  [WITH NO_INFOMSGS]</code>

Table A defines this statement’s optional parameters.

Table A: DBCC CHECKIDENT

Parameter

Description

NORESEED
Returns the current identity value and the current maximum value of the identity column, without reseeding. These values are usually (and should be) the same.
RESEED
Changes the current identity value, using the maximum value in the identity column, if the current identity value is less than the maximum identity value stored in the identity column.
<em>newreseedvalue</em>
Specifies the new seed value when reseeding. If the table is empty, the first identity value (after executing DBCC CHECKIDENT) will equal newreseedvalue. If the table contains data, the next identity value will equal newreseedvalue + the current increment value (the default is 1). This behavior is new to SQL Server 2005 (and remains in 2008). SQL Server 2000 always increments the seed value.
WITH NO INFOMSGS
Suppresses all informational messages.

Method 2
http://msdn.microsoft.com/en-us/library/aa933196%28SQL.80%29.aspx

<code>IDENTITY_INSERT <tablename> ON<br /><br />explicitly insert a row with a bigger id<br /><br />insert <tablename><br />(99, 'test')  -- current id was 10<br /><br />then go <br /><br /></code><code>IDENTITY_INSERT <tablename> OFF</code><br /><br />and the next insert you make will have an id = 100<br />

~ by shoel on September 22, 2010 .



Leave a Reply