Index on bit fields in SQL Server Management Studio

Index on bit fields in SQL Server Management Studio

• lawlordds

  an index on a bit field gives absolutely no benefit due to the way that sql server handles bit fields.
  
  

• Buddhaaz

  http://msdn2.microsoft.com/en-us/library/ms177603(SQL.90).aspx

  see how bit fields are groupd together as bytes. . . bit fields 1 - 8 are in one byte, bit fields 9 - 16 are in another. . . . and so on It was the same in SQL 2000.

  as far as the 'smell', and I don't mean that as disrespectful, often when there is a bit field there is some other piece of data that can be used or should be tracked.  

  for example, instead of an 'IsSubscribed' bit field, have a nullable field SubscriptionDate, then selecting 'IsSubscribed' = -1 equates to

  select p.id from person where not SubscriptionDate is null

  In this case SubscriptionDate contains much more information.

  And often times you need to track Subscription information and that should be in another table.  Then selecting IsSubscribed = -1 transforms to:

  select p.id from Person p inner join Subscription s on p.id = s.personId

  
  
  

• Suzanne

  My experience tells me that this is not true. When the SQL Server only has to access an index instead of the real table, this can increase performance. In the past we've converted Bit fields to TinyInt, so they could be added to an index (while the only values where 0 and 1). This sometimes resulted in queries that executed more than 10 times as fast as without that field in the index.
  
  

• mynus

  Maybe your right about the unnormalized schema, but creating an extra table for each bit field just for the sake of normalizing makes reading the schema a lot more complicated.
  
  I've been searching, but can't find any information on managing bit fields in SQL Server. Have you got a reference for me were I can find more information
  
  Thanks for your help.
  
  

• DJT_UK

  Well you actually can create indexes on bit fields in SQL Server 2000, but you can't do it through the Design Table Interface. You could either use T-SQL to do it or if you want to do it Visually, then you could also right-click on the table choose All Tasks->Manage Indexes and you can select bit fields here to create the index.

  Now Microsoft discloses the storage implementation of bit field data types, but that doesn't mean that they don't store bit field data types differently for Indexes. Maybe if a bit field is Indexed they store it in the B-Trees in their own byte field instead of in the concatenated fashion that they store the data itself. Doubtful, but possible. Your best bet would be to use Query Analyzer with Show Execution Plan enabled and look at the difference between querying with a Indexed Bit field and without it. If it works I would imagine there are situations where it could be helpful. There are definitely appropriate times when you can/should utilize bit fields (e.g Male/Female), etc...

  If you had a very large database and a bit field and you wanted to search your data then an index on that field would work. The question is "Does Microsoft do some unpublished Magic" to take advantage of it, as in store the Bit Field Data differently for Indexes as opposed to data, but the best way to test it would be to setup an appropriate scenario and evaluate the Execution Plan and Performance.

  Sam

  
  

• stacybleger

  but thats not a bit field. . . that is a tinyint.

  read as to how bit fields are managed. if you have one bit field it might help. . . but if you have more than one in a table it won't.

  I contend, a need for an index on a bit field 'smells' of an unnormalized schema (not in all cases, but 99%)

  
  
  
• Index on bit fields in SQL Server Management Studio