Reset The Primary Key Of A Table

Lets say you've got 28 rows in your table but because you've been doing a lot of inserts and rollbacks the next key to be inserted is much higher than your highest primary key. This would be a problem if you've done a lot of inserts and have maybe reached the maximum value of the int type being used as the primary key field type.

You would want to reset the index (as all primary keys are special indices) like this:

DBCC CHECKIDENT ({tablename}, reseed, {number})

Don't make my initial mistake and think that the 'reseed' in the middle there is a place holder for a value - it isn't. Just leave it as is; it's a command. The operation is called reseeding, not resetting - but that will be all academic to most of us.

References:

• Insert:
  http://msdn.microsoft.com/en-us/library/ms174335.aspx
  http://www.google.co.uk/search?sourceid=chrome&ie=UTF-8&q=t-sql+insert
• Rollback:
  http://msdn.microsoft.com/en-us/library/ms181299.aspx
  http://www.google.co.uk/search?hl=en&q=t-sql+rollback&aq=f&aqi=g2g-c2g1&aql=&oq=&gs_rfai=
• DBCC CHECKIDENT:
  http://msdn.microsoft.com/en-us/library/ms176057.aspx
  http://www.google.co.uk/search?sourceid=chrome&ie=UTF-8&q=DBCC+CHECKIDENT
• Data type maximum values:
  http://www.tsqltutorials.com/datatypes.php
  http://www.google.co.uk/search?sourceid=chrome&ie=UTF-8&q=t-sql+int+maximum+value
• Pinal Dave:
  http://blog.sqlauthority.com/2007/03/15/sql-server-dbcc-reseed-table-identity-value-reset-table-identity/