Return to Answer

added 301 characters in body

edited May 30, 2019 at 16:14

96.2k
30
442
692

On my machine (SQL Server 2017) the following C# SQLCLR function runs about 30% faster than the binary(5) idea, 35% faster than CONCAT_WS, and in half the time of the self-answer.

It requires UNSAFE permission and uses pointers. The implementation is very specifically tied to the test data.

For testing purposes, the easiest way to get this unsafe assembly working is to set the database to TRUSTWORTHY and disable the clr strict security configuration option if necessary.

Compiled code

For convenience the CREATE ASSEMBLY compiled bits are at https://gist.github.com/SQLKiwi/72d01b661c74485900e7ebcfdc63ab8e

T-SQL Function Stub

CREATE FUNCTION dbo.NullableIntsToBinary ( @Col01 int, @Col02 int, @Col03 int, @Col04 int, @Col05 int, @Col06 int, @Col07 int, @Col08 int, @Col09 int, @Col10 int, @Col11 int, @Col12 int, @Col13 int, @Col14 int, @Col15 int, @Col16 int, @Col17 int, @Col18 int, @Col19 int, @Col20 int, @Col21 int, @Col22 int, @Col23 int, @Col24 int, @Col25 int, @Col26 int, @Col27 int, @Col28 int, @Col29 int, @Col30 int, @Col31 int, @Col32 int ) RETURNS binary(132) WITH EXECUTE AS CALLER AS EXTERNAL NAME Obbish.UserDefinedFunctions.NullableIntsToBinary;

Source code

The C# source is at https://gist.github.com/SQLKiwi/64f320fe7fd802a68a3a644aa8b8af9f

If you compile this for yourself, you must use a Class Library (.dll) as the target project type and check the Allow Unsafe Code build option.

Combined solution

Since you ultimately want to compute the SpookyHash of the binary data returned above, you can call SpookyHash within the CLR function and return the 16-byte hash.

An example implementation based on a table with a mixture of column data types is at https://gist.github.com/SQLKiwi/6f82582a4ad1920c372fac118ec82460. This includes an unsafe inlined version of the Spooky Hash algorithm derived from Jon Hanna's SpookilySharp and the original public domain C source code by Bob Jenkins.

On my machine (SQL Server 2017) the following C# SQLCLR function runs about 30% faster than the binary(5) idea, 35% faster than CONCAT_WS, and in half the time of the self-answer.

It requires UNSAFE permission and uses pointers. The implementation is very specifically tied to the test data.

For testing purposes, the easiest way to get this unsafe assembly working is to set the database to TRUSTWORTHY and disable the clr strict security configuration option if necessary.

Compiled code

For convenience the CREATE ASSEMBLY compiled bits are at https://gist.github.com/SQLKiwi/72d01b661c74485900e7ebcfdc63ab8e

T-SQL Function Stub

CREATE FUNCTION dbo.NullableIntsToBinary ( @Col01 int, @Col02 int, @Col03 int, @Col04 int, @Col05 int, @Col06 int, @Col07 int, @Col08 int, @Col09 int, @Col10 int, @Col11 int, @Col12 int, @Col13 int, @Col14 int, @Col15 int, @Col16 int, @Col17 int, @Col18 int, @Col19 int, @Col20 int, @Col21 int, @Col22 int, @Col23 int, @Col24 int, @Col25 int, @Col26 int, @Col27 int, @Col28 int, @Col29 int, @Col30 int, @Col31 int, @Col32 int ) RETURNS binary(132) WITH EXECUTE AS CALLER AS EXTERNAL NAME Obbish.UserDefinedFunctions.NullableIntsToBinary;

Source code

The C# source is at https://gist.github.com/SQLKiwi/64f320fe7fd802a68a3a644aa8b8af9f

If you compile this for yourself, you must use a Class Library (.dll) as the target project type and check the Allow Unsafe Code build option.

Combined solution

Since you ultimately want to compute the SpookyHash of the binary data returned above, you can call SpookyHash within the CLR function and return the 16-byte hash.

An example implementation based on a table with a mixture of column data types is at https://gist.github.com/SQLKiwi/6f82582a4ad1920c372fac118ec82460.

On my machine (SQL Server 2017) the following C# SQLCLR function runs about 30% faster than the binary(5) idea, 35% faster than CONCAT_WS, and in half the time of the self-answer.

It requires UNSAFE permission and uses pointers. The implementation is very specifically tied to the test data.

For testing purposes, the easiest way to get this unsafe assembly working is to set the database to TRUSTWORTHY and disable the clr strict security configuration option if necessary.

Compiled code

For convenience the CREATE ASSEMBLY compiled bits are at https://gist.github.com/SQLKiwi/72d01b661c74485900e7ebcfdc63ab8e

T-SQL Function Stub

