title: Automatically set new seed value on Identity
body: <p>Similar Question: <a href="https://stackoverflow.com/questions/2923855/how-to-automatically-reseed-after-using-identity-insert">How to automatically reseed after using identity_insert?</a></p>
  
  <p>I have a stored procedure that runs once a day, part of this procedure is to delete ~million rows from a table and re-insert a similar number of rows (there are millions of other rows that are not affected), once it finishes it rebuilds all the indexes on the table ready for use.</p>
  
  <p>Performance wise, i have no problem, the only annoyance is the excessive size of the Primary Key - the primary key is an <code>IDENTITY</code> <code>INT</code> - obviously, deleting a million rows then re-inserting leaves me with a ~1000000 gap every day.</p>
  
  <p>So, i want to reseed the ID column after the <code>DELETE</code> prior to the <code>INSERT</code>.</p>
  
  <p>I know that i can use <code>CHECKIDENT</code> to reseed the identity column - but only if the new seed value is known before calling <code>CHECKIDENT</code>- i.e. using <code>MAX()</code> immediately prior to the <code>CHECKIDENT</code>.</p>
  
  <p>My table is a very high traffic table, whilst it's unlikely, there is a small chance that other inserts / deletes may be attempted during this process, therefor i am a bit worried about resorting to MAX()+1 to determine my new seed value (i.e. a row being inserted from elsewhere after the MAX() was performed but prior to the completion of CHECKIDENT - wich would result in an error), eg:</p>
  
  
  
  <pre><code>DECLARE @NewSeed INT 
  SELECT  @NewSeed = ISNULL(MAX(IdentityColumn), 0)
  FROM    tbl_Whatever 
  DBCC CHECKIDENT('tbl_Whatever', RESEED, @NewSeed) 
  </code></pre>
  
  <p>My Question: is there a slicker way to perform a reseed based on the current max value of the Identity column? - I'm assuming the answer is no, but just thought i'd ask.</p>
  
  <p>SQL Version is 2008 R2</p>