I need to store a bit array for each record of a table, supporting the following operations:
Testing if a bit is set, and setting a bit (using SQL)
Querying and setting the value using ADO 2.8 (not ADO.NET)
Indexing (in order to benefit from the "covering index" feature)
The maximum number of bits to be stored in this array is fixed, but may exceed 32. That is, a simple int column doesn't always work.
From what I've seen so far, my options are:
- Use several int columns
- Use bigint (works as long as the number of bits is <= 64)
- Use binary
- ?
The first option would work, but require quite a bit of refactoring in the code that accesses the data. The second option is a temporary relief only, and from my searches so far I'm not too sure if ADO works that well with bigint. I have no experience with binary, and I'm not aware of any other options.
Which data type would you choose, given the requirements?