Making a row delete itself at a certain time?

Is there any possible way to make a row delete itself at a certain date and time? I am tring to make an "On_Sale" table and perticular items(rows) must expire at a certain Date and time. I have an idea to delete a row when it expires but it will cause an extra burden on my application. So is it possible to have SQL responsible for deleting a row at a certain time and date?

Also, I have another question:
when I asign a PK Identity to a certain column is there a way I can enforce consecutive order of PK values? For example if I delete a row and the PK value was 5 it will reoder the whole table so all PK values are in consecutive order:

PK----Item----price
1----XYZ1----$$$$
2----XYZ2----$$$$
3----XYZ3----$$$$

when I delete PK 2 this is what happens

PK----Item----price
1----XYZ1----$$$$
3----XYZ3----$$$$

the PKs are not in consecutive order

What I want is this to happen: when PK 2 is deleted I need it to show as follows

PK----Item----price
1----XYZ1----$$$$
2----XYZ3----$$$$

The PKs stay in consecutive order.

See how the PK are still in consecutive order?I am tring to make an "On_Sale" table and perticular items(rows) must expire at a certain Date and time. I have an idea to delete a row when it expires but it will cause an extra burden on my application.Why delete it at all? Save the expiration date within your row, and then create a view on that table with something like WHERE expiration_date <= current_date. Then make your application use the view to retrieve the data (or simply add the condition to your application logic), thus it will never see expired rows. If you put an index on that column the retrieval should be quite fast.

If you are concerned about space, than run a batch job every night that deletes the expired rows.|||you could create an agent job that would delete the rows also.