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

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.

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...

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. – Paul Smith Nov 15 '09 at 4:51.

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.

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.

– Paul Smith Nov 14 '09 at 20:31.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.