Our DBA team has been discussing a lot recently about whether to index a BIT column in our tables or not.
Tell us what do you think. Do you think that a table scan would work just as fast as a non-clustered index seek?
Come on people, show us your DBA skills :-)
Subscribe to:
Post Comments (Atom)
Posts
1 comment:
The question is, do you really WANT an index on a BIT column? We're going to run some experiments, but in general, it is highly unlikely that you will get much use out of such an index. The exception is when the data is heavily weighted towards, say, 1 (e.g. 95-99% of the table), and you are searching for 0. Or vice-versa.
What I'd like to test is the effect on execution time and the execution plan if you run different queries against large tables with a BIT column whose values are evenly distributed, or weighted heavily (in this case 97% - 3%), and in both cases, compare clustered to nonclustered to no index.
So, the answer to this one is yes, you can create a clustered index on a BIT column.
However, as for whether you SHOULD—as with so many other choices—it depends.
The optimizer will do a little bit better if you explicitly tell it that you are dealing with a BIT (since the engine assumes INT), e.g.:
WHERE MyBit = CONVERT(BIT, 0)
--or
WHERE MyBit = CAST(0 AS BIT)
Though that can come with a trade-off as well; namely, remembering to explicitly convert values on every statement. Whereas, if you use a CHAR(1) constrained to 'T'/'F', for example, no explicit conversion is necessary for the proper index to be used.
Post a Comment