SQL Server Performance Forum – Threads Archive

Doubt regarding Composite Index

Even if the WHERE clause in a query does not specify the first column of an available index (which normally disqualifies the index from being used), if the index is a composite index and contains all of the columns referenced in the query, the query optimizer can still use the index, because the index is a covering index. This i what i read.
I tried the following thing but still it did not use the index..can some one please tell me the reason for it create table TRIAL
(
SMID number,
SLKEYSTOREFILEPATH varchar(255),
SLKEYSTOREFILEPATH1 varchar(255),
SLKEYSTOREFILEPATH2 varchar(255),
SLKEYSTOREFILEPATH3 varchar(255),
SLKEYSTOREFILEPATH4 varchar(255),
SLKEYSTOREFILEPATH5 varchar(255),
primary key (SMID)
)
; create index
TRIAL_idx
on
TRIAL
(
SMID,SLKEYSTOREFILEPATH,SLKEYSTOREFILEPATH1,SLKEYSTOREFILEPATH2,SLKEYSTOREFILEPATH3,SLKEYSTOREFILEPATH4,SLKEYSTOREFILEPATH5
); EXPLAIN PLAN SET statement_id = ‘example1’ FOR
select smid,SLKEYSTOREFILEPATH,SLKEYSTOREFILEPATH1,SLKEYSTOREFILEPATH2,SLKEYSTOREFILEPATH3,SLKEYSTOREFILEPATH4,SLKEYSTOREFILEPATH5
from TRIAL
where SLKEYSTOREFILEPATH=12 and SLKEYSTOREFILEPATH1=12 and SLKEYSTOREFILEPATH2=12
and SLKEYSTOREFILEPATH3=12 and SLKEYSTOREFILEPATH4=12
and SLKEYSTOREFILEPATH5=12
;

---

first, creating indexes on very wide columns is very expensive
if the average width of each of the varchar columns is less than 20, then you are ok next, your index duplicates the primary key finally,
an index seek, seek being the operative word,
requires that the SARG be the first key of the index
if not, then you end up with a index scan, which could be less expensive than a table scan
depending on the index and table width please learn more about execution plans before theorizing on what people say without the underlying explanation oh yeah,
your index is bigger than the table, so the table scan is used
check sp_spaceused on your table

---

I think if you modify your index and put any one of the columns in the WHERE condition as the left most column, the index would be used. ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

---

point well noted joechang..any ways it was just an example
please clarify my following doubts
1)an index seek, seek being the operative word,
requires that the SARG be the first key of the index–> i did not get this 2)your index is bigger than the table–> i did not get this also Kindly help

---

]]>