Does column order really matter for Query Optimizer to pick index.
Case 1:
Say my CUSTOMER table has one composite index containing FirstName and LastName. FirstName exists prior than LastName. Does the column, FirstName and LastName, order matter to have Query Optimizer to utilize the index when I write WHERE clause in a SELECT statement?
Statement 1:
SELECT * FROM CUSTOMER
WHERE FirstName = 'John' and LastName ='Smith'
Statement 2:
SELECT * FROM CUSTOMER
WHERE LastName ='Smith' and FirstName = 'John'
Will both statement 1 and 2 use the composite index or only statement 1?
Case 2:
Say my CUSTOMER has two single-column indexes. One index is on column FirstName. Another is on column LastName.For statement 1 and 2 above, which index will be picked by Query Optimizer or both? How does QO pick for index?
I read couple book and some books say column order matter but some say no. Which one should I go with? I'm kind of confused.
Column order is not important but SQL Server 2005 comes with something that gives you the benefit of a composite yet it is not a composite, it is called index column include so the lastname will be covered in your query. Indexes are part of the physical design so the RDMS vendors owns and improves on it. Try the link below for details. Post again if you still have questions. Hope this helps.
http://msdn2.microsoft.com/en-us/library/ms190806.aspx
|||Case 1:
Doesn't matter.
Case 2:
Doesn't matter.
Case 3:
SELECT FirstName FROM CUSTOMER WHERE LastName='Smith'
Now, it matters. The index is bad for this query. Create a new index on LastName,FirstName and this will run much faster.
The analogy is quite simple. Look at a telephone book. It's arranged by lastname,firstname. If I ask you to find me John Smith in the phone book, it'll take you a few seconds. If I ask you to find Smith, John in the phonebook, it'll take you a few seconds. If I ask you to find me all the first names of people whose last name is Smith (it'll take a little bit, but you can do it). Now what if I ask you to tell me the last name of every John? Uh...
