Indexes on bit fields 1

[EriRobert]

Hello

You don't seem to be able to index a bit field in a table. I can sort of understand this as there are only 2 values to index. However if the distribution of the values in the field are, say 99% (0) and 1% (1), and you are searching for values of 1 then the index is beneficial.

Is SQL doing anything clever behind the scenes with bit columns?

It is almost like you want to say index just the small half of the bit column (1 in the above example) to keep the index small, then searches would be possible and efficient.

I've got a few cases like the one above and wouldn't like to think of it searching without an index. And I don't like having to define the column as, say, smallint instead to force indexing.

Does anyone have more information on bit columns / indexes?

Robert

 

[vongrunt]

Apparently EM won't let you create such index - bit columns are not available in dropdown column list (Manage Indexes/Keys dialog). But manual CREATE INDEX works :X

create table blah
(	foo int primary key,
	bar varchar(20) null,
	flag bit 
)
create index ix_flag on blah(flag)
--drop table blah

One smart thing SQL Server does: 1 bit allocates 1 byte of storage but occupies only 1 bit. So if you have even 8 bit columns, they will take only 1 byte of storage per row together.

Index on bit column is generally not a good idea because of - as you said - poor typical selectivity. Plus bit cannot be used in GROUP BY queries without additional CAST/CONVERT.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[JayKusch]

Bottom line ... Per BOL (Books OnLine) ...


Columns of type bit cannot have indexes on them.

Microsoft® SQL Server™ optimizes the storage used for bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 through 16 bit columns, they are stored as 2 bytes, and so on.






Thanks

J. Kusch

 

[vongrunt]

> Columns of type bit cannot have indexes on them.

This comes from Books Offline ; Books Online have that fact removed :

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_2it0.asp

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[EriRobert]

I'm not disagreeing whether or not a bit column can be indexed - it can't.

What's the solution when you want a bit column, but think it should be indexed? Am I right in thinking any searches on that column will be slow.

In this case should I define the column as something else, for example tinyint so I can index it? Doesn't seem right somehow.

 

[vongrunt]

Again - it can. Just checked on SQL2k PE/Std/EE, all SP4.

Other than that.... if you frequently query for bitcolumn=1 this may indicate lots of "dead" data in a table (for example no more registered users, expired memberships etc) so another option is horizontal table partitioning. Of course this is much easier to do from scratch than on living database.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[EriRobert]

Hi Vongrunt

Do you think that the manual Create Index is truely creating the index. It just seems strange that EM won't allow it just to put you off (even though it would work).

 

[vongrunt]

Yup, it was there in system tables and used by optimizer when appropriate.

FYI check this:

http://www.aspfaq.com/show.asp?id=2530

Personally I never tried to create index on bit column before, but remember that GROUP BY bitcolumn didn't work (dunno exactly which SP, may be even SP3). Now both things work (?!?).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[EriRobert]

Thanks vongrunt that's a good FYI