I have a table like this:
CREATE TABLE T ( ID INT NOT NULL IDENTITY PRIMARY KEY, CreateDateTime datetime2 NOT NULL, --other columns )
How can I make sure that
CreateDateTime are both sorted in the same order? In other words, the following queries would return an identical order:
select * from T order by ID select * from T order by CreateDateTime
I need this guarantee because for performance reasons I want to query a date range by filtering on the ID column. This is possible if the guarantee holds.
I want to fail DML that would violate that property. My intention is to catch bugs breaking this invariant.
Is there a way to do this without using triggers? My hope is that there is a more elegant solution.