[dba-SQLServer] Indexes on single value fields

Mark Breen,

I think it was you that mentioned that an index on a field that only contained a single value ('Y' 
in my case) would not be used.  I can report that it definitely is used.  I have been slowly 
converting my fields with either a 'Y' or a ' ' (space), replacing the ' ' with null.  I tried to do 
a count / group by to discover how many values I had in one of my fields and without the index it 
had not finished after about 8 minutes.  With an index it took five seconds.

In fact it took less time to build the index than it took to count without the index, which is 
pretty strange if you ask me.

I use these "Y / nothing" fields as criteria in where clauses for the orders that the client sends 
me.  I always generate cover indexes on the order selection fields before running the order because 
it so dramatically reduces the time to select the resulting order records.

-- 
John W. Colby
www.ColbyConsulting.com