Adam Machanic (11/2/2010)
david.avraamides (11/1/2010)
I would consider using a sentinel date such as the maximum T-SQL datetime (9999-12-31) for two reasons: 1) your queries become simpler since you don't always need that "or end_date is null"....
Sometimes. Sometimes they become more complex, since you need to add an "...or date is not (sentinel)" to them. The overarching concern in data modelling should be correctness of expression, not how to simplify a query.
How should you index for this query? There are basically two options: (startDate, endDate), and (endDate, startDate).
Without a two-dimensional index such as a Quadtree, you can't do fully-indexed 2D range searches. However, there are a few tricks you can do to get close, by exploiting information the QO doesn't know about your data. I should probably write an article about it, as its a bit more complex than one can easily cover in a post.
Also, you *can* implement a quadtree index in Sql Server, with a little work.