SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Why not a fullscan ?		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kudzen
Starting Member

Portugal
12 Posts
Posted - 08/02/2012 :  07:04:02  Show Profile  Reply with Quote
Hy there guys,

I'm having some troubles understanding when the Query Optimizer decides to use an index.
I saw on the web that there is a Ratio that(supposedly) if is more than 15% the Query Analyzer can not use my Index.
100 * ( filtered key / row count)

I decided to create one table with just 3 columns,
ID int identity(1,1) PRIMARY KEY clustered index
datecheck date,
name varchar(10)

I inserted in my table a lot of rows:
datecheck Total
2012-05-10 558680
2012-06-11 1000


I created one nonclustered index on datechek
The date 2012-05-10 has almost ALL the rows of the table, so why this query is using an INDEX SEEK on date?

select datecheck
from index_test
where datecheck = '2012-05-10'

If i use the ratio that i mentioned earlier, i got something like:

100 * ( 558680 / 559680 ) = 99.82%

What i'm doing wrong ?

Thank you guys
Edited by - kudzen on 08/02/2012 07:10:13

visakh16
Very Important crosS Applying yaK Herder

India
47189 Posts
Posted - 08/02/2012 :  10:03:18  Show Profile  Reply with Quote
thats because you've only datecheck in the select list so optimiser decided it can read the whole information from clustered index itself hence seek.

try this and see if there's difference

select *
from index_test 
where datecheck = '2012-05-10'




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000