How to automatically set the SNo of the row in MS SQL Server 2005?

I have a couple of rows in a database table (lets call it Customer). Each row is numbered by SNo, which gets automatically incremented by the identity property inherent in MS SQLServer. But when I delete a particular row that particular row number is left blank, but I want the table to auto correct itself.

To give you a example:

I have a sample Customer Table with following rows:

SNo CustomerName Age

1   Dani         28
2   Alex         29
3   Duran        21
4   Mark         24

And suppose I del开发者_StackOverflow中文版ete 3rd row the table looks like this:

SNo CustomerName Age

1   Dani         28
2   Alex         29
4   Mark         24

But I want the table to look like this:

SNo CustomerName Age

1   Dani         28
2   Alex         29
3   Mark         24

How can I achieve that?

Please help me out

Thanks in anticipation

As has been pointed out doing that would break anything in a relationship with SNo, however if your doing this because you need ordinal numbers in you presentation layer for example, you can pull off a [1..n] row number with;

SELECT ROW_NUMBER() OVER(ORDER BY SNo ASC), SNo, CustomerName, Age FROM Customer

Obviously in this case the row number is just an incrementing number, its meaningless in relation to anything else.

I don't think you want to do that. Imagine the scenario where you have another table CustomerOrder that stores all customer orders. The structure for that table might look something like this:

CustomerOrder
-------------
OrderID     INT
SNo         INT
OrderDate   DATETIME
...

In this case, the SNo field is a foreign key into the CustomerOrder table, and we use it to relate orders to a customer. If you delete a record from your Customer table (say with SNo = 1), are you going to go back and update the SNo values in the entire CustomerOrder table? It's best to just let the ID's autoincrement and not worry about spaces in the IDs due to deletions.

Why not create a view?

CREATE VIEW <ViewName>
AS
SELECT     
ROW_NUMBER() OVER(ORDER BY SNo ASC) AS SNo
,CustomerName
,Age
FROM Customers

GO

Then access the data in customers table by selecting from the view.

Of course the SNo shown by the view has no meaning in the context of relationships, but the data returned will look exactly like you want it to look.

Using transactions when inserting records in the Database with C#

You have to use DBCC CHECKIDENT(table_name, RESEED, next_val_less_1);

As have been pointed out in other answers, this is a bad idea, and if the reason is for a presentation there are other solutions.

-- Add data to temp table
select SNo, CustomerName, Age
into #Customer
from Customer

-- Truncate Customer
-- Resets identity to seed value for column
truncate table Customer

-- Add rows back to Customer
insert into Customer(CustomerName, Age)
select CustomerName, Age
from #Customer
order by SNo

drop table #Customer