GilaMonster (1/7/2013)

What is the basis for you claim that if only 1% of rows in the table match that SQL will almost certainly do a table scan? If the table has 1M rows, but only 10K match, you state SQL is almost certain to do a table scan??

Experience and testing. Yes, SQL is almost certain to scan at 1% of the rows in the table if it does not have a covering index to seek on (Almost certain. I can probably conjure an example where at 1% with accurate row estimations seeks and key lookups are done). Feel free to test it, or to see the many, many blog posts and articles on this subject.

Where did you get that 30% is a hard number for a tipping point?

iirc it's in Kalen's book. Not in the mood for hunting for it tonight.

How could 0.1% of the rows in a table equal 30% of the table pages??

Let's say rows of 100 bytes each. That's 80 per page. 1 000 000 rows is 12 500 pages. 30% of that is 3750 pages. 3750 rows would be 0.375% of the total rows in the table.

In case you misunderstood my comment, tipping point is when the number of rows to be looked up is equal to 30% of the total page count in the table