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.