I REALLY hope I can talk you and the people you work for out of this idea. At the root of the problem, it's a violation of normal form because you have a column that contains two distinct values... the date of insertion and a very dependent daily sequence number. You also have a growth problem. Yeah... I know you'll say that you'll never go over 99,999 rows in a day but a lot of consultants make a lot of money off of people who thought the same thing.

And, let me ask... what do you want to do if someone deletes a row?

I implore you not to use this column for anything except display purposes which also means don't store it in your database. Calculate it at display time like ChrisM did with the understanding that if someone does a delete, the sequence numbers will change.

If I can't talk you out of it, then wait until the end of each day and calculate the column once for the previous day using code like ChrisM used. Dwain's code is pretty decent and there's a low probability of someone getting in out of sequence but that's not quite the same as saying that it's guaranteed to never dupe a row during the change at midnight.