How can I assign a number to each row in a table representing the record number?
database-design, sql-server, sql-server-express
Solution
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.
Problem
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 ```