Re: Index Seek (or) Index Scan in Execution Plan

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Wayne Snyder (wayne.nospam.snyder_at_mariner-usa.com)
Date: 11/01/04 

Date: Mon, 1 Nov 2004 08:11:11 -0500

The 2 queries in your list are not the same. They are searching for
different rows, and a different number of rows will be returned for each.
This is called selectivity - If a very small percentage of rows in the table
will be returned ( 3-5%) then the query is very selective. Index Seeks are
better for very selective queries and index scans or better for queries with
low selectivity. SQL Server's optimizer is smart enough to figure this out
and (generally) choose a good plan..

see inline 

-- 
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Prabhat" <not_a_mail@hotmail.com> wrote in message
news:%23ByYLXAwEHA.3908@TK2MSFTNGP12.phx.gbl...
> Hi all,
>
> In Adition to Above Post / Question I have 2 More Questions:
>
> 1) Is the Index Seek is Faster or Index Scan? and Why?
Index seek does a binary search from the root to the leaf level, a Scan
reads through part of all of the leaf level.... So scans generally do more
IO than seeks.
>
> 2) How Can I Replace the Index = IndexName in the Above Post? (I Think the
> Index = is used only for backward compatibility in SQL Server 2000)
>
It is preferable to not use index hints, but if performance is killing
you...( update statistics first, then see if you get better response).
select yad yad from table WITH (index = whatever)
Be sure to use the with clause for compatilibility with SQL 2005
> Thanks in Advance for any Suggestion and help for these 2 posts...
>
> Thanks
> Prabhat
>
>
> "Prabhat" <not_a_mail@hotmail.com> wrote in message
> news:#J1NJu$vEHA.3096@TK2MSFTNGP14.phx.gbl...
> > Hi all,
> >
> > I have one table. Where :
> >
> > DonorID    Int (Identity)   Primary Key
> > FirstName    Varchar(25)
> > LastName    Varchar(25)
> > ...
> > ...
> >
> > I have One nonclustred index on Lastname another nonclustred index on
> > (lastname, firstname).
> >
> > Suppose I Execute the Query:
> >
> > select * from TABLE where lastname like 'abott%'            (This Query
> uses
> > Index Seek on the Compound Index)
> >
> > But if I use the below Query:
> >
> > select * from TABLE where lastname like 'smith%'        (This Query uses
> > Index Scan)
> >
> > But
> >
> > select * from TABLE (index = ind_CMP_name) where lastname like 'smith%'
> > (But this Query uses the Index Seek)
> >
> > NOTE: ind_CMP_name is the Compound Index.
> >
> > Why there is the Difference, One Query uses Index Seek while other uses
> > Index Scan, even if both the query uses the same where condition on same
> > column?
> >
> > Thanks
> > Prabhat
> >
> >
>
>

Relevant Pages

  • Re: Index Seek (or) Index Scan in Execution Plan
    ... your queries do run better when you use them. ... Run your query with and without the hint, ... SQL Server MVP ... >>>> It is preferable to not use index hints, ...
    (microsoft.public.sqlserver.server)
  • Re: Index Seek (or) Index Scan in Execution Plan
    ... your queries do run better when you use them. ... Run your query with and without the hint, ... SQL Server MVP ... >>>> It is preferable to not use index hints, ...
    (microsoft.public.sqlserver.programming)
  • Re: Ongoing purging of active records causes deadlocks
    ... For a query like: ... plan for some of our queries and it looks like it sorts first, ... What is a possible alternative, though, is that the purge first performs: ... Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books ... ...
    (comp.databases.ms-sqlserver)
  • Re: Looking for a professional SQL programmer for a small job
    ... Pro SQL Server 2000 Database Design - ... I have two queries to build and while I know my way around SQL, ... >> One is a seach query that pull rental properties from a database based on ...
    (microsoft.public.sqlserver.programming)
  • Re: Index Seek (or) Index Scan in Execution Plan
    ... The 2 queries in your list are not the same. ... This is called selectivity - If a very small percentage of rows in the table ... will be returned then the query is very selective. ... Wayne Snyder, MCDBA, SQL Server MVP ...
    (microsoft.public.sqlserver.server)