sqlserver Indexes for multiple datetime columns

Hi there, I have a table that has in its structure, start_time_stamp (datetime) and end_time_stamp (datetime)

I have created an index with both of these columns as key columns, and a few other fields that we use in our queries in the include columns.

However i've noticed that only one field gets used in the seek predicate, and the other is just used to filter those results.

I would have thought they would both be used in the seek predicate and I cant for the life of me figure out why they arent?

An index is a binary tree that is traversed to find the key values. If you have a compound key, then the tree will be traversed to the first match of the leading column before any following columns of the key can be inspected (which would be a further trip down the index tree).

The type of query depends whether the seek can go all the way to the full key or whether the trailing columns have to be scanned.

For example, if you have a query like this:

SELECT col_a
FROM   my_table
WHERE  start_date =  @order_date
AND    end_date   <= @delivery_datethen the compound index will seek the first row where start_date = @order_date and the end_date <= @delivery_date. It will then scan the rest of the branch where start_date = @order_date until the "smallest" end_date is reached.

But the example is quite an uncommon scenario if the compound index represents start and end dates.

For example, consider this query:

SELECT col_a
FROM   my_table
WHERE  @my_date BETWEEN start_date AND end_dateNow assume that you have a compound index on my_table(start_date, end_date). In this example, the index can be seeked to locate the first start_date >= @my_date. But after that, it has to scan the rest of the index all the way to the "highest" start_date, because there might be qualifying rows. This disqualifies any seek on the end_date.

If your table uses non-overlapping date ranges, and all end_date >= start_date, then you only need to index the start_date, and you can rewrite the query to:

SELECT col_a
FROM   my_table
WHERE  start_date =
       (
           SELECT MIN(start_date)
           FROM   my_table
           WHERE  start_date >= @my_date
       )
AND    @my_date <= end_dateIf you have more complex queries, such as queries for overlapping date ranges, then a simple rewrite won't do. For example, a query like this:

SELECT col_a
FROM   my_table
WHERE  start_date <= @end_date
AND    end_date   >= @start_datecannot make efficient use of an index. If the query returns few rows, and the rows are very large, then an index on (start_date) is still useful, but after a seek on the leading column,  the rest of the index will be scanned. Adding end_date as a key column does not add any value; you can just as well add it as an included column.

If you have queries like that, and you want to optimize them, you can have a look at the Date Range Scans article.