Simulate a 128-bit unsigned integer in SQL and C# using a 64-bit signed value?

Question

Take this scenario: You have a few flag enumerations in C# tied to (and in fact generated from) Enum-ish tables in SQL Server. Say you are a distributor, and you allow your resellers to specify what US states they ship to. Being a brilliant and elegant software engineer, you implemented these as a bitwise-combinable flag value to save storage:

create table USState (
    StateID bigint, StateAbbr char(2), StateName varchar(50))
/* insert all US States + DC into USState, StateIDs must be in powers of two */ 
/* StateID 0 reserved for 'None': */
create procedure GetStatesByFlag (@StateFlags bigint) as
declare @StateIDs table
(
StateID bigint,
primary key (StateID)
)
insert into @StateIDs 
    select StateID 
    from USState
    where @StateFlags & StateID != 0
        or (@StateFlags = 0 and StateID = 0)

select s.StateID, s.StateAbbr, s.StateName 
from 
    USState s join 
    @StateIDs si 
    on si.StateID = s.StateID

Sweet. You can include/exclude dynamically in both SQL and C# using bitwise logic, which lets you instantly hydrate checkbox lists and select lists in Asp.NET while still only storing a single 64-bit number to hold any combination of selections. And you don't need a non-indexable comparison operator in your procedures' WHERE clauses, except against the enum table itself which has a maximum of 64 rows. Searching your distributors for everyone who ships to Indiana and California can still use an equality comparison and an index.

Now you have a request to add support for US territories, armed forces mailing codes and Canadian provinces, and do so in a backwards-compatible fashion. There's no cutting the list down to < 64 entries, and the business really wants to avoid having to segregate old-school states from the rest of the territories and divisions.

What do you do?

Creative answers are appreciated, but the real challenge here is this: Is there a way to force the same bitwise math that works on unsigned 64-bit values to work on signed ones while using the negative space to exceed 64 possible bits, in both C# and SQL (2008)? If it matters, the flag is simulated, not a "real" flag, so it's technically not necessary for this to work against a CLR enum with [Flags] attribute.

Castello

Combinations yes, but in this case a combination is like ship to Indiana and California, not one state. Anyway maybe you could try using GUIDs ? They are in fact 128 bit integers. – Molal

NEgative space is just one of the 64 bits, so no. – Ammieammine

I ran into the exact same problem. How do you use a GUID to store numbers? – Cheree

@arao6, I started to go down that path, but I bailed because the solution was becoming more complicated than the problem. It was just simpler to split the enum into two different sets with fewer than 64 entries each. – Castello

Answer 1

You can't exceed 64 bits on a 64 bit value, not even using the "negative space". If you have 64 bits, you have 64 bits. You can use a Guid to get 128 bits, which will put the problem off for a while, but ultimately you will need to add additional fields.

Hesitancy

Answer 2

In SQL Server, you could try decimal(38,0)

This gives you 38 digits to the left of the decimal point (1E38). In binary terms, it's about 126 bits (8.5E37). And it can be manipulated like a number.

However, one option would be to define what you want in .NET and use a matching CLR data type in SQL Server. This way, it can be consistent between the 2 platforms.

However, I would really consider changing away from flags...

Armillda

Unfortunately, the decimal type is invalid for bitwise operators. The CLR Data Type idea is interesting; I'll investigate whether I can pull off > 64-bit flags with that angle. As far as changing away from flags goes, I normally use them only where I expect a dozen or fewer possible values to exist and there is no other reason to create a many-to-many relationship. I also have a tool which automatically generates indexable queries on the SQL side and Contains operators on the .NET side for them, but I do restrict their use to instances where they're likely to save both space and coding time. – Castello

Answer 3

My 2c: You're trying to be clever, and your causing your own pain. Bitfields and SQL don't mix well, primarily because bitfields cannot be properly indexed and any search will have to do a full scan. Eg. to find all reselers that ship to AK you need to scan the entire table of reselers. Also the solution does not scale to more than 64 values (as you already discovered). It also poor storage choice, it requires a bit with value 0 to store the negative information (lack of association).

Use a separate table to model the many-to-many relation between resellers and states/territories/provinces/countries they ship to.

Priapus

Point taken. Regarding indexing though, searches do not require table scans on the resellers table. There is one scan on the 64-row max States table to build the indexed @ StateIDs table variable, which contains the output of all the bitwise comparisons. (That work could actually be persisted as well, but it's not resource intensive enough to bother at this point.) The @StateIDs table var then joins by value to a foreign key of the resellers table to retrieve the matches with no table scan. – Castello