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
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.