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
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?
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.