T-SQL Re-Assigning Order Numbering

Say I have a table with a field called "ordernum" that denotes the order of a given set of rows. Now imagine that I delete one of these rows. What type of query would work best for re-assigning the order numbers so that they remain sequential?

Here's an example:

id 开发者_JS百科 group_id name ordernum active

---------------------------------------------------

0______________0_______________'Name1'__________5__________true

1_______________0_______________'Name2'__________4__________true

2_______________0_______________'Name3'__________3__________true

3_______________1_______________'Name4'__________2__________true

4_______________1_______________'Name5'_________1__________true

5_______________1_______________'Name6'_________NULL__________false

Now if I deleted the column with id='4' how would I reset the values in the 'ordernum' field for that specific group? Is this even possible?

Or if I added a new row. (The first time the rows are created they are sorted by date, but then the user has the option to set the order himself.)

In my table design I have a column 'active' boolean. If 'active' is set to false, then 'ordernum' is set to NULL. Otherwise it should be given an order number.

You could use a trigger for the delete:

update tbl
set ordernum = ordernum - 1
from deleted d
where d.active = 1
and group_id = d.group_id
and ordernum > d.ordernum

Adding a new row shouldn't be a big deal either, though I wouldn't necessarily use a trigger for the insert.