>I'm curious, would SQL Server use index on "logical" tinyint field if say we are looking for records with value 1 in that field and they represent only 1% or less of total number of records?
From the looks of things, not unless there is a compelling reason. Unless explicitly directed, SS used a table scan on a table with 26000 rows. The Index was over 7x more costly. When increased to 52000 rows, it decided on its own to use the index.
Larry Miller
MCSD
LWMiller3@verizon.netAccumulate learning by study, understand what you learn by questioning. -- Mingjiao