Skip to content

Instantly share code, notes, and snippets.

@SQLKiwi
Last active May 30, 2019 16:04
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save SQLKiwi/6f82582a4ad1920c372fac118ec82460 to your computer and use it in GitHub Desktop.
Save SQLKiwi/6f82582a4ad1920c372fac118ec82460 to your computer and use it in GitHub Desktop.
using Microsoft.SqlServer.Server;
using System.Collections.Specialized;
using System.Data.SqlTypes;
public partial class UserDefinedFunctions
{
[return: SqlFacet(MaxSize = 16, IsNullable = false, IsFixedLength = true)]
[SqlFunction(DataAccess = DataAccessKind.None, SystemDataAccess = SystemDataAccessKind.None, IsDeterministic = true, IsPrecise = true)]
public static unsafe byte[] HashCustomPerTable
(
SqlByte ColTinyInt01, SqlByte ColTinyInt02, SqlByte ColTinyInt03, SqlByte ColTinyInt04,
long ColBigInt01, long ColBigInt02,
string ColNvarchar01, string ColNvarchar02, string ColNvarchar03
)
{
// Buffer length: 1 for bitmap, 1 per tinyint, 8 per bigint, 2 * length per string, (string - 1) one byte delimiters
int bufLength = 1 + (4 * 1) + (2 * 8) + (sizeof(char) * (ColNvarchar01.Length + ColNvarchar02.Length + ColNvarchar03.Length)) + 3 - 1;
// Buffer (stack allocated)
byte* ptr = stackalloc byte[bufLength];
byte* current = ptr + 1; // Skip 1 byte of bitmap at the beginning;
// NULL bitmap (initially all bits unset)
BitVector32 bitmap = new BitVector32(0);
// Write TINYINTs
if (!ColTinyInt01.IsNull) { current[0] = ColTinyInt01.Value; } else { bitmap[1 << 00] = true; }
if (!ColTinyInt02.IsNull) { current[1] = ColTinyInt02.Value; } else { bitmap[1 << 01] = true; }
if (!ColTinyInt03.IsNull) { current[2] = ColTinyInt03.Value; } else { bitmap[1 << 02] = true; }
if (!ColTinyInt04.IsNull) { current[3] = ColTinyInt04.Value; } else { bitmap[1 << 03] = true; }
current += 4; // Wrote 4 bytes
// Write BIGINTs
long* biptr = (long*)current;
biptr[0] = ColBigInt01;
biptr[1] = ColBigInt02;
current += 16; // Wrote 16 byes
// Write strings
WriteStringToBuffer(in ColNvarchar01, ref current);
WriteStringToBuffer(in ColNvarchar02, ref current);
WriteStringToBuffer(in ColNvarchar03, ref current);
// Write completed bitmap
*ptr = (byte)bitmap.Data;
// SpookyHash
return SpookyHash.Hash128(ptr, bufLength);
// Local function
void WriteStringToBuffer(in string s, ref byte* buffer)
{
fixed (char* chptr = s)
{
char* cp = (char*)buffer;
for (int i = 0; i < s.Length; i++)
{
cp[i] = chptr[i];
}
buffer += sizeof(char) * s.Length;
*buffer = 0; // single-byte string delimiter
buffer++;
}
}
}
}
internal static class SpookyHash
{
private const ulong SpookyConst = 0xDEADBEEFDEADBEEF;
private const int NumVars = 12;
private const int BlockSize = NumVars * 8;
private const int BufSize = 2 * BlockSize;
internal static unsafe byte[] Hash128(void* ptr, int length)
{
if (length < BufSize)
{
return ShortHash(ptr, length);
}
ulong h0, h1, h2, h3, h4, h5, h6, h7, h8, h9, h10, h11;
h0 = h3 = h6 = h9 = 0;
h1 = h4 = h7 = h10 = 0;
h2 = h5 = h8 = h11 = SpookyConst;
var p64 = (ulong*)ptr;
ulong* end = p64 + ((length / BlockSize) * NumVars);
ulong* buf = stackalloc ulong[NumVars];
while (p64 < end)
{
h0 += p64[0]; h2 ^= h10; h11 ^= h0; h0 = h0 << 11 | h0 >> -11; h11 += h1;
h1 += p64[1]; h3 ^= h11; h0 ^= h1; h1 = h1 << 32 | h1 >> 32; h0 += h2;
h2 += p64[2]; h4 ^= h0; h1 ^= h2; h2 = h2 << 43 | h2 >> -43; h1 += h3;
h3 += p64[3]; h5 ^= h1; h2 ^= h3; h3 = h3 << 31 | h3 >> -31; h2 += h4;
h4 += p64[4]; h6 ^= h2; h3 ^= h4; h4 = h4 << 17 | h4 >> -17; h3 += h5;
h5 += p64[5]; h7 ^= h3; h4 ^= h5; h5 = h5 << 28 | h5 >> -28; h4 += h6;
h6 += p64[6]; h8 ^= h4; h5 ^= h6; h6 = h6 << 39 | h6 >> -39; h5 += h7;
h7 += p64[7]; h9 ^= h5; h6 ^= h7; h7 = h7 << 57 | h7 >> -57; h6 += h8;
h8 += p64[8]; h10 ^= h6; h7 ^= h8; h8 = h8 << 55 | h8 >> -55; h7 += h9;
h9 += p64[9]; h11 ^= h7; h8 ^= h9; h9 = h9 << 54 | h9 >> -54; h8 += h10;
h10 += p64[10]; h0 ^= h8; h9 ^= h10; h10 = h10 << 22 | h10 >> -22; h9 += h11;
h11 += p64[11]; h1 ^= h9; h10 ^= h11; h11 = h11 << 46 | h11 >> -46; h10 += h0;
p64 += NumVars;
}
int remainder = length - (int)((byte*)end - (byte*)ptr);
if (remainder != 0)
{
for (int i = 0; i < remainder; i++)
{
((byte*)buf)[i] = ((byte*)end)[i];
}
}
for (int i = remainder; i < BlockSize; i++)
{
((byte*)buf)[i] = 0;
}
((byte*)buf)[BlockSize - 1] = (byte)remainder;
h0 += buf[0]; h1 += buf[1]; h2 += buf[2]; h3 += buf[3];
h4 += buf[4]; h5 += buf[5]; h6 += buf[6]; h7 += buf[7];
h8 += buf[8]; h9 += buf[9]; h10 += buf[10]; h11 += buf[11];
h11 += h1; h2 ^= h11; h1 = h1 << 44 | h1 >> -44;
h0 += h2; h3 ^= h0; h2 = h2 << 15 | h2 >> -15;
h1 += h3; h4 ^= h1; h3 = h3 << 34 | h3 >> -34;
h2 += h4; h5 ^= h2; h4 = h4 << 21 | h4 >> -21;
h3 += h5; h6 ^= h3; h5 = h5 << 38 | h5 >> -38;
h4 += h6; h7 ^= h4; h6 = h6 << 33 | h6 >> -33;
h5 += h7; h8 ^= h5; h7 = h7 << 10 | h7 >> -10;
h6 += h8; h9 ^= h6; h8 = h8 << 13 | h8 >> -13;
h7 += h9; h10 ^= h7; h9 = h9 << 38 | h9 >> -38;
h8 += h10; h11 ^= h8; h10 = h10 << 53 | h10 >> -53;
h9 += h11; h0 ^= h9; h11 = h11 << 42 | h11 >> -42;
h10 += h0; h1 ^= h10; h0 = h0 << 54 | h0 >> -54;
h11 += h1; h2 ^= h11; h1 = h1 << 44 | h1 >> -44;
h0 += h2; h3 ^= h0; h2 = h2 << 15 | h2 >> -15;
h1 += h3; h4 ^= h1; h3 = h3 << 34 | h3 >> -34;
h2 += h4; h5 ^= h2; h4 = h4 << 21 | h4 >> -21;
h3 += h5; h6 ^= h3; h5 = h5 << 38 | h5 >> -38;
h4 += h6; h7 ^= h4; h6 = h6 << 33 | h6 >> -33;
h5 += h7; h8 ^= h5; h7 = h7 << 10 | h7 >> -10;
h6 += h8; h9 ^= h6; h8 = h8 << 13 | h8 >> -13;
h7 += h9; h10 ^= h7; h9 = h9 << 38 | h9 >> -38;
h8 += h10; h11 ^= h8; h10 = h10 << 53 | h10 >> -53;
h9 += h11; h0 ^= h9; h11 = h11 << 42 | h11 >> -42;
h10 += h0; h1 ^= h10; h0 = h0 << 54 | h0 >> -54;
h11 += h1; h2 ^= h11; h1 = h1 << 44 | h1 >> -44;
h0 += h2; h3 ^= h0; h2 = h2 << 15 | h2 >> -15;
h1 += h3; h4 ^= h1; h3 = h3 << 34 | h3 >> -34;
h2 += h4; h5 ^= h2; h4 = h4 << 21 | h4 >> -21;
h3 += h5; h6 ^= h3; h5 = h5 << 38 | h5 >> -38;
h4 += h6; h7 ^= h4; h6 = h6 << 33 | h6 >> -33;
h5 += h7; h8 ^= h5; h7 = h7 << 10 | h7 >> -10;
h6 += h8; h9 ^= h6; h8 = h8 << 13 | h8 >> -13;
h7 += h9; h10 ^= h7; h9 = h9 << 38 | h9 >> -38;
h8 += h10; h11 ^= h8; h10 = h10 << 53 | h10 >> -53;
h9 += h11; h0 ^= h9;
h10 += h0; h1 ^= h10; h0 = h0 << 54 | h0 >> -54;
byte[] ret = new byte[16];
fixed (byte* r = ret)
{
ulong* u = (ulong*)r;
u[1] = h1;
u[0] = h0;
}
return ret;
}
private static unsafe byte[] ShortHash(void* ptr, int length)
{
ulong* p64 = (ulong*)ptr;
ulong a = 0;
ulong b = 0;
ulong c = SpookyConst;
ulong d = SpookyConst;
ulong* end = p64 + ((length / 32) * 4);
int remainder = length & 31;
if (length > 15)
{
for (; p64 < end; p64 += 4)
{
c += p64[0];
d += p64[1];
c = c << 50 | c >> -50; c += d; a ^= c;
d = d << 52 | d >> -52; d += a; b ^= d;
a = a << 30 | a >> -30; a += b; c ^= a;
b = b << 41 | b >> -41; b += c; d ^= b;
c = c << 54 | c >> -54; c += d; a ^= c;
d = d << 48 | d >> -48; d += a; b ^= d;
a = a << 38 | a >> -38; a += b; c ^= a;
b = b << 37 | b >> -37; b += c; d ^= b;
c = c << 62 | c >> -62; c += d; a ^= c;
d = d << 34 | d >> -34; d += a; b ^= d;
a = a << 5 | a >> -5; a += b; c ^= a;
b = b << 36 | b >> -36; b += c; d ^= b;
a += p64[2];
b += p64[3];
}
if (remainder >= 16)
{
c += p64[0];
d += p64[1];
c = c << 50 | c >> -50; c += d; a ^= c;
d = d << 52 | d >> -52; d += a; b ^= d;
a = a << 30 | a >> -30; a += b; c ^= a;
b = b << 41 | b >> -41; b += c; d ^= b;
c = c << 54 | c >> -54; c += d; a ^= c;
d = d << 48 | d >> -48; d += a; b ^= d;
a = a << 38 | a >> -38; a += b; c ^= a;
b = b << 37 | b >> -37; b += c; d ^= b;
c = c << 62 | c >> -62; c += d; a ^= c;
d = d << 34 | d >> -34; d += a; b ^= d;
a = a << 5 | a >> -5; a += b; c ^= a;
b = b << 36 | b >> -36; b += c; d ^= b;
p64 += 2;
remainder -= 16;
}
}
d += ((ulong)length) << 56;
switch (remainder)
{
case 15:
d += ((ulong)((byte*)p64)[14]) << 48;
goto case 14;
case 14:
d += ((ulong)((byte*)p64)[13]) << 40;
goto case 13;
case 13:
d += ((ulong)((byte*)p64)[12]) << 32;
goto case 12;
case 12:
d += ((uint*)p64)[2];
c += p64[0];
break;
case 11:
d += ((ulong)((byte*)p64)[10]) << 16;
goto case 10;
case 10:
d += ((ulong)((byte*)p64)[9]) << 8;
goto case 9;
case 9:
d += (ulong)((byte*)p64)[8];
goto case 8;
case 8:
c += p64[0];
break;
case 7:
c += ((ulong)((byte*)p64)[6]) << 48;
goto case 6;
case 6:
c += ((ulong)((byte*)p64)[5]) << 40;
goto case 5;
case 5:
c += ((ulong)((byte*)p64)[4]) << 32;
goto case 4;
case 4:
c += ((uint*)p64)[0];
break;
case 3:
c += ((ulong)((byte*)p64)[2]) << 16;
goto case 2;
case 2:
c += ((ulong)((byte*)p64)[1]) << 8;
goto case 1;
case 1:
c += (ulong)((byte*)p64)[0];
break;
case 0:
c += SpookyConst;
d += SpookyConst;
break;
}
d ^= c; c = c << 15 | c >> -15; d += c;
a ^= d; d = d << 52 | d >> -52; a += d;
b ^= a; a = a << 26 | a >> -26; b += a;
c ^= b; b = b << 51 | b >> -51; c += b;
d ^= c; c = c << 28 | c >> -28; d += c;
a ^= d; d = d << 9 | d >> -9; a += d;
b ^= a; a = a << 47 | a >> -47; b += a;
c ^= b; b = b << 54 | b >> -54; c += b;
d ^= c; c = c << 32 | c >> -32; d += c;
a ^= d; d = d << 25 | d >> -25; a += d;
b ^= a; a = a << 63 | a >> -63; b += a;
byte[] ret = new byte[16];
fixed (byte* r = ret)
{
ulong* u = (ulong*)r;
u[1] = b;
u[0] = a;
}
return ret;
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment