Why not a fullscan ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Why not a fullscan ?

Author

Topic

kudzen
Starting Member

12 Posts

Posted - 2012-08-02 : 07:04:02

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-02 : 10:03:18

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources