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
精彩评论