May 7, 2010 at 10:55 am
Situation is this: I am selecting a column value (not part of any index) from a table containing about 2.5M rows. The WHERE clause on the SELECT statement specifies an indexed column. This columns contains 97% of 1 value and 3% of another value. If I specify the 97% value in the WHERE clause, SQL simply performs a table scan and that makes sense to me. If I specify the 3% value, SQL also performs a table scan and does not use the index. If I add a new value to the index which is unique and reference that value in the WHERE clause, the index is used.
So, is there any rule of thumb to indicate when SQL will decide that a table scan is best based upon index cardinality.
May 7, 2010 at 11:02 am
As you mention a table scan, I am making two assumations, 1) the index that you are referencing is a nonclustered index, and 2) there isn't a clustered index on the table.
The reason it may use a table scan is that it would cost more to read the index and then lookup in the table for the extra data.
May 7, 2010 at 11:04 am
http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 7, 2010 at 11:13 am
I recommend you read that article that Gail posted. If you still have questions, come back and ask them.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 7, 2010 at 11:14 am
Concur. That makes three, must be true.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply