Indexing a Boolean Column

[Bob Day]

USING VS 2003 vb.net MSDE...

I have a table with a boolean (bit) data type that I use as an index (table,
etc., created via Server Explorer). When I go to the tables property pages,
Index tab, this bit column does not show up in the drop down list of columns
to create a new index on it.

Is there some issue with creating an index on a bit column? It works OK
with out the index, but I would think it would allow me to create an index.
Or is an index not needed for a bit column?

Please advise

Bob

 

[Joe Fallon]

Indexing a Boolean column is not a recommended practice. (The data is not
"dispersed" enough to do any good.)
It usually causes more work than it is worth.

You may consider creating a regular PK index and then another index with the
PK and your Boolean field together.
This 2nd index could be used as a "covering" index - in other words any
query that needs both values (PK and Boolean field) has them in this
covering index so it does not need to go to the data row to get the other
field.

Examine your most common queries and determine what fields from this table
are most called.
If it is PK, Boolean field and UserId then index by PK, UID, Boolean and you
get the best of all worlds.
The index covrs the most common queries and returns all the required data
without going to the table and doing a lookup.

 

[Kevin Yu [MSFT]]

Hi Bob,

I'd like to know if this issue has been resolved yet. Is there anything
that I can help. I'm still monitoring on it. If you have any questions,
please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."