CREATE FUNCTION dbo.NullableIntsToBinary ( @Col01 int, @Col02 int, @Col03 int, @Col04 int, @Col05 int, @Col06 int, @Col07 int, @Col08 int, @Col09 int, @Col10 int, @Col11 int, @Col12 int, @Col13 int, @Col14 int, @Col15 int, @Col16 int, @Col17 int, @Col18 int, @Col19 int, @Col20 int, @Col21 int, @Col22 int, @Col23 int, @Col24 int, @Col25 int, @Col26 int, @Col27 int, @Col28 int, @Col29 int, @Col30 int, @Col31 int, @Col32 int ) RETURNS binary(132) WITH EXECUTE AS CALLER AS EXTERNAL NAME Obbish.UserDefinedFunctions.NullableIntsToBinary;

Source code

The C# source is at https://gist.github.com/SQLKiwi/64f320fe7fd802a68a3a644aa8b8af9f

If you compile this for yourself, you must use a Class Library (.dll) as the target project type and check the Allow Unsafe Code build option.

Combined solution

Since you ultimately want to compute the SpookyHash of the binary data returned above, you can call SpookyHash within the CLR function and return the 16-byte hash.

Moved large code elements to gist

Source Link

edited May 30, 2019 at 15:54

Paul White ♦

96.2k
30
442
692

This is as fast as I have managed to get a C# SQLCLR function to go.

On my machine (SQL Server 2017) the following C# SQLCLR function runs about 30% faster than the binary(5) idea, 35% faster than CONCAT_WS, and in half the time of the self-answer.

For convenience: the CREATE ASSEMBLY compiled bits are at https://gist.github.com/SQLKiwi/72d01b661c74485900e7ebcfdc63ab8e

