SQL Interview: 3: Are bit columns useful in indexes?

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.
Section: Query Performance Level: Medium
Question:
In SQL Server a column that is defined with a bit data type can only have the values 0, or 1, or it can be NULL if the column allows it.
Given such a small number of possible values, are bit columns ever useful in indexes? Can you give an example of when they might or might not be useful?
Answer:
The range of potential values for a data type is not the issue. The selectivity of the values is critical.
So while an index that includes a value that’s evenly distributed (i.e. it’s zero in half the rows and one in the other half), is unlikely to be useful, a highly skewed distribution of values can make them very useful.
For example, a query that’s finding a hundred incomplete tasks amongst millions of completed tasks, will most certainly find the column useful in an index. It’s also an example of what would potentially be useful in a filtered index.
For a more complete discussion, see this previous blog post.
2021-03-26