BIT Column Indexes

There are two myths around BIT column indexes.

Myth #1. Cannot create indexes on BIT columns.

Not sure how this was evolved. But this not absolutely false. Let us try it.

USE tempdb
GO

CREATE TABLE Employee
(ID INT IDENTITY PRIMARY KEY CLUSTERED,
Name Varchar(50),
Status BIT
)

CREATE INDEX idx_employee_status ON Employee(Status)

Above statement is successful and you can create the index is created.

I verified this in SQL Server 2000 SP4 and it was successful.

Myth #2. BIT Column indexes are not useful.

Above argument has a base. BIT column can have possible three values, 0, 1 and NULL. Therefore index selectivity is low.  For example, consider the above table which has 10 million rows and status will say whether these are active or not.  In large table, active records will be very less so active = 0X1 will be highly selective.