In a few logging tables that are frequently written to, I'd like to be able to store a relative order so that I can union between these tables, and get the order that things actually occurred in.
DateTime2's resolution is lacking. Several rows will get the exact same date, so there is no way to tell which happened first.
Because sorting should work across several tables, sorting by Id is out.
Then I started looking at timestamp. This works for updated dates, but it does not work for created dates, because you can only have one timestamp column per table, and it automatically updates.
This is for Microsoft Sql Server 2008.
You can simulate it with another column typed as
binary(8) (same as
rowversion) and defaulting to
create table TX ( ID int not null, Updated rowversion not null, Created binary(8) not null constraint DF_TX_Created DEFAULT (@@DBTS) ) go insert into TX (ID) values (1),(2) go update TX set ID = 3 where ID = 1 go insert into TX (ID) values (4) go select * from TX
ID Updated Created ----------- ------------------ ------------------ 3 0x00000000000007D3 0x00000000000007D0 2 0x00000000000007D2 0x00000000000007D0 4 0x00000000000007D4 0x00000000000007D3
Created values will always be equal to the last
rowversion value assigned, so they will "lag", in some sense, compared to
Also, multiple inserts from a single statement will receive the same
Created values, whereas
Updated values will always be distinct.