CREATE ASSEMBLY Obbish FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300C13F9C9E0000000000000000E00022200B013000001200000006000000000000F6310000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000A33100004F000000004000008003000000000000000000000000000000000000006000000C00000004310000380000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000FC110000002000000012000000020000000000000000000000000000200000602E7273726300000080030000004000000004000000140000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001800000000000000000000000000004000004200000000000000000000000000000000D731000000000000480000000200050058250000AC0B0000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000013300400F40400000100001120840000008D1A000001120016281300000A25250C2C05088E692D0516E00B2B0908168F1A000001E00B070D0F00281400000A2D0B090F00281500000A542B0912001717281600000A0F01281400000A2D0D091A580F01281500000A542B0912001817281600000A0F02281400000A2D100918D31A5A580F02281500000A542B0912001A17281600000A0F03281400000A2D100919D31A5A580F03281500000A542B0912001E17281600000A0F04281400000A2D10091AD31A5A580F04281500000A542B0A12001F1017281600000A0F05281400000A2D10091BD31A5A580F05281500000A542B0A12001F2017281600000A0F06281400000A2D10091CD31A5A580F06281500000A542B0A12001F4017281600000A0F07281400000A2D10091DD31A5A580F07281500000A542B0D1200208000000017281600000A0F08281400000A2D10091ED31A5A580F08281500000A542B0D1200200001000017281600000A0F09281400000A2D11091F09D31A5A580F09281500000A542B0D1200200002000017281600000A0F0A281400000A2D11091F0AD31A5A580F0A281500000A542B0D1200200004000017281600000A0F0B281400000A2D11091F0BD31A5A580F0B281500000A542B0D1200200008000017281600000A0F0C281400000A2D11091F0CD31A5A580F0C281500000A542B0D1200200010000017281600000A0F0D281400000A2D11091F0DD31A5A580F0D281500000A542B0D1200200020000017281600000A0F0E281400000A2D11091F0ED31A5A580F0E281500000A542B0D1200200040000017281600000A0F0F281400000A2D11091F0FD31A5A580F0F281500000A542B0D1200200080000017281600000A0F10281400000A2D11091F10D31A5A580F10281500000A542B0D1200200000010017281600000A0F11281400000A2D11091F11D31A5A580F11281500000A542B0D1200200000020017281600000A0F12281400000A2D11091F12D31A5A580F12281500000A542B0D1200200000040017281600000A0F13281400000A2D11091F13D31A5A580F13281500000A542B0D1200200000080017281600000A0F14281400000A2D11091F14D31A5A580F14281500000A542B0D1200200000100017281600000A0F15281400000A2D11091F15D31A5A580F15281500000A542B0D1200200000200017281600000A0F16281400000A2D11091F16D31A5A580F16281500000A542B0D1200200000400017281600000A0F17281400000A2D11091F17D31A5A580F17281500000A542B0D1200200000800017281600000A0F18281400000A2D11091F18D31A5A580F18281500000A542B0D1200200000000117281600000A0F19281400000A2D11091F19D31A5A580F19281500000A542B0D1200200000000217281600000A0F1A281400000A2D11091F1AD31A5A580F1A281500000A542B0D1200200000000417281600000A0F1B281400000A2D11091F1BD31A5A580F1B281500000A542B0D1200200000000817281600000A0F1C281400000A2D11091F1CD31A5A580F1C281500000A542B0D1200200000001017281600000A0F1D281400000A2D11091F1DD31A5A580F1D281500000A542B0D1200200000002017281600000A0F1E281400000A2D11091F1ED31A5A580F1E281500000A542B0D1200200000004017281600000A0F1F281400000A2D11091F1FD31A5A580F1F281500000A542B0D1200200000008017281600000A091F20D31A5A581200281700000A54140C2A1E02281800000A2A42534A4201000100000000000C00000076342E302E33303331390000000005006C0000003C030000237E0000A80300006004000023537472696E6773000000000808000004000000235553000C0800001000000023475549440000001C0800009003000023426C6F620000000000000002000001475502000900000000FA013300160000010000001A0000000200000002000000210000001800000011000000010000000100000001000000030000000000230301000000000006005602BD030600D502BD03060059018B030F00DD0300000600810158030600390258030600050258030600BC0258030600760258030600A102580306009801580306006D019E03060031019E030600CC0158030600B30102030600490301040600E901010406003F014E040600320442030A0024026A030A0022016A030A001C016A030A008F026A030A004F00EC030E004300FD000600F302420300000000D6000000000001000100010010001D0400004D000100010050200000000096003904E30001005025000000008618850306002200000000000000000001001300000002002B00000003005800000004006A00000005007C00000006008E0000000700A00000000800B20000000900C40000000A00010000000B00190000000C00310000000D005E0000000E00700000000F00820000001000940000001100A60000001200B80000001300CA00000014000700000015001F0000001600370000001700640000001800760000001900880000001A009A0000001B00AC0000001C00BE0000001D00D00000001E000D0000001F002500000020003D00090085030100110085030600190085030A00290085031000310085031000390085031000410085031000490085031000510085031000590085031000610085031500690085031000710085031000790085031000890085031A00910085030600A10085030600B90085030600C90085030100C1002E032C00C100F8023000C90039033400C900EB00300099008503060020008B000F022400930057032700830089032E000B0028012E00130031012E001B0050012E00230059012E002B006C012E0033006C012E003B006C012E00430059012E004B0072012E0053006C012E005B006C012E0063008A012E006B00B4012E007300C10108000600430020000480000001000000000000000000000000001C0300000400000000000000000000003A00F400000000000400000000000000000000003A00DF00000000000400000000000000000000003A004203000000000000000000436F6C313000436F6C323000436F6C333000436F6C303100436F6C313100436F6C323100436F6C333100436F6C303200436F6C313200436F6C323200436F6C333200426974566563746F7233320053716C496E74333200436F6C303300436F6C313300436F6C323300436F6C303400436F6C313400436F6C323400436F6C303500436F6C313500436F6C323500436F6C303600436F6C313600436F6C323600436F6C303700436F6C313700436F6C323700436F6C303800436F6C313800436F6C323800436F6C303900436F6C313900436F6C3239003C4D6F64756C653E0053797374656D2E44617461006765745F44617461006D73636F726C69620053797374656D2E436F6C6C656374696F6E732E5370656369616C697A65640053797374656D446174614163636573734B696E64004775696441747472696275746500556E76657269666961626C65436F64654174747269627574650044656275676761626C6541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C795469746C6541747472696275746500417373656D626C7954726164656D61726B417474726962757465005461726765744672616D65776F726B41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053656375726974795065726D697373696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E4174747269627574650053716C46756E6374696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E7341747472696275746500417373656D626C7950726F647563744174747269627574650053716C466163657441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C79436F6D70616E794174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650042797465006765745F56616C75650053797374656D2E52756E74696D652E56657273696F6E696E67004F6262697368004F62626973682E646C6C006765745F49734E756C6C007365745F4974656D0053797374656D005365637572697479416374696F6E0053797374656D2E5265666C656374696F6E004D6963726F736F66742E53716C5365727665722E536572766572002E63746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E496E7465726F7053657276696365730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C54797065730053797374656D2E53656375726974792E5065726D697373696F6E730055736572446566696E656446756E6374696F6E73004F626A656374004E756C6C61626C65496E7473546F42696E6172790053797374656D2E536563757269747900000000000000BD4A7B45200F244C90A9E9CD3AF35EB300042001010803200001052001011111042001010E04200101020520010111410B070411650F05451D050F08032000020320000805200201080208B77A5C561934E089809E2E01808453797374656D2E53656375726974792E5065726D697373696F6E732E53656375726974795065726D697373696F6E4174747269627574652C206D73636F726C69622C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391501540210536B6970566572696669636174696F6E014400201D05116111611161116111611161116111611161116111611161116111611161116111611161116111611161116111611161116111611161116111611161116111610801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000200000000001201000D436C6173734C69627261727932000005010000000017010012436F7079726967687420C2A920203230313900002901002436376433393864652D303534342D343136312D626561372D39353036373564313233646200000C010007312E302E302E3000004D01001C2E4E45544672616D65776F726B2C56657273696F6E3D76342E372E320100540E144672616D65776F726B446973706C61794E616D65142E4E4554204672616D65776F726B20342E372E328146010004005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E6973746963015402094973507265636973650131010003005408074D617853697A658400000054020A49734E756C6C61626C650054020D497346697865644C656E67746801040100000000000000000037D7D5B90000000002000000670000003C3100003C1300000000000000000000000000001000000000000000000000000000000052534453DF8CE89102760D4089AB4EC0C959B93601000000433A5C55736572735C7061756C775C736F757263655C7265706F735C436C6173734C696272617279325C436C6173734C696272617279325C6F626A5C52656C656173655C4F62626973682E70646200CB3100000000000000000000E5310000002000000000000000000000000000000000000000000000D7310000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C000000000000FF25002000100000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000240300000000000000000000240334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00484020000010053007400720069006E006700460069006C00650049006E0066006F0000006002000001003000300030003000300034006200300000001A000100010043006F006D006D0065006E007400730000000000000022000100010043006F006D00700061006E0079004E0061006D006500000000000000000044000E000100460069006C0065004400650073006300720069007000740069006F006E000000000043006C006100730073004C0069006200720061007200790032000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000036000B00010049006E007400650072006E0061006C004E0061006D00650000004F00620062006900730068002E0064006C006C00000000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100390000002A00010001004C006500670061006C00540072006100640065006D00610072006B00730000000000000000003E000B0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004F00620062006900730068002E0064006C006C00000000003C000E000100500072006F0064007500630074004E0061006D0065000000000043006C006100730073004C0069006200720061007200790032000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000F83100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 WITH PERMISSION_SET = UNSAFE;

