How can I assign a number to each row in a table representing the record number?

Question

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?
To be more clear,suppose I have a simple table like this :

ID int (primary key) Name varchar(5)

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:

ID NAME
1 Alex
2 Scott
3 Sara

and I delete Alex and Scott and add a new record it will be:

3 Sara
4 Mina 

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.

I tried to write something to get the job done but it failes. Here it is :

SELECT        COUNT(*) AS [row number],Name
FROM          dbo.Test
GROUP BY ID, Name
HAVING        (ID = ID)

This shows as:

row number            Name
1                     Alex
1                     Scott
1                     Sara

while I want it to get shown as:

row number            Name
1                     Alex
2                     Scott
3                     Sara

Answer 1

If you just want the number against the rows while selecting the data and not in the database then you can use this

select row_number() over(order by id) from dbo.Test

This will give the row number n for nth row.

Answer 2

Try

SELECT id, name, ROW_NUMBER() OVER (ORDER BY id) AS RowNumber
FROM   MyTable

Answer 3

What you want is called an auto increment.

For SQL-Server this is achieved by adding the IDENTITY(1,1) attribute to the table definition.

Other RDBMS use a different syntax. Firebird for example has generators, which do the counting. In a BEFORE-INSERT trigger you would assign the ID-field to the current value of the generator (which will be increased automatically).