SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

DBA Myths: An index on a bit column will never be used.

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)
-- Load data
GO 100

-- Warning, this can take a while to run
GO 2000000

-- Run query
SELECT * FROM BitIndexTest WHERE myBit = 0

And here is the execution plan. Notice that there is an index seek using the index on the bit column.


-- Cleanup code

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


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...