Indexing Yes/No Fields: Wasted?

P

(PeteCresswell)

I'm hearing that in a SQL Server DB, indexing Booleans is a
no-no: wasted effort.

By implication, it doesn't speed up the process of listing all
the records where IsWhatever=True.

Does the same hold for JET?
 
J

John W. Vinson

I'm hearing that in a SQL Server DB, indexing Booleans is a
no-no: wasted effort.

By implication, it doesn't speed up the process of listing all
the records where IsWhatever=True.

Does the same hold for JET?
Click to expand...

I can't quote a cite or an experiment, but I've certainly read that it depends
on the data distribution. If you have 999,995 No records and 5 Yes records,
and an index on the field, searching or filtering for the Yes records should
be faster than a full table scan. Of course if you have half a million of
each, you'll get no benefit or even a loss.

I would expect this to be true for any modern database engine, but I'm very
willing to be corrected.
 
M

MikeB

I'm hearing that in a SQL Server DB, indexing Booleans is a
no-no: wasted effort.

By implication, it doesn't speed up the process of listing all
the records where IsWhatever=True.

Does the same hold for JET?
Click to expand...

You would have an index with two values (and perhaps Null if that is
allowed), so all the records would hang of one of those two values.
makes for sequential searching through all the records. So it would
seem a wasted effort.
 
T

Tony Toews [MVP]

(PeteCresswell) said:
I'm hearing that in a SQL Server DB, indexing Booleans is a
no-no: wasted effort.
Click to expand...

An Access regular, but I don't recall the name now, said it did make a
big difference.

Try it and see. Exit Access between each test though as I've noticed
that Access will cache some data between rerunning of a report in
particular.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
D

David W. Fenton

I'm hearing that in a SQL Server DB, indexing Booleans is a
no-no: wasted effort.

By implication, it doesn't speed up the process of listing all
the records where IsWhatever=True.

Does the same hold for JET?
Click to expand...

I don't know if it's even true for SQL Server. Theoretically,
sparsely populated indexes speed things up less than well-populated
indexes, but I've found that with Jet, indexing Boolean fields does
make a significant difference in performance, and is worth it. I
don't know if SQL Server will or will not show any difference, but I
certainly do know not to trust conventional wisdom that is based
entirely on theory and not practice.
 
D

David W. Fenton

m:
You would have an index with two values (and perhaps Null if that
is allowed), so all the records would hang of one of those two
values. makes for sequential searching through all the records. So
it would seem a wasted effort.
Click to expand...

Yet, for some reason, it still speeds up selects on the Boolean
field.

In the real world, that is, as opposed to the world of theory.
 
Top