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 />
