Auto Number PROBLEM

I have a table namely Product with the following identity modifiers:-
Column- "Num"
Identity-Yes
Identity Seed-1
Identity Increment-1

My problem is everytime i delete a row in the table Product, the Num column after the deleted row will not automatically replace the deleted Num column's data.
Let say:

Num Name
30 Apple
31 Orange
32 Pineapple

If I delete row with Num 31, the Num column after the deleted row will not change to 31 but remain 32.

Num Name
30 Apple
32 Pineapple

What can I do to ensure that the Num column (Num 32) will change to Num 31?

Thank you.what you could do is think very hard about what you seem to want to do

suppose you have an Orders table, which contains customer orders for all products you've sold

if you renumber the products in the products table, you must therefore renumber the Orders table too

this could shut your database down for a few hours

and why? what is the benefit?

autonumbers should never be reassigned

it's not as though you're going to run out of numbers

you could realistically set your identity seed at 183,527,426 and your increment at 3,743 and you would still not run out of numbers for several centuries|||you can add a new column to your table

RowNumber

Id Fruit RowNumber
31 Apple 100
32 Banana 101
33 Pineapple 102

If you add a new fruit
RowNumber = Count(*) of the table

If you delete a fruit
you update RowNumber=RowNumber-1 for all the fruits over the
RowNumber of the deleted fruit

If you delete the Banana

Update Table Fruits Set RowNumber=RowNumber-1
Where RowNumber>(Select RowNumber From Fruits Where Id=32)

Delete Fruits Where Id=32|||But some would say this is not a GREAT thing to do
'cause it's not performant.

But it will work|||i would say it is not a great thing to do because of (a) the impact it has on related tables, and (b) the total absence of ROI (http://searchwebservices.techtarget.com/sDefinition/0,,sid26_gci214270,00.html)

also, not only do you have to update the fruits table, you also have to update the desserts table, the pie table, and any other table which has foreign keys to the fruit table

and i repeat: what exactly is the benefit of doing it?

if it is important that there be no gap in numbers, then i strongly suggest that an autonumber is the wrong design choice|||in my case there is no impact on other tables
because I don't touch the ID number
but the new independant column ROWNUMBER !!!