How should I structure my time based index?

Question

So I have this table in SQL Server 2008. Let's just say it's called TimeSheets.

Now, that table has two columns I am concerned with.

TimeSheetDate and UserCustomerKey.

TimeSheetDate is a DateTime column and UserCustomerKey is a GUID.

UserCustomerKey is the primary ID of a join table for Users and Customers.

Typically, the primary application is going to query TimeSheets by the TimeSheetDate and UserCustomerKey. And since we're dealing with time sheets, we usually need the most recent time sheets for reports.

So I created an index like:

TimeSheetDate (DESC)
UserCustomerKey (ASC)

Since we normally care about descending time sheet searches (most recent time sheets from today), this index makes sense.

However, there may be applications that JUST search by TimeSheetDate and not care about the UserCustomerKey.

1) So, do I need to also create a single index for TimeSheetDate or will the above index also include it?

2) Do I need to create another index that has UserCustomerKey BEFORE TimeSheetDate or does it matter?

I don't want to waste resources if I don't have to.

Thanks.

Answer 1

If a single column is used by a query for filtering, it can use an index if the that column is the left-most column in the index.

So assuming the TimeSheetDate is the left-most column in your index, you don't need to create another index for it.

Whether or not you should create another index in a reversed order is dependent on the selectivity of the UserCustomerKey column and whether queries filter using it.

Answer 2

First of all, since I assume the timesheet dates are rising, not falling, you should replace the

TimeSheetDate (DESC)
UserCustomerKey (ASC)

with

TimeSheetDate (ASC) 
UserCustomerKey (ASC)

This is important! Through the DESC you basically force SQL Server to insert new entries at the start of the index. That is bad because it leads to continuous page splits and a heavily fragmented index. Even if you rebuild the index and it is perfectly unfragmented after that, you have absolutely no performance gain from having the youngest dates at the beginning of the index. That's just not how a B-Tree index works. The ASC and DESC options are there so you can try to influence the index in a way that the inserts happen at the end (not the start!) because that's what those indexes are optimized for.

The date+key index can be used just fine with date-only queries, but not for key-only queries.

An index containing only the date would gain you a minimal advantage for the date-only queries. I assume that the date/key index is made UNIQUE, so if you have only the date in it (not unique), SQL Server adds a 4 byte uniquiefier to each entry. This is still smaller than the 16 byte guid for the customer key, so you have the advantage of a smaller index for these queries. But it costs you space for another index, including the associated cost on insert/update/delete, buffer cache, I/O, statistics updates and so on, so I wouldn't recommend it.

Creating an additional index in reverse order (key+date) doesn't make any sense at all. It has the same size and the same depth as the original index (after defragmentation, otherwise it would be even larger), so the number of pages you have to read to get to a specific entry (if you give both date and key in the where clause) is exactly the same. If you have key-only queries, you would need an additional index containing only the customer key.

Edit: There is one scenario where a [key+date] index would actually make sense: If you have frequent queries like

SELECT ... FROM x WHERE UserCustomerKey='...' ORDER BY TimeSheetDate

because in that case the index already provides the correct sort order for the query. If such queries are relatively seldom, it's not worth the additional index space to include the date column. BTW in that case it doesn't matter if you have asc or desc in the index or the order by. SQL Server can read an index forwards or backwards, according to need, because the leaf pages form a double linked list.