title: How can I assign a number to each row in a table representing the record number?
body: <p>How can I show the number of rows in a table in a way that when a new record is added the number representing the row goes higher and when a record is deleted the number gets updated accordingly?<br>
  To be more clear,suppose I have a simple table like this :</p>
  
  <blockquote>
    <p>ID int (primary key) Name varchar(5)</p>
  </blockquote>
  
  <p>The ID is set to get incremented by itself (using identity specification) so it can't represent the number of row(record) since if I have for example 3 records as:</p>
  
  <blockquote>
    <p>ID   NAME<br>
     1   Alex<br>
     2   Scott<br>
     3   Sara</p>
  </blockquote>
  
  <p>and I delete Alex and Scott and add a new record it will be: </p>
  
  <pre><code>3 Sara
  4 Mina 
  </code></pre>
  
  <p>So basically I'm looking for a sql-side solution for doing this so that I don't change anything else in the source code in multiple places.</p>
  
  <p>I tried to write something to get the job done but it failes. Here  it is :</p>
  
  <pre><code>SELECT        COUNT(*) AS [row number],Name
  FROM          dbo.Test
  GROUP BY ID, Name
  HAVING        (ID = ID)
  </code></pre>
  
  <p>This shows as:</p>
  
  <pre><code>row number            Name
  1                     Alex
  1                     Scott
  1                     Sara
  </code></pre>
  
  <p>while I want it to get shown as:</p>
  
  <pre><code>row number            Name
  1                     Alex
  2                     Scott
  3                     Sara
  </code></pre>