0

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.

  • What is the primary key of this table? – Aaron Bertrand Feb 12 '14 at 20:16
  • The primary key of all of our tables are GUID's. – cbmeeks Feb 12 '14 at 20:24
  • I think you need to show the queries you use and even better the CREATE TABLE statement as well. – ypercubeᵀᴹ Feb 12 '14 at 22:52
2

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.

  • There are definitely times when it will be queried with [TimeSheetDate, UserCustomerKey], [TimeSheetDate], [UserCustomerKey], [UserCustomerKey, TimeSheetDate]. So, basically all four combinations. – cbmeeks Feb 12 '14 at 21:13
  • Then I would test those combinations to see what combination is best for queries and how much overhead the combination adds on insert, updates and deletes. Options are two indexes, one for each column, two indexes with a different column order and so on.. – Matan Yungman Feb 12 '14 at 21:21
  • 1
    One index for each column will not help very much for the combination queries, if you have many customers and many dates (which I assume is the case here). Having two combination indexes is useless (see my answer). Having a date+key index has the advantage than you can direct most inserts to the end of the index, thus avoiding fragmentation, so it would be better than a key+date index. If you have all combinations of queries I would recommend [date+key] and [key] for the customer-only queries. – TToni Feb 12 '14 at 21:57
-1

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.

  • 1
    The (key,date) index will be also helpful is if he has queries like SELECT MAX(TimeSheetDate) FROM x GROUP BY UserCustomerKey – ypercubeᵀᴹ Feb 12 '14 at 22:51
  • @ypercube: That's right. I just wanted to emphasize that the index would be useless as an additional index for combined queries. – TToni Feb 13 '14 at 21:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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