title: Avoiding gaps in an identity column
body: <p>I have a table in <code>MS SQL SERVER 2008</code> and I have set its <code>primary key</code> to increment automatically but if I delete any row from this table and insert some new rows in the table it starts from the next identity value which created gap in the identity value. My program requires all the identities or keys to be in sequence.</p>
  
  <p>Like:
  Assignment Table has total 16 rows with sequence identities(1-16) but if I delete a value at 16th position </p>
  
  <pre><code>Delete From Assignment Where assignment_id=16; 
  </code></pre>
  
  <p>and after this operation when I insert a new row </p>
  
  <pre><code>Insert  into Assignment(assignment_title)Values('myassignment');
  </code></pre>
  
  <p>Rather than assigning 16 as a primary key to this new value it assigns 17.</p>
  
  <p>How can I solve this Problem ?</p>