using Microsoft.SqlServer.Server; using System.Collections.Specialized; using System.Data.SqlTypes; public partial class UserDefinedFunctions { [return: SqlFacet(MaxSize = 132, IsNullable = false, IsFixedLength = true)] [SqlFunction(DataAccess = DataAccessKind.None, SystemDataAccess = SystemDataAccessKind.None, IsDeterministic = true, IsPrecise = true)] public static byte[] NullableIntsToBinary ( SqlInt32 Col01, SqlInt32 Col02, SqlInt32 Col03, SqlInt32 Col04, SqlInt32 Col05, SqlInt32 Col06, SqlInt32 Col07, SqlInt32 Col08, SqlInt32 Col09, SqlInt32 Col10, SqlInt32 Col11, SqlInt32 Col12, SqlInt32 Col13, SqlInt32 Col14, SqlInt32 Col15, SqlInt32 Col16, SqlInt32 Col17, SqlInt32 Col18, SqlInt32 Col19, SqlInt32 Col20, SqlInt32 Col21, SqlInt32 Col22, SqlInt32 Col23, SqlInt32 Col24, SqlInt32 Col25, SqlInt32 Col26, SqlInt32 Col27, SqlInt32 Col28, SqlInt32 Col29, SqlInt32 Col30, SqlInt32 Col31, SqlInt32 Col32 ) { // The byte array to return (initialized to zeroes) byte[] buffer = new byte[132]; // NULL bitmap (initially all bits unset) BitVector32 bitmap = new BitVector32(0); unsafe { // Make sure the byte array doesn't move fixed (byte* ptr = buffer) { // Access as integer pointers int* iptr = (int*)ptr; // For each input column: // If not NULL copy the integer value into the buffer at the right point // Else set the corresponding null bitmap bit if (!Col01.IsNull) { iptr[00] = Col01.Value; } else { bitmap[1 << 00] = true; } if (!Col02.IsNull) { iptr[01] = Col02.Value; } else { bitmap[1 << 01] = true; } if (!Col03.IsNull) { iptr[02] = Col03.Value; } else { bitmap[1 << 02] = true; } if (!Col04.IsNull) { iptr[03] = Col04.Value; } else { bitmap[1 << 03] = true; } if (!Col05.IsNull) { iptr[04] = Col05.Value; } else { bitmap[1 << 04] = true; } if (!Col06.IsNull) { iptr[05] = Col06.Value; } else { bitmap[1 << 05] = true; } if (!Col07.IsNull) { iptr[06] = Col07.Value; } else { bitmap[1 << 06] = true; } if (!Col08.IsNull) { iptr[07] = Col08.Value; } else { bitmap[1 << 07] = true; } if (!Col09.IsNull) { iptr[08] = Col09.Value; } else { bitmap[1 << 08] = true; } if (!Col10.IsNull) { iptr[09] = Col10.Value; } else { bitmap[1 << 09] = true; } if (!Col11.IsNull) { iptr[10] = Col11.Value; } else { bitmap[1 << 10] = true; } if (!Col12.IsNull) { iptr[11] = Col12.Value; } else { bitmap[1 << 11] = true; } if (!Col13.IsNull) { iptr[12] = Col13.Value; } else { bitmap[1 << 12] = true; } if (!Col14.IsNull) { iptr[13] = Col14.Value; } else { bitmap[1 << 13] = true; } if (!Col15.IsNull) { iptr[14] = Col15.Value; } else { bitmap[1 << 14] = true; } if (!Col16.IsNull) { iptr[15] = Col16.Value; } else { bitmap[1 << 15] = true; } if (!Col17.IsNull) { iptr[16] = Col17.Value; } else { bitmap[1 << 16] = true; } if (!Col18.IsNull) { iptr[17] = Col18.Value; } else { bitmap[1 << 17] = true; } if (!Col19.IsNull) { iptr[18] = Col19.Value; } else { bitmap[1 << 18] = true; } if (!Col20.IsNull) { iptr[19] = Col20.Value; } else { bitmap[1 << 19] = true; } if (!Col21.IsNull) { iptr[20] = Col21.Value; } else { bitmap[1 << 20] = true; } if (!Col22.IsNull) { iptr[21] = Col22.Value; } else { bitmap[1 << 21] = true; } if (!Col23.IsNull) { iptr[22] = Col23.Value; } else { bitmap[1 << 22] = true; } if (!Col24.IsNull) { iptr[23] = Col24.Value; } else { bitmap[1 << 23] = true; } if (!Col25.IsNull) { iptr[24] = Col25.Value; } else { bitmap[1 << 24] = true; } if (!Col26.IsNull) { iptr[25] = Col26.Value; } else { bitmap[1 << 25] = true; } if (!Col27.IsNull) { iptr[26] = Col27.Value; } else { bitmap[1 << 26] = true; } if (!Col28.IsNull) { iptr[27] = Col28.Value; } else { bitmap[1 << 27] = true; } if (!Col29.IsNull) { iptr[28] = Col29.Value; } else { bitmap[1 << 28] = true; } if (!Col30.IsNull) { iptr[29] = Col30.Value; } else { bitmap[1 << 29] = true; } if (!Col31.IsNull) { iptr[30] = Col31.Value; } else { bitmap[1 << 30] = true; } if (!Col32.IsNull) { iptr[31] = Col32.Value; } else { bitmap[1 << 31] = true; } // Write completed NULL bitmap into the buffer iptr[32] = bitmap.Data; } } return buffer; } }

The C# source is at https://gist.github.com/SQLKiwi/64f320fe7fd802a68a3a644aa8b8af9f

Since you ultimately want to compute the SpookyHash of the binary data returned above, you can call SpookyHash within the CLR function and return the 16-byte hash.

An example implementation based on a table with a mixture of column data types is inat https://gist.github.com/SQLKiwi/6f82582a4ad1920c372fac118ec82460.

This is as fast as I have managed to get a C# SQLCLR function to go.

On my machine (SQL Server 2017) runs about 30% faster than the binary(5) idea, 35% faster than CONCAT_WS, and in half the time of the self-answer.

For convenience:

CREATE ASSEMBLY Obbish FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300C13F9C9E0000000000000000E00022200B013000001200000006000000000000F6310000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000A33100004F000000004000008003000000000000000000000000000000000000006000000C00000004310000380000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000FC110000002000000012000000020000000000000000000000000000200000602E7273726300000080030000004000000004000000140000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001800000000000000000000000000004000004200000000000000000000000000000000D731000000000000480000000200050058250000AC0B0000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000013300400F40400000100001120840000008D1A000001120016281300000A25250C2C05088E692D0516E00B2B0908168F1A000001E00B070D0F00281400000A2D0B090F00281500000A542B0912001717281600000A0F01281400000A2D0D091A580F01281500000A542B0912001817281600000A0F02281400000A2D100918D31A5A580F02281500000A542B0912001A17281600000A0F03281400000A2D100919D31A5A580F03281500000A542B0912001E17281600000A0F04281400000A2D10091AD31A5A580F04281500000A542B0A12001F1017281600000A0F05281400000A2D10091BD31A5A580F05281500000A542B0A12001F2017281600000A0F06281400000A2D10091CD31A5A580F06281500000A542B0A12001F4017281600000A0F07281400000A2D10091DD31A5A580F07281500000A542B0D1200208000000017281600000A0F08281400000A2D10091ED31A5A580F08281500000A542B0D1200200001000017281600000A0F09281400000A2D11091F09D31A5A580F09281500000A542B0D1200200002000017281600000A0F0A281400000A2D11091F0AD31A5A580F0A281500000A542B0D1200200004000017281600000A0F0B281400000A2D11091F0BD31A5A580F0B281500000A542B0D1200200008000017281600000A0F0C281400000A2D11091F0CD31A5A580F0C281500000A542B0D1200200010000017281600000A0F0D281400000A2D11091F0DD31A5A580F0D281500000A542B0D1200200020000017281600000A0F0E281400000A2D11091F0ED31A5A580F0E281500000A542B0D1200200040000017281600000A0F0F281400000A2D11091F0FD31A5A580F0F281500000A542B0D1200200080000017281600000A0F10281400000A2D11091F10D31A5A580F10281500000A542B0D1200200000010017281600000A0F11281400000A2D11091F11D31A5A580F11281500000A542B0D1200200000020017281600000A0F12281400000A2D11091F12D31A5A580F12281500000A542B0D1200200000040017281600000A0F13281400000A2D11091F13D31A5A580F13281500000A542B0D1200200000080017281600000A0F14281400000A2D11091F14D31A5A580F14281500000A542B0D1200200000100017281600000A0F15281400000A2D11091F15D31A5A580F15281500000A542B0D1200200000200017281600000A0F16281400000A2D11091F16D31A5A580F16281500000A542B0D1200200000400017281600000A0F17281400000A2D11091F17D31A5A580F17281500000A542B0D1200200000800017281600000A0F18281400000A2D11091F18D31A5A580F18281500000A542B0D1200200000000117281600000A0F19281400000A2D11091F19D31A5A580F19281500000A542B0D1200200000000217281600000A0F1A281400000A2D11091F1AD31A5A580F1A281500000A542B0D1200200000000417281600000A0F1B281400000A2D11091F1BD31A5A580F1B281500000A542B0D1200200000000817281600000A0F1C281400000A2D11091F1CD31A5A580F1C281500000A542B0D1200200000001017281600000A0F1D281400000A2D11091F1DD31A5A580F1D281500000A542B0D1200200000002017281600000A0F1E281400000A2D11091F1ED31A5A580F1E281500000A542B0D1200200000004017281600000A0F1F281400000A2D11091F1FD31A5A580F1F281500000A542B0D1200200000008017281600000A091F20D31A5A581200281700000A54140C2A1E02281800000A2A42534A4201000100000000000C00000076342E302E33303331390000000005006C0000003C030000237E0000A80300006004000023537472696E6773000000000808000004000000235553000C0800001000000023475549440000001C0800009003000023426C6F620000000000000002000001475502000900000000FA013300160000010000001A0000000200000002000000210000001800000011000000010000000100000001000000030000000000230301000000000006005602BD030600D502BD03060059018B030F00DD0300000600810158030600390258030600050258030600BC0258030600760258030600A102580306009801580306006D019E03060031019E030600CC0158030600B30102030600490301040600E901010406003F014E040600320442030A0024026A030A0022016A030A001C016A030A008F026A030A004F00EC030E004300FD000600F302420300000000D6000000000001000100010010001D0400004D000100010050200000000096003904E30001005025000000008618850306002200000000000000000001001300000002002B00000003005800000004006A00000005007C00000006008E0000000700A00000000800B20000000900C40000000A00010000000B00190000000C00310000000D005E0000000E00700000000F00820000001000940000001100A60000001200B80000001300CA00000014000700000015001F0000001600370000001700640000001800760000001900880000001A009A0000001B00AC0000001C00BE0000001D00D00000001E000D0000001F002500000020003D00090085030100110085030600190085030A00290085031000310085031000390085031000410085031000490085031000510085031000590085031000610085031500690085031000710085031000790085031000890085031A00910085030600A10085030600B90085030600C90085030100C1002E032C00C100F8023000C90039033400C900EB00300099008503060020008B000F022400930057032700830089032E000B0028012E00130031012E001B0050012E00230059012E002B006C012E0033006C012E003B006C012E00430059012E004B0072012E0053006C012E005B006C012E0063008A012E006B00B4012E007300C10108000600430020000480000001000000000000000000000000001C0300000400000000000000000000003A00F400000000000400000000000000000000003A00DF00000000000400000000000000000000003A004203000000000000000000436F6C313000436F6C323000436F6C333000436F6C303100436F6C313100436F6C323100436F6C333100436F6C303200436F6C313200436F6C323200436F6C333200426974566563746F7233320053716C496E74333200436F6C303300436F6C313300436F6C323300436F6C303400436F6C313400436F6C323400436F6C303500436F6C313500436F6C323500436F6C303600436F6C313600436F6C323600436F6C303700436F6C313700436F6C323700436F6C303800436F6C313800436F6C323800436F6C303900436F6C313900436F6C3239003C4D6F64756C653E0053797374656D2E44617461006765745F44617461006D73636F726C69620053797374656D2E436F6C6C656374696F6E732E5370656369616C697A65640053797374656D446174614163636573734B696E64004775696441747472696275746500556E76657269666961626C65436F64654174747269627574650044656275676761626C6541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C795469746C6541747472696275746500417373656D626C7954726164656D61726B417474726962757465005461726765744672616D65776F726B41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053656375726974795065726D697373696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E4174747269627574650053716C46756E6374696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E7341747472696275746500417373656D626C7950726F647563744174747269627574650053716C466163657441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C79436F6D70616E794174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650042797465006765745F56616C75650053797374656D2E52756E74696D652E56657273696F6E696E67004F6262697368004F62626973682E646C6C006765745F49734E756C6C007365745F4974656D0053797374656D005365637572697479416374696F6E0053797374656D2E5265666C656374696F6E004D6963726F736F66742E53716C5365727665722E536572766572002E63746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E496E7465726F7053657276696365730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C54797065730053797374656D2E53656375726974792E5065726D697373696F6E730055736572446566696E656446756E6374696F6E73004F626A656374004E756C6C61626C65496E7473546F42696E6172790053797374656D2E536563757269747900000000000000BD4A7B45200F244C90A9E9CD3AF35EB300042001010803200001052001011111042001010E04200101020520010111410B070411650F05451D050F08032000020320000805200201080208B77A5C561934E089809E2E01808453797374656D2E53656375726974792E5065726D697373696F6E732E53656375726974795065726D697373696F6E4174747269627574652C206D73636F726C69622C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391501540210536B6970566572696669636174696F6E014400201D05116111611161116111611161116111611161116111611161116111611161116111611161116111611161116111611161116111611161116111611161116111610801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000200000000001201000D436C6173734C69627261727932000005010000000017010012436F7079726967687420C2A920203230313900002901002436376433393864652D303534342D343136312D626561372D39353036373564313233646200000C010007312E302E302E3000004D01001C2E4E45544672616D65776F726B2C56657273696F6E3D76342E372E320100540E144672616D65776F726B446973706C61794E616D65142E4E4554204672616D65776F726B20342E372E328146010004005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E6973746963015402094973507265636973650131010003005408074D617853697A658400000054020A49734E756C6C61626C650054020D497346697865644C656E67746801040100000000000000000037D7D5B90000000002000000670000003C3100003C1300000000000000000000000000001000000000000000000000000000000052534453DF8CE89102760D4089AB4EC0C959B93601000000433A5C55736572735C7061756C775C736F757263655C7265706F735C436C6173734C696272617279325C436C6173734C696272617279325C6F626A5C52656C656173655C4F62626973682E70646200CB3100000000000000000000E5310000002000000000000000000000000000000000000000000000D7310000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C000000000000FF25002000100000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000240300000000000000000000240334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00484020000010053007400720069006E006700460069006C00650049006E0066006F0000006002000001003000300030003000300034006200300000001A000100010043006F006D006D0065006E007400730000000000000022000100010043006F006D00700061006E0079004E0061006D006500000000000000000044000E000100460069006C0065004400650073006300720069007000740069006F006E000000000043006C006100730073004C0069006200720061007200790032000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000036000B00010049006E007400650072006E0061006C004E0061006D00650000004F00620062006900730068002E0064006C006C00000000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100390000002A00010001004C006500670061006C00540072006100640065006D00610072006B00730000000000000000003E000B0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004F00620062006900730068002E0064006C006C00000000003C000E000100500072006F0064007500630074004E0061006D0065000000000043006C006100730073004C0069006200720061007200790032000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000F83100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 WITH PERMISSION_SET = UNSAFE;

using Microsoft.SqlServer.Server; using System.Collections.Specialized; using System.Data.SqlTypes; public partial class UserDefinedFunctions { [return: SqlFacet(MaxSize = 132, IsNullable = false, IsFixedLength = true)] [SqlFunction(DataAccess = DataAccessKind.None, SystemDataAccess = SystemDataAccessKind.None, IsDeterministic = true, IsPrecise = true)] public static byte[] NullableIntsToBinary ( SqlInt32 Col01, SqlInt32 Col02, SqlInt32 Col03, SqlInt32 Col04, SqlInt32 Col05, SqlInt32 Col06, SqlInt32 Col07, SqlInt32 Col08, SqlInt32 Col09, SqlInt32 Col10, SqlInt32 Col11, SqlInt32 Col12, SqlInt32 Col13, SqlInt32 Col14, SqlInt32 Col15, SqlInt32 Col16, SqlInt32 Col17, SqlInt32 Col18, SqlInt32 Col19, SqlInt32 Col20, SqlInt32 Col21, SqlInt32 Col22, SqlInt32 Col23, SqlInt32 Col24, SqlInt32 Col25, SqlInt32 Col26, SqlInt32 Col27, SqlInt32 Col28, SqlInt32 Col29, SqlInt32 Col30, SqlInt32 Col31, SqlInt32 Col32 ) { // The byte array to return (initialized to zeroes) byte[] buffer = new byte[132]; // NULL bitmap (initially all bits unset) BitVector32 bitmap = new BitVector32(0); unsafe { // Make sure the byte array doesn't move fixed (byte* ptr = buffer) { // Access as integer pointers int* iptr = (int*)ptr; // For each input column: // If not NULL copy the integer value into the buffer at the right point // Else set the corresponding null bitmap bit if (!Col01.IsNull) { iptr[00] = Col01.Value; } else { bitmap[1 << 00] = true; } if (!Col02.IsNull) { iptr[01] = Col02.Value; } else { bitmap[1 << 01] = true; } if (!Col03.IsNull) { iptr[02] = Col03.Value; } else { bitmap[1 << 02] = true; } if (!Col04.IsNull) { iptr[03] = Col04.Value; } else { bitmap[1 << 03] = true; } if (!Col05.IsNull) { iptr[04] = Col05.Value; } else { bitmap[1 << 04] = true; } if (!Col06.IsNull) { iptr[05] = Col06.Value; } else { bitmap[1 << 05] = true; } if (!Col07.IsNull) { iptr[06] = Col07.Value; } else { bitmap[1 << 06] = true; } if (!Col08.IsNull) { iptr[07] = Col08.Value; } else { bitmap[1 << 07] = true; } if (!Col09.IsNull) { iptr[08] = Col09.Value; } else { bitmap[1 << 08] = true; } if (!Col10.IsNull) { iptr[09] = Col10.Value; } else { bitmap[1 << 09] = true; } if (!Col11.IsNull) { iptr[10] = Col11.Value; } else { bitmap[1 << 10] = true; } if (!Col12.IsNull) { iptr[11] = Col12.Value; } else { bitmap[1 << 11] = true; } if (!Col13.IsNull) { iptr[12] = Col13.Value; } else { bitmap[1 << 12] = true; } if (!Col14.IsNull) { iptr[13] = Col14.Value; } else { bitmap[1 << 13] = true; } if (!Col15.IsNull) { iptr[14] = Col15.Value; } else { bitmap[1 << 14] = true; } if (!Col16.IsNull) { iptr[15] = Col16.Value; } else { bitmap[1 << 15] = true; } if (!Col17.IsNull) { iptr[16] = Col17.Value; } else { bitmap[1 << 16] = true; } if (!Col18.IsNull) { iptr[17] = Col18.Value; } else { bitmap[1 << 17] = true; } if (!Col19.IsNull) { iptr[18] = Col19.Value; } else { bitmap[1 << 18] = true; } if (!Col20.IsNull) { iptr[19] = Col20.Value; } else { bitmap[1 << 19] = true; } if (!Col21.IsNull) { iptr[20] = Col21.Value; } else { bitmap[1 << 20] = true; } if (!Col22.IsNull) { iptr[21] = Col22.Value; } else { bitmap[1 << 21] = true; } if (!Col23.IsNull) { iptr[22] = Col23.Value; } else { bitmap[1 << 22] = true; } if (!Col24.IsNull) { iptr[23] = Col24.Value; } else { bitmap[1 << 23] = true; } if (!Col25.IsNull) { iptr[24] = Col25.Value; } else { bitmap[1 << 24] = true; } if (!Col26.IsNull) { iptr[25] = Col26.Value; } else { bitmap[1 << 25] = true; } if (!Col27.IsNull) { iptr[26] = Col27.Value; } else { bitmap[1 << 26] = true; } if (!Col28.IsNull) { iptr[27] = Col28.Value; } else { bitmap[1 << 27] = true; } if (!Col29.IsNull) { iptr[28] = Col29.Value; } else { bitmap[1 << 28] = true; } if (!Col30.IsNull) { iptr[29] = Col30.Value; } else { bitmap[1 << 29] = true; } if (!Col31.IsNull) { iptr[30] = Col31.Value; } else { bitmap[1 << 30] = true; } if (!Col32.IsNull) { iptr[31] = Col32.Value; } else { bitmap[1 << 31] = true; } // Write completed NULL bitmap into the buffer iptr[32] = bitmap.Data; } } return buffer; } }

Since you ultimately want to compute the SpookyHash of the binary data returned above, you can call SpookyHash within the CLR function and return the 16-byte hash. An example implementation based on a table with a mixture of column data types is in https://gist.github.com/SQLKiwi/6f82582a4ad1920c372fac118ec82460.

On my machine (SQL Server 2017) the following C# SQLCLR function runs about 30% faster than the binary(5) idea, 35% faster than CONCAT_WS, and in half the time of the self-answer.

For convenience the CREATE ASSEMBLY compiled bits are at https://gist.github.com/SQLKiwi/72d01b661c74485900e7ebcfdc63ab8e

The C# source is at https://gist.github.com/SQLKiwi/64f320fe7fd802a68a3a644aa8b8af9f

Since you ultimately want to compute the SpookyHash of the binary data returned above, you can call SpookyHash within the CLR function and return the 16-byte hash.

An example implementation based on a table with a mixture of column data types is at https://gist.github.com/SQLKiwi/6f82582a4ad1920c372fac118ec82460.