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.

Filed under: Index, Microsoft SQL Server, SQLServerPedia Syndication Tagged: index, microsoft sql server