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.

  • 2
    A trigger + sequence? – McNets Nov 8 '19 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 '19 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 '19 at 11:50
  • @AMtwo, yes. We need to process up to 1500 rows in second. – Ruslan K. Nov 8 '19 at 12:16
  • Processing 1500 rows/second doesn't preclude managing status for each item in the queue... – AMtwo Nov 8 '19 at 13:27

Your Answer

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.