Confusion !!!!!!!!!!!........Index Seek And Index Scan

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  September 24, 2009 at 5:00 am

  #226541

  Hi.

  See the below script , and results from Execution Plan

  create table t (id int,ch char,na varchar(20),flag CHAR(2))

  insert into t values (2,'A','jack','Y')

  insert into t values (5,'b','amy','N')

  insert into t values (1,'$','adams','N')

  insert into t values (3,'*','anna','Y')

  insert into t values (7,'@','rose','N')

  insert into t values (4,'&','smith','Y')

  insert into t values (6,'!','sue','Y')

  Create nonclustered index nc_t on t(id,ch,na)

  drop table t

  ----------------------------**************------------------------

  1. select na from t where ch = '!'

  Result :index scan

  2. select na from t where id = 6

  Result :index seek

  3. select na from t where ch = '!' and id = 6

  Result :index seek

  4. select na from t where flag = 'Y' and id = 6 and ch = '!'

  Result :table scan

  Question :

  1) In 1 and 2 , why we have different results ?

  2) In 3, we haven't included "na" colun which is also a part of index, but why plan is showing seek ??

  Please Explain

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

• Ian Scarlett

  SSC-Insane

  Points: 23197

  More actions

  September 24, 2009 at 6:15 am

  #1057957

  This is what your index will actually look like

  1$adams

  2Ajack

  3*anna

  4&smith

  5bamy

  6!sue

  7@rose

  1. select na from t where ch = '!'

  Result :index scan

  id is the first column of your index, not ch, so there is no way the optimiser can go directly to rows where ch = !, so it has to scan the whole index. For this to do an index seek, your index should be

  Create nonclustered index nc_t on t(ch,id,na)

  2. select na from t where id = 6

  Result :index seek

  id is the first column, so the optimiser can go directly to the rows.

  3. select na from t where ch = '!' and id = 6

  Result :index seek

  As for 3

  4. select na from t where flag = 'Y' and id = 6 and ch = '!'

  Result :table scan

  flag isn't part of the index, so the optimiser would have to locate rows matching the other criteria, then go and retrieve the actual row (bookmark lookup) to examine the contents of flag. It has decided it is quicker to read the whole table instead.