Problem with Autoincrement in SQL

Suppose I have a table like this

ID Name
1  RJ
2  Hello
3  NV 
4  Dere 
5  What

So when I am deleting the fourth entry from the database ( ID=4 and Name= dere ) by using this Sql Statement

Delete from Table where ID="4"

then it deletes the table row but when I see the table it looks to me as

ID Name
1  RJ
2  Hello
3  NV 
5  What

So is there any way that it also updates the auto increment ID automatically like开发者_开发技巧 I want the table to look like this as soon as I delete the table fourth entry

ID Name
1  RJ
2  Hello
3  NV 
4  What

Hope you have got to know my question..!!

No. This would involve updating potentially hundreds of thousands of records. Your code shouldn't require that keys be contiguous. You should never assume anything about the keys.

That's not what IDENTITY/autonumber columns are for.

If you need a dynamic column number (based on what order?), use something dynamic, not a plain old data column.

For example, you could just get rid of that ID column and add a datetime column on CreationDate defaulting to GETDATE()

SELECT *
    ,ROW_NUMBER() OVER (ORDER BY CreationDate) AS ID
FROM tbl

declare @id int
set @id = 4
Delete from Table where ID=@id

Update Table
Set ID = ID - 1
WHERE ID > @ID

BUT DON'T DO THIS

Forgive this, but why? It's just a number, and down the road is is likely you will have other tables that refer to this one by the ID. That's what DB's do best. Don't worry about the numbering.

it is common sql behavior for Identity.