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.
CREATE TABLE
statement as well.