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

[SQL Server Sequence Number](https://learn.microsoft.com/en-us/archive/blogs/sqlcat/sql-server-sequence-number)

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 second 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 second select then you don't need the transaction.

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