For example, I have a table

create table Queue (OrderNum int, Data varchar(1000)) --int - may be a different type

and many concurrent inserts into this table. Is there is a way to fill the OrderNum column with data that will show the order in which a row was committed in this table? Not inserted!

I want to realize many Queue readers, which will periodically select new rows where OrderNum > (max(OrderNum) of the previous select from Queue). In this case I can't use either identity or rowversion, because they are generated in the INSERT statement and the reader can select a committed row with OrderNum = 2 while a row with OrderNum = 1 still isn't committed. So we would have a gap, or data loss for readers.

New contributor
Ruslan K. is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • 2
    A trigger + sequence? – McNets Nov 8 at 7:56
  • @McNets But triggers work in transactions and sequence works same as identity and rowversion - generates during insert + trigger executing, and may finally committed in "wrong" order. Isn't it? – Ruslan K. Nov 8 at 11:46
  • Generally, queue tables will either have a status, or remove records after they are processed, so that your query is more like SELECT TOP 1 ... WHERE Status = 'new' ORDER BY OrderNum without having to actually filter on the order number to get new rows. Is there a reason you couldn't use a status column? – AMtwo Nov 8 at 11:50
  • @AMtwo, yes. We need to process up to 1500 rows in second. – Ruslan K. Nov 8 at 12:16
  • Processing 1500 rows/second doesn't preclude managing status for each item in the queue... – AMtwo Nov 8 at 13:27

Your Answer

Ruslan K. is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.