Faster requesting one binary (1) field or 8 bits of fields?

Question

If I have 8 logical values ​​in a record, and these 8 values ​​are always used when querying a table, and the data in the table is static (read-only), is it faster to match records by indexing and query to one byte field as binary (1), or is it better to have 8 separate bit columns with all 8 added to the index?

Answer 1

Indices in one bit field will be mostly useless. A little scary selectivity, 0 or 1, and probably the optimizer will ignore it. 8 indexes on 8-bit fields will be 8 indexes that are not taken into account by the optimizer.

The index in the byte column is only slightly more selective, with 256 different values. But if you are looking for individual bit patterns, such as “3 on,” then there is no way to express this as an individual value for a search or as a range.

The conclusion is that no matter what you try, you will end up in the table anyway.

So better explain what your problem is, and not your solution, and perhaps we can think of something more effective.

Answer 2

I would enable the option of indexing one byte or at least a computed 1-byte column with 8 values ​​together if you need to keep them separate for other objects. (perhaps best of both worlds)

As one byte, the power of the comparison results to search across all 8 values ​​is likely to avoid tipping points than 8 separate indices - although these 8 indices can be combined by the engine, I have a suspicion that the power may make a query hint and scan, and don't look - you would need to run tests to prove it.

Answer 3

One byte request will work faster. Building a byte for comparison in memory will be much faster.