Is there a difference between id,date or date,id index in sql server

Question

I'm creating an index in sql server 2005 and the discussion with a coworker is if it makes a difference between index key columns being id and date vs date then id.

Is there a fundamental difference in the way the index would be created in either scenario?

Would it make a difference in other versions of SQL server?

Thanks

Answer 1

Yes, definitely. Does anyone ever query the table for JUST date or JUST id? An index of date,id can be used to look up just date, but not just id, and vice-versa

Using date,id:

Jan 1     4  
Jan 1     7  
Jan 2     6  
Jan 2     9  
Jan 2     33  
Jan 3     23  
Jan 4     1

Using id,date:

1     Jan 4  
4     Jan 1  
6     Jan 2  
7     Jan 1  
9     Jan 2  
23    Jan 3  
33    Jan 2

If your WHERE clause or a JOIN in your query is using both date and id, then either index is fine. But you can see that if you're doing a lookup just by date, the first index is useful for that, but the second one is totally random.

In a more general sense, an index on A, B, C, D is going to be useful for queries on A,B,C,D, OR A,B,C OR A,B OR just A.

Answer 2

The order of columns does matter when it comes to indexes. Whether or not it'll matter in your case depends.

Let me explain.

Let's say you have a person table, with first, last, and middle name.

So you create this index, with the columns in the following order:

FirstName, MiddleName, LastName

Now, let's say you now do a query using a WHERE on all of those columns. It'll use the entire index.

But, let's say you only query on first and last name, what happens now is that while it will still use the query, it will grab the range of the index that has the same first name as your WHERE-clause, then scan those, retrieving those that have a matching last name. Note, it will scan all the rows with the same first name.

However, if you had rearranged the index, like this:

FirstName, LastName, MiddleName

Then the above query would grab the range of the index that has the same first and last name, and retrieve those.

It's easier to grasp if you look at it in another way.

The phone book is sorted by last name, then firstname and middle name. If you had put middle name between first and last name, and sorted, then people with the same first and last name would seemingly be all over the place, simply because you sorted on middle name before first name.

Hence, if you're looking for my name, which is "Lasse Vågsæther Karlsen", you'll find all the Karlsen-people, we would be located in a sequential list in the phone book, but my name would be seemingly randomly placed, simply because the list would then be sorted by Vågsæther.

So an index can be used, even if the query doesn't use all the columns in the index, but the quick lookup-features only work as long as the columns are listed at the front of the index. Once you skip a column, some kind of scan takes place.

Now, if all your queries use both id and date, it won't matter much, but if all the queries include date, and only some of them contain an id, then I'd put date first, and id second, this way the index would be used in more cases.

Answer 3

Yes, it does matter. Suppose you create an index on columns (A, B). You can do a SELECT with a WHERE clause including both columns and the index can be used. The index will also be used if you do a SELECT with a WHERE that only includes column A. But if you do a SELECT with a WHERE that only includes column B, the index can't be used.

See here for more info.