SQL: Are bit columns useless in SQL Server indexes?

If you are aware of Betteridge’s law of headlines, you already know the answer, but let me explain.

There are a lot of odd myths that surround SQL Server. One of the more persistent ones is related to indexes on columns that hold bit values.

A SQL Server column that’s a bit can be in three states. It can have the value zero; it can have the value one; and it can be NULL (ie: have no value at all).

Back in SQL Server 6.0 and 6.5 days, the Microsoft courseware used to tell you that if you had a column that was just a bit (ie: 0 or 1), then there was no point creating an index on it. The same advice continued onto SQL Server 7 materials, and if I recall correctly, SQL Server 2000 courseware said something similar.

In SQL Server 7 and 2000, the product used to actively fight with people who were trying to create these types of indexes. Enterprise Manager (one of the predecessors to Management Studio), had a GUI for creating indexes but didn’t list any bit columns for you to choose.

Little wonder that there was a persistent message that bit columns were pointless and that myth continues to this day in many people’s minds.

The logic was that if there were only two values, there would be nothing selective enough about a bit to make it useful. I’m sure they were thinking about proportions like male and female (not a good option for today’s world) and they were imagining fairly evenly distributed values. If half of the values were zero and half were one, then what was the point of the column as an index key?

Problem is that was only partly true, and only up to a point.

[caption id=“attachment_3042” align=“alignnone” width=“522”] Image by Mikhail Rakityanskiy[/caption]

Indexing has never been about the range of potential values, it’s about the selectivity of the values.

So I might not want to use a bit column in an index if it’s a 50/50 proposition, but imagine an IsFinalized column on a transaction table. If there are 2 billion transactions and 200 that aren’t finalized, I’d never use the index to find the finalized ones, but I’d sure be hoping there was an index to let me find the unfinalized ones!

Even in SQL Server 2000, where it tried to dissuade you in the tools, you could use bit columns in indexes when creating the index by using T-SQL. We’ve used them very successfully over the years.

So if you hear people immediately dismissing bit columns from indexes (or see this in the output from development tools), don’t buy into it. Always look at the selectivity of columns in indexes, not the ranges of possible values.

2018-04-16