February 17, 2014 by Kenneth Fisher
Not true. (Or I guess probably wouldn’t be posting about it would I?)
Probably the first thing I should point out is that just because you can doesn’t mean you should. I can only think of a few very edge cases where an index on just a bit column would be appropriate. Not even if you add a few included columns. Primarily if a bit column is going to be part of an index it should be just that, part of a bigger index.
On top of that I realize that some people really dislike bit’s. Personally I disagree. I think they are a datatype like any other. I’m not going to waste space on a tinyint or char(1) when a bit will do. Now don’t get me wrong, I’m also not going to use them when they are not appropriate either.
And on to a quick proof:
-- Set up code CREATE TABLE BitIndexTest (Id int NOT NULL identity(1,1), myBit bit) CREATE INDEX ix_BitIndexTest ON BitIndexTest(myBit) GO
-- Load data INSERT INTO BitIndexTest VALUES (0) GO 100 -- Warning, this can take a while to run INSERT INTO BitIndexTest VALUES (1) GO 2000000 UPDATE STATISTICS BitIndexTest GO
-- Run query SELECT * FROM BitIndexTest WHERE myBit = 0 GO
And here is the execution plan. Notice that there is an index seek using the index on the bit column.
-- Cleanup code DROP TABLE BitIndexTest
Now this is a bit of an edge case. A small number of rows with one value and a large number with the other. If it had been a much higher percentage then you get a table scan instead of an index seek. In fact if you up the number of 0’s to 1000 then it switches over to a table scan. If you include (id)to the index then it will use the index longer but I couldn’t say for certain how much longer.
Again this an edge case. And because it bears repeating, just because you can create an index on just a bit column doesn’t mean you should. If you are using SQL 2008 or higher then a better solution to the same problem would be a filtered index. I still wouldn’t put a filtered index on the 90% side of a 10/90 split. I probably wouldn’t put one on a 50/50 split for that matter. But if you are going to pick one or the other a filtered index is the better solution.