title: Simulate a 128-bit unsigned integer in SQL and C# using a 64-bit signed value?
body: <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 &amp; 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 &lt; 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>