I am new to table indexes and how they are used in queries. I've read tons of articles and most of them talk about how to set them, but very few talk about how they are used in queries. I need help understanding these questions:
- What is great resource that not only talks about creating them but also how they are used?
- Do all the fields listed in a table index need to be included in the where and/or group by & order by statements? I have a table with 5 fields listed in the index and I may only use 3 or 4 of those fields in a query. Will the index still be used/applied?
Any assistance is greatly appreciated.
" I have a table with 5 fields listed in the index and I may only use 3 or 4 of those fields in a query."
The index will not help optimize the query if your index has 5 fields and use only 3 or 4 fields in your where clause.You should create another non clustered index depending on your queries.
Some DBA's doesn't create table index specially if the involved table has several columns and querying with different queries having different columns included in the WHERE Clause.
Sample:
Table Name: Table1
Col 1 Col 2 Col 3 Col 4
Query 1:
Select * from Table1
where col1=1 and col2=1 and col3=1 and col4=1
--On this query, this will be optimized if we create index involving all the columns in the
WHERE Clause (col1,col2,col3,col4)
Query 2:
Select * from Table1
where col1=1 and col2=1
--On this query, this will be optimized if we create index involving all the columns in the
WHERE Clause (col1,col2)
Basically, you are also creating an index for those frequently use query.
I hope this helps. Sorry as it is hard for me to explain through this 
@maeztro45 - Thanks. This is very helpful.
@Ifor - Thanks for the book suggestion. It looks like this book could answer some of my follow-up questions.