That would not be a good design if you ask me. Although that might be possible, it would cause an overhead on your processing, because each delete you make will update records below it. What if you have a million records, and a user decides to delete record with serial number of 10, what do you think will happen? I suggest you let your database handle the auto numbering, instead of passing it from your app, to your database.
[Update]
Here's an example that you might want to look at.
CREATE PROCEDURE [dbo].[YourSP](@Serialnum INT)
AS
BEGIN
DELETE FROM YourTable WHERE Serial_num = @Serialnum
UPDATE YourTable SET Serial_num = Serialnum - 1 WHERE Serial_num > @Serialnum
END
Submit an article or tip