SQL Server indexing includes questions -

i've been trouble shooting bad sql calls in works applications. i've been reading on indexes, tweaking , benchmarking things. here's of rules i've gathered (let me know if sounds right):

• for heavily used quires, boil down query needed , rework where statements use common columns first. make non clustered index on columns used in statement , including on remaining select columns (excluding large columns of course nvarchar(max)).

• if query going return > 20% of entries table contents, it's best table scan , not use index

• order in index matters. have make sure structure statement index built.

now 1 thing i'm having trouble finding info on if query selecting on columns not part of index using statement is? index used , leaf node hits table , looks @ associated row it?

ex: table

id col1 col2 col3  create index my_index on my_table (col1)  select id, col1, col2, col3 my_table col1 >= 3 , col1 <= 6 

is my_index used here? if so, how resolve id, col2, col3? point table rows , pick values?

to answer question, yes, my_index used. , yes, index point table rows , pick id, col2 , col3 values there. index does.

regarding 'rules'

• rule 1 makes sense. except fact not 'include' other columns in index. explained above, index refer table , retrieve row(s) need.

• rule 2, don't understand. create index , sql server decide indices use or not use. don't have worry it.

• rule 3, order not make difference.

i hope helps.