Index Seek和Index Scan

When   I   create   a   graphical   query   execution   plan   of   a   query   using   Query   Analyzer,   I   notice   that   there   are   two   types   of   scans:   Table   Scans   and   Index   Scans.   How   are   these   different?    
   
   
   
  Answer  
   
  When   the   Query   Optimizer   is   asked   to   optimize   a   query   and   create   an   execution   plan   for   it,   it   tries   its   best   to   use   an   Index   Seek.   An   Index   Seek   means   that   the   Query   Optimizer   was   able   to   find   a   useful   index   in   order   to   locate   the   appropriate   records.   As   you   probably   know,   indexes   make   data   retrieval   in   SQL   Server   very   fast.    
   
  But   when   the   Query   Optimizer   is   not   able   to   perform   an   Index   Seek,   either   because   there   is   no   indexes   or   no   useful   indexes   available,   then   SQL   Server   has   to   scan   all   the   records,   looking   for   all   the   records   that   meet   the   requirements   of   the   query.    
   
  There   are   two   types   of   scans   the   SQL   Server   can   perform.   When   a   Table   Scan   is   performed,   all   the   records   in   a   table   are   examined,   one   by   one.   For   large   tables,   this   can   take   a   long   time.   But   for   very   small   tables,   a   table   scan   can   actually   be   faster   than   an   Index   Seek.   So   if   you   see   that   SQL   Server   has   performed   a   Table   Scan,   take   a   note   of   how   many   rows   are   in   the   table.   If   there   aren't   many,   then   in   this   case,   a   Table   Scan   is   a   good   thing.    
   
  When   an   Index   Scan   is   performed,   all   the   rows   in   the   leaf   level   of   the   index   are   scanned.   What   does   this   mean?   Essentially,   this   means   that   all   of   the   rows   of   the   table   or   the   index   are   examined   instead   of   the   table   directly.   Sometimes,   the   Query   Optimizer   determines   that   an   Index   Scan   is   more   efficient   than   a   Table   Scan,   so   one   is   performed,   although   the   performance   difference   between   them   is   generally   not   much.    
   
  You   might   ask   that   if   there   is   an   index   available,   why   can't   an   Index   Seek   be   performed?   In   some   cases,   such   as   if   a   huge   quantity   of   rows   need   to   be   returned,   it   is   faster   to   do   an   Index   Scan   than   an   Index   Seek.   Or   it   may   be   because   the   index   is   not   selective   enough.   In   any   case,   the   Query   Optimizer   doesn't   think   the   available   index   is   useful,   other   than   for   performing   an   Index   Scan.    
   
  So   what   does   all   this   mean   from   an   analysis   standpoint?   Generally   speaking,   an   Index   Scan   or   an   Index   Seek   is   almost   the   same   thing,   from   a   performance   perspective.   If   you   see   any   one   of   these   in   a   query   execution   plan,   the   first   thing   you   need   to   do   is   to   see   if   there   are   few   rows   in   the   table.   If   so,   then   a   scan   is   OK.   Or,   if   many   rows   are   being   returned,   then   a   scan   is   often   faster   than   an   Index   Seek,   and   the   Query   Optimizer   made   the   correct   choice   of   selecting   a   scan.   The   only   way   to   speed   up   this   particular   situation   would   be   to   find   a   way   to   rewrite   the   query   in   order   to   return   fewer   rows,   assuming   this   is   possible.    
   
  If   the   above   two   reasons   don't   apply,   then   your   next   step   would   be   to   try   to   identify   useable   indexes   to   help   speed   the   performance   of   the   query,   assuming   that   the   current   performance   of   the   query   is   unacceptable,   so   that   an   Index   Seek   is   performed   instead   of   an   Index   or   Table   Scan.