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

Question

Take this scenario: you have several flag enumerations in C # related to (and actually generated) Enum-ish tables in SQL Server. Say that you are a distributor, and you allow your resellers to indicate which US states they are shipping to. As a brilliant and elegant software engineer, you implemented them as a bit-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 enable / exclude dynamically in both SQL and C # using bitwise logic, which allows you to instantly remove checkboxes and select lists in Asp.NET, while retaining only one 64-bit number to preserve any combination of the selected ones. And you do not need a non-indexable comparison operator in your procedures. WHERE clauses, with the exception of the enumeration table itself, which has a maximum of 64 rows. Finding your distributors for anyone traveling to India and California can still use the equality comparison and index.

Now you have a request to add support for the territories of the United States, postal codes of the armed forces and provinces of Canada and do this in the reverse order. There is no cutting list to <64 entries, and the business really wants to avoid the need to share the old school countries with the rest of the territories and units.

What do you do?

Creative answers are welcome, but the real problem is this: is there a way to get the same bit math that works with unsigned 64-bit values ​​to work on signed ones using negative space to exceed 64 possible bits, like in C # , and in SQL (2008)? If it matters, the flag is modeled, not the “real” flag, so it is technically not necessary that this works against the CLR with the [Flags] attribute.

Answer 1

You cannot exceed 64 bits on a 64-bit value without even using the "negative space". If you have 64 bits, you have 64 bits. You can use Guid to get 128 bits, which will prevent the problem for a while, but in the end you will need to add additional fields.

Answer 2

In SQL Server, you can try decimal (38.0)

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

However, one option would be to determine what you want in .NET and use the appropriate CLR data type in SQL Server. Thus, it can be agreed between the two platforms.

However, I would really think about discarding the flags ...

Answer 3

My 2c: You try to be smart and you hurt yourself. Bitfields and SQL do not mix well, primarily because bit fields cannot be indexed correctly, and any search will need to perform a full scan. For instance. to find all resellers going to AK, you need to scan the entire reseller table. Also, the solution does not scale to more than 64 values ​​(as you have already discovered). It is also a poor choice of storage, this requires a bit with a value of 0 to store negative information (no communication).

Use a separate table to model many-to-many relationships between resellers and the states / territories / provinces / countries to which they are sent.