128 bit Int in SQL Server 2012?

Question

I'm looking for the best way to implement 128 bit unsigned integers in SQL Server. The main requirement is that it must support bitwise operations across all 128 bits. (Is this even theoretically possible on a 64 bit machine? I digress.)

I've read about some implementations using C# and user-defined types via a CLR assembly, but I couldn't determine if that would support bitwise operations.

Has anyone successfully done what I need to do? Any input would be greatly appreciated. Thanks!!

Answer 1

Database side, I'd use a binary[16] column.

Then in clr code code you can overload the bitwise operators within a custom type:

public struct My128BitValue
{
    private readonly long _l1;
    private readonly long _l2;

    public My128BitValue(long l1, long l2)
    {
        _l1 = l1;
        _l2 = l2;
    }

    public static My128BitValue operator &(My128BitValue left, My128BitValue right)
    {
        return new My128BitValue(left._l1 & right._l1, left._l2 & right._l2);
    }

    public static My128BitValue operator |(My128BitValue left, My128BitValue right)
    {
        return new My128BitValue(left._l1 | right._l1, left._l2 | right._l2);
    }

    public static My128BitValue operator ^(My128BitValue left, My128BitValue right)
    {
        return new My128BitValue(left._l1 ^ right._l1, left._l2 ^ right._l2);
    }
}

CLR basic example here, you need to complete FromByteArray and ToByteArray methods:

using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{    
    [SqlFunction()]
    public static SqlBinary bitwiseAnd128Bit(SqlBinary lhs, SqlBinary rhs)
    {
        My128BitValue v1 = My128BitValue.FromByteArray((byte[])lhs); //explicit conversion
        My128BitValue v2 = My128BitValue.FromByteArray((byte[])rhs);
        My128BitValue result = v1 & v2;
        return result.ToByteArray(); //implicit conversion
    }
}

From SQL you can then run bitwiseAnd128Bit on two columns.

Read more about CLR here http://msdn.microsoft.com/en-us/library/w2kae45k(v=vs.90).aspx