Adding Bit Datatype Column to the INDEX

Question

Adding Bit Datatype Column to the INDEX

nagesh.prasad

SSC Veteran

Points: 291
More actions
February 14, 2013 at 10:37 pm

#270224

Can we add bit datatype column to the index? What will be the impact of the same?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 1

There is nothing to stop you adding a BIT data type to an Index, HOWEVER, it will have very low cardinality, as there are basically 3 states, True, False, and Unknown.

Ideally the Bit wouldn't be the only column in the index and in 99% of cases is probably going to be part of the INCLUDE part of the index, as BIT fields tend to be used for filtering.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Yes, you can. Impact depends on whether it's useful for queries, where it is in the index and a whole lot more.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Jason-299789 (2/15/2013)
in 99% of cases is probably going to be part of the INCLUDE part of the index, as BIT fields tend to be used for filtering.

If the bit column tends to be used for filtering, wouldn't you put it in the key columns so that it can be part of the index seek.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 4

Gail,

I would say it depends on various factors, but as a general rule I would look at having it in the Include column list initially, and then evaluate.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

My general rule is if it is filtered on and can be part of a seek, then it goes in the key. No sense in making SQL do more work in an index seek than necessary.

Now, if the queries are such that the bit can't be used for a seek (function on the column), then sure it goes into the include

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 6

Fair Point Gail,

In then end it comes down to evaluating the requirement and looking at the reason for putting the BIT column on that particular index, then evaluating the change across other queries.

_________________________________________________________________________
SSC Guide to Posting and Best Practices