Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
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. –  Aaron Bertrand Aug 9 '12 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 '12 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 '12 at 13:17

2 Answers 2

up vote 10 down vote accepted

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.

share|improve this answer
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 '12 at 12:37
    
+1 I would do the same thing. –  A-K Aug 9 '12 at 13:18
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 '12 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 '12 at 18:01
    
@usr: It was not entirely clear what you meant with the first comment. –  ypercube Aug 9 '12 at 18:16

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.

share|improve this answer
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. –  Aaron Bertrand Aug 9 '12 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 '12 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 '12 at 12:26
1  
Have you considered multi-row inserts from multiple connections? –  A-K Aug 9 '12 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 '12 at 5:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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