128 bit Int in SQL Server 2012?
I'm looking for a better way to implement 128 bit unsigned integers in SQL Server. The basic requirement is that it must support bitwise operations on all 128 bits. (Is this theoretically possible on a 64-bit machine? I digress.)
I read about some implementations using C # and custom types via CLR assembly, but I couldn't determine if it supports bitwise operations.
Has anyone successfully done what I need to do? Any input would be greatly appreciated. Thank!!
source to share
Database side, I would use a column binary[16]
.
Then, in your clr code, you can overload the bitwise operators on your 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);
}
}
Basic CLR example, you need to execute methods FromByteArray
and ToByteArray
:
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 run bitwiseAnd128Bit
on two columns.
More on CLR here http://msdn.microsoft.com/en-us/library/w2kae45k(v=vs.90).aspx
source to share