title: Reset A Sequence
body: <p>I've read several posts about the <a href="https://stackoverflow.com/questions/14162648/sql-server-2012-column-identity-increment-jumping-from-6-to-1000-on-7th-entry">TSQL Identity Bug</a> and have been playing around with using <code>SEQUENCE</code>.  However, I'm curious about resetting the <code>SEQUENCE</code> on the <code>ID</code> value in a table.  For an example:</p>
  
  <pre><code>CREATE SEQUENCE Inc
      AS INT
      START WITH 1
      INCREMENT BY 1
      CYCLE 
      CACHE 
  
  -- Quick ability to redo everything if needed:
  -- DROP SEQUENCE Inc
  
  -- Our table grabs the next sequence for our ID field:
  CREATE TABLE SequenceID(
      NewIDField INT DEFAULT NEXT VALUE FOR Inc,
      Name VARCHAR(100)
  )
  
  INSERT INTO SequenceID (Name)
  VALUES ('John')
      , ('Tiffany')
      , ('Bob')
      , ('Jessica')
  
  SELECT *
  FROM SequenceID
  
  -- We remove Bob:
  DELETE FROM SequenceID
  WHERE NewIDField = 3
  
  -- ID value 3 is gone; it moves from 1 to 2 to 4
  SELECT *
  FROM SequenceID
  
  INSERT INTO SequenceID (Name)
  VALUES ('David')
      , ('Rosa')
      , ('Samuel')
  
  -- ID 3 doesn't exist because the SEQUENCE grabs the next value from 4
  SELECT *
  FROM SequenceID
  
  -- Let's just reset our ID
  ;WITH ResetIt AS(
      SELECT ROW_NUMBER() OVER (ORDER BY NewIDField) AS ID
          , NewIDField AS ExistingID
          , Name
      FROM SequenceID
  )
  UPDATE SequenceID
  SET NewIDField = ResetIt.ID
  FROM ResetIt
  WHERE SequenceID.NewIDField = ResetIt.ExistingID
  
  -- Yay!
  SELECT *
  FROM SequenceID
  
  INSERT INTO SequenceID (Name)
  VALUES ('Sarah')
  
  -- Oh Sarah, tsk tsk.
  SELECT *
  FROM SequenceID
  
  DROP TABLE SequenceID
  </code></pre>
  
  <p>Is there a way to automatically perform this with SEQUENCE where we can determine the last value and begin there (similar to a RESEED), as even with <code>IDENTITY</code>, if we remove a value, we still must <code>RESEED</code>, see:</p>
  
  <pre><code>CREATE TABLE IDID(
      ID INT IDENTITY(1,1),
      I INT
  )
  
  INSERT INTO IDID (I)
  VALUES (1),(2),(3),(4)
  
  SELECT *
  FROM IDID
  
  DELETE FROM IDID
  WHERE ID = 3
  
  INSERT INTO IDID (I)
  VALUES (5),(6),(7)
  
  SELECT *
  FROM IDID
  
  DROP TABLE IDID
  </code></pre>