I've had a look and MS themselves offer a solution without locks

http://blogs.msdn.com/b/sqlcat/archive/2006/04/10/sql-server-sequence-number.aspx

This is a simple update with no lock hints, but they say it locks/deadlocks.

Nothing much on SO about this either.

I'd be inclined to add UPDLOCK to your ROWLOCK (as per ["table as a queue" (SO)][1] but without READPAST). This will increase isolation in case a 2nd process starts reading.

However, the fact all your processes want to read/write the same row makes me second guess myself. READPAST allows safe concurrency but in this case it's useless.

Note: you can use the OUTPUT clause instead of a 2nd select then you don't need the transaction.

HTH...

  [1]: https://stackoverflow.com/questions/939831/sql-server-process-queue-race-condition/940001#940001