Re: Setup index

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Wayne Snyder (wsnyder_at_computeredservices.com)
Date: 05/29/04 

Date: Sat, 29 May 2004 08:15:03 -0400

The optimizer tries to find a good way to get your data. Using a
nonclustered index to retrieve data is often the best answer when there is a
very small percentage of the rows being returned. As the number of rows
being returned increases, at some point it becomes faster for the results to
come directly from the clustered index .

What you are seeing is the normally good behaviour of the optimizer. 

-- 
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
"ying" <anonymous@discussions.microsoft.com> wrote in message
news:146e401c444f2$f3fe7310$a301280a@phx.gbl...
>
> I create a index on a "date" field on a table. When I use
> query:
> Select ...
> From  table
> Where date='5/21/2004', in the estimated executation plan,
> I can see it use the bookmark lookup which is the "date"
> index.
> However, if I change the query to:
> ....where date between '1/1/2004' and '1/31/2004'. It use
> clustered index which is another field.
>
> Can any one tell me why this happen? I think both queries
> should use the "date" index. How I can improve the
> performance?
>
> Thanks.

Relevant Pages

  • Re: Usage of Views
    ... Can you please suggest any way to understand the details about the optimized query? ... > I support the Professional Association for SQL Server ... Would there be any chance of optimizer applying the operations ... > final desired output? ...
    (microsoft.public.sqlserver.server)
  • Re: Help with using Alias Fields in a expressions.
    ... Interesting...but it seems that the query is optimized quite well. ... query optimizer seems quite smart in this case. ... sub-quires like I did does run very well in JET, and also sql server. ...
    (microsoft.public.sqlserver.server)
  • Re: collation for multiple language in a column
    ... The query returns 100 rows, and without the collate clause, it would have ... COLLATE clause, SQL Server needs to add a Sort operator to the plan. ... is such that the TOP 100 restriction makes it very easy for the optimizer ...
    (microsoft.public.sqlserver.server)
  • Re: index views ?
    ... You can create indexed view in SQL Server 2000, ... With Enterprise Edition the Query ... Optimizer will look to use indexed views even if the view isn't directly ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: Mysterious text in query result
    ... When I use Query Analyzer to retrieve data from the column and display ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxxxxxxx Online for SQL ... Books Online for SQL Server 2000 ...
    (microsoft.public.sqlserver.tools)