sql - Using (UPDLOCK, READPAST) and ORDER BY - Not Working? -

i trying select codes database, not need person coming along , selecting same codes before have chance update them taken. have tried using updlock , solves duplicate sales problem adds problem of deadlocks. using (updlock, readpast), works fine until try use order within statement.

microsoft sql 2005

begin transaction  select top 10 id dbo.codes  (updlock, readpast) itemno = 'type-2' , sold = 0  order cast(nowstamp datetime) asc  commit transaction 

i feel problem going come indexes.

this similar issue order , with(rowlock, updlock, readpast)

the answer describes need rowlock , index. unfortunately, can't create index on column since it's considered non-deterministic because of cast (see first note on http://msdn.microsoft.com/en-us/library/ms189292(v=sql.90).aspx).

you should use convert , specify deterministic style from: http://msdn.microsoft.com/en-us/library/ms187928(v=sql.90).aspx