StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSimulate a 128-bit unsigned integer in SQL and C# using a 64-bit signed value?
text
Body
copied!<p>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: </p> <pre><code>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 </code></pre> <p>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.</p> <p>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 <em>really</em> wants to avoid having to segregate old-school states from the rest of the territories and divisions.</p> <p>What do you do?</p> <p>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. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload