Indexing Bit Columns

In previous versions of SQL Server (prior to 2000), you were unable to index a column with a bit datatype ie: a true/false, yes/no, boolean value. In Books Online for SQL Server 2000, under the description of the bit data type, it still said:


Columns of type bit cannot have indexes on them.

Microsoft® SQL Server™ optimizes the storage used for bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 through 16 bit columns, they are stored as 2 bytes, and so on.

Well it turns out that you can. Apparently it’s covered in an update to Books Online and it did get changed in SQL Server 2000.

One of the reasons we have always avoided bit data types is because we couldn’t build indexes on them. Yes, I know most of the Microsoft documentation says it’s pointless because there are only two possible values but they always assume an even distribution eg: Male vs Female. We often have boolean values like Finalised/Unfinalised that might have less than 1% of the values unfinalised. In that case, you’d certainly be sad if you didn’t have an index on the column.

There are other issues though. As mentioned in the quote above, a bit column may well end up consuming a byte anyway and I’m guessing that masking out a bit from a byte is going to be slower than just using the byte. I’ll bet when they get used in an index they end up using a byte regardless.

Then there are development environments, some of which store -1 for True, some of which store 1. There are even tools from Microsoft that do each of these. If we use smallint, it doesn’t matter unless we are indexing the column and then only if we’re interested in the True values, not the False values. In our case, we’re after the False values most of the time anyway.


Leave a Reply

Your email address will not be published. Required fields are marked *