SQL Server Performance Forum – Threads Archive

Index on a BIT?

Hi all, I was wondering if there are every certain situations where it could be ok to put an index on a bit? I’ve read in many places that it’s not practical to put indexes on a column with less than 5% uniqueness. However, we have a table where we think there may be considerable improvment with an index on the bit field. The table looks something like this: PK InvestorID int
PK SecID int
PK DatedDate datetime
Qty int
CurPos bit Each combination of InvestorID/SecID/DatedDate is unique. There could be 100’s of rows with the same InvestorID/SecID combo – but only one with CurPos = 1. In almost every single query, we are searching for CurPos = 1. We’re trying to figure out which column(s) should be the clustered index, and if CurPos should be an index at all. Would it be silly to have an index on the BIT in this case? We did notice in one of our queries that a table scan was eliminated when we put a non-clustered index on CurPos. One last question: I’m a bit confused on the order that the query is processed. Let’s say we have the PK composite as our clustered index. Then we do a search like: WHERE SecID = 10004 AND DatedDate < 3/5/04 AND DatedDate > 1/1/04 AND CurPos = 1 Will it use the CI to first eliminate the first group of the where clause, and then do a scan for CurPos on the data thats left? By the way, this table is updated/inserted to very frequently via triggers. Thanks! ~H

Well, from Books online here would be the best reason why
"Columns of type bit cannot have indexes on them. " Chris

quote:Originally posted by ChrisFretwell Well, from Books online here would be the best reason why
"Columns of type bit cannot have indexes on them. " Chris

Hmmm…Which article in BOL does it say that? Anyway, maybe it’s not recomended, but it’s definately permitted. I just did it. ~H

Look up BIT, described. Have you tried a query to use it? The why you wouldnt want to goes way back in my memory to my first look at sql in version 4.2 (yup, been there done that) I’ll try to remember how it was worded. In general, if you only have a few possible values, in this case 2, then having an index most likely require traversing half or more of the data (based on the distribution of the values of course) so an index, even if used would not provide a great deal of performance gain in exchange for the possible overhead of data space, time to add/modify records and indexes etc. There are lots of documents on the subject, and a variety of opinions too. Chris

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by ChrisFretwell Look up BIT, described. Have you tried a query to use it? The why you wouldnt want to goes way back in my memory to my first look at sql in version 4.2 (yup, been there done that) I’ll try to remember how it was worded. In general, if you only have a few possible values, in this case 2, then having an index most likely require traversing half or more of the data (based on the distribution of the values of course) so an index, even if used would not provide a great deal of performance gain in exchange for the possible overhead of data space, time to add/modify records and indexes etc. There are lots of documents on the subject, and a variety of opinions too. Chris <hr height="1" noshade id="quote"></blockquote id="quote"> Thanks Chris. Yeah, I totally understand the reasons why you wouldn’t want to put an index on a BIT. The thing is, I was wondering if our case is special. We know that 95% of our records have the column set to 0. Only about 5% have 1. And in every query we’re only getting rows where that BIT is 1. One of our developers tried putting the index on CurPos and he got rid of a table scan. I guess it’s a no-brainer that it shouldn’t be a clustered index – but what about a non-clustered? Or what about part of a composite index? Thanks so much for your help, ~H P.S. – WOW SQL Server 4.2! Was that written in punchcards?? <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ />

No, but it might have helped. SQL 4.2 was a interesting start for MS. It had no EM per say, so its why I’m more comfortable writing t-sql code than using wizards or EM. I can remember one app we were developing with VB 1.0 and SQL 4.2. In the end, we moved the database to access 1.0 (keeping the front end as VB 1.0) for performance reasons. I’m glad SQL 6 then 6.5 followed so quickly. Not to mention the VB improvments (but I left the developer side a month after VB 5.0 so I cant comment on anything in that relm anymore) Chris

]]>