I have a table like this:

 CreateDateTime datetime2 NOT NULL,
 --other columns

How can I make sure that ID and 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.

  • 4
    Can you explain why "I want to query a date range by filtering on the ID column"? If you put a proper index on the datetime column, you should filter on the datetime column, period. Aug 9, 2012 at 12:10
  • Good question. There are situations where I want to scan the data for big periods of time, like hours. This is a high-volume logging table. Neither do I want a loop join with 1m rows, nor do I want a table scan, nor do I want a covering index starting with CreateDateTime because of space usage.
    – usr
    Aug 9, 2012 at 12:35
  • Your request is relatively easy to accomplish with a few constraints, but it would be rather involved, will slow down modifications, and your concurrent multi-row inserts might start failing. I would go for @ypercube's suggestion.
    – A-K
    Aug 9, 2012 at 13:17

2 Answers 2


I don't think it's easy to make such a guarantee and even if you do manage to make such an assertion actually work, it may slow down your Inserts into the table.

Since this is a performance issue for the ordering queries, here's a suggestion:

Make (CreateDateTime, ID) or (CreateDateTime) the clustered index of the table, instead of the (usual) clustered index on (ID) alone.

Then you can run queries with ORDER BY CreateDateTime and they will use the clustered index.

  • 1
    That suggestion would work I guess. It will increase the size of all index rows by 8 bytes though. I'm still hoping for a better solution, although this would be ok.
    – usr
    Aug 9, 2012 at 12:37
  • 2
    @usr Since both the fields are in your clustered index anyways, it won't add any space at all to your table...
    – JNK
    Aug 9, 2012 at 17:41
  • It would add that space to all non-clustered indexes. With 3 NC-indexes that's 24 bytes per row.
    – usr
    Aug 9, 2012 at 18:01
  • @usr: It was not entirely clear what you meant with the first comment. Aug 9, 2012 at 18:16
  • I don't think you can guarantee uniqueness with just CreateDateTime. Clocks can be reset forward and backwards. Events can happen so quickly they appear to happen at the same time. (I almost convinced myself I could make a datetime2 column the PK of my log table, but you really need to include an IDENTITY column to ensure uniqueness).
    – ligos
    Aug 14, 2012 at 5:57

The ID column is an identity column with a seed of 1 and an increment of 1, which means that it is always guaranteed to be incremental.

If CreateDateTime is only populated when a new row is created (and never updated), you could put a default of GETDATE() on it and it would also always be incremental. On the other hand, if that value can be updated or it won't always be the current date and time, you need to create a trigger to ensure that the updated or inserted row is valid (by comparing it to the previous value in the table).

Without a trigger, this becomes more difficult as you would have to use a check constraint which would force you to check all the rows within the table which would kill the performance on insert and updates.

  • 2
    I'd be careful about guarantees. The IDENTITY property alone does not guarantee uniqueness or incremental. I can easily stuff a duplicate value in there (or fill a gap) using SET IDENTITY_INSERT ON. Aug 9, 2012 at 12:09
  • I'm not so worried about filling the table in a correctly sorted way - I want to catch bugs. I want to error out if my application for whatever reason inserts a wrong value. For example someone might tun the clock back. I guess a UDF in a check constraint would work although that is hardly any better than a trigger.
    – usr
    Aug 9, 2012 at 12:25
  • Good point @Aaron Bertrand, I was assuming that access would be limited in order to avoid that sort of operations. I think that if you want to also stop that on a structural level, you need to add triggers or check constraints. Either way, it's going to be expensive.
    – lurkerguy
    Aug 9, 2012 at 12:26
  • 1
    Have you considered multi-row inserts from multiple connections?
    – A-K
    Aug 9, 2012 at 16:13
  • Also be careful your GETDATE() doesn't jump backwards or forwards in time (eg: because someone adjusts the server clock, or daylight saving).
    – ligos
    Aug 14, 2012 at 5:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.