问题描述:

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.

Any suggestions?

网友答案:

You can simulate it with another column typed as binary(8) (same as rowversion) and defaulting to @@DBTS:

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

Result:

ID          Updated            Created
----------- ------------------ ------------------
3           0x00000000000007D3 0x00000000000007D0
2           0x00000000000007D2 0x00000000000007D0
4           0x00000000000007D4 0x00000000000007D3

Notes:

The Created values will always be equal to the last rowversion value assigned, so they will "lag", in some sense, compared to Updated values.

Also, multiple inserts from a single statement will receive the same Created values, whereas Updated values will always be distinct.

相关阅读:
Top