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.
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:
CREATE ASSEMBLY Obbish FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300C13F9C9E0000000000000000E00022200B013000001200000006000000000000F6310000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000A33100004F000000004000008003000000000000000000000000000000000000006000000C00000004310000380000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000FC110000002000000012000000020000000000000000000000000000200000602E7273726300000080030000004000000004000000140000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001800000000000000000000000000004000004200000000000000000000000000000000D731000000000000480000000200050058250000AC0B0000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000013300400F40400000100001120840000008D1A000001120016281300000A25250C2C05088E692D0516E00B2B0908168F1A000001E00B070D0F00281400000A2D0B090F00281500000A542B0912001717281600000A0F01281400000A2D0D091A580F01281500000A542B0912001817281600000A0F02281400000A2D100918D31A5A580F02281500000A542B0912001A17281600000A0F03281400000A2D100919D31A5A580F03281500000A542B0912001E17281600000A0F04281400000A2D10091AD31A5A580F04281500000A542B0A12001F1017281600000A0F05281400000A2D10091BD31A5A580F05281500000A542B0A12001F2017281600000A0F06281400000A2D10091CD31A5A580F06281500000A542B0A12001F4017281600000A0F07281400000A2D10091DD31A5A580F07281500000A542B0D1200208000000017281600000A0F08281400000A2D10091ED31A5A580F08281500000A542B0D1200200001000017281600000A0F09281400000A2D11091F09D31A5A580F09281500000A542B0D1200200002000017281600000A0F0A281400000A2D11091F0AD31A5A580F0A281500000A542B0D1200200004000017281600000A0F0B281400000A2D11091F0BD31A5A580F0B281500000A542B0D1200200008000017281600000A0F0C281400000A2D11091F0CD31A5A580F0C281500000A542B0D1200200010000017281600000A0F0D281400000A2D11091F0DD31A5A580F0D281500000A542B0D1200200020000017281600000A0F0E281400000A2D11091F0ED31A5A580F0E281500000A542B0D1200200040000017281600000A0F0F281400000A2D11091F0FD31A5A580F0F281500000A542B0D1200200080000017281600000A0F10281400000A2D11091F10D31A5A580F10281500000A542B0D1200200000010017281600000A0F11281400000A2D11091F11D31A5A580F11281500000A542B0D1200200000020017281600000A0F12281400000A2D11091F12D31A5A580F12281500000A542B0D1200200000040017281600000A0F13281400000A2D11091F13D31A5A580F13281500000A542B0D1200200000080017281600000A0F14281400000A2D11091F14D31A5A580F14281500000A542B0D1200200000100017281600000A0F15281400000A2D11091F15D31A5A580F15281500000A542B0D1200200000200017281600000A0F16281400000A2D11091F16D31A5A580F16281500000A542B0D1200200000400017281600000A0F17281400000A2D11091F17D31A5A580F17281500000A542B0D1200200000800017281600000A0F18281400000A2D11091F18D31A5A580F18281500000A542B0D1200200000000117281600000A0F19281400000A2D11091F19D31A5A580F19281500000A542B0D1200200000000217281600000A0F1A281400000A2D11091F1AD31A5A580F1A281500000A542B0D1200200000000417281600000A0F1B281400000A2D11091F1BD31A5A580F1B281500000A542B0D1200200000000817281600000A0F1C281400000A2D11091F1CD31A5A580F1C281500000A542B0D1200200000001017281600000A0F1D281400000A2D11091F1DD31A5A580F1D281500000A542B0D1200200000002017281600000A0F1E281400000A2D11091F1ED31A5A580F1E281500000A542B0D1200200000004017281600000A0F1F281400000A2D11091F1FD31A5A580F1F281500000A542B0D1200200000008017281600000A091F20D31A5A581200281700000A54140C2A1E02281800000A2A42534A4201000100000000000C00000076342E302E33303331390000000005006C0000003C030000237E0000A80300006004000023537472696E6773000000000808000004000000235553000C0800001000000023475549440000001C0800009003000023426C6F620000000000000002000001475502000900000000FA013300160000010000001A0000000200000002000000210000001800000011000000010000000100000001000000030000000000230301000000000006005602BD030600D502BD03060059018B030F00DD0300000600810158030600390258030600050258030600BC0258030600760258030600A102580306009801580306006D019E03060031019E030600CC0158030600B30102030600490301040600E901010406003F014E040600320442030A0024026A030A0022016A030A001C016A030A008F026A030A004F00EC030E004300FD000600F302420300000000D6000000000001000100010010001D0400004D000100010050200000000096003904E30001005025000000008618850306002200000000000000000001001300000002002B00000003005800000004006A00000005007C00000006008E0000000700A00000000800B20000000900C40000000A00010000000B00190000000C00310000000D005E0000000E00700000000F00820000001000940000001100A60000001200B80000001300CA00000014000700000015001F0000001600370000001700640000001800760000001900880000001A009A0000001B00AC0000001C00BE0000001D00D00000001E000D0000001F002500000020003D00090085030100110085030600190085030A00290085031000310085031000390085031000410085031000490085031000510085031000590085031000610085031500690085031000710085031000790085031000890085031A00910085030600A10085030600B90085030600C90085030100C1002E032C00C100F8023000C90039033400C900EB00300099008503060020008B000F022400930057032700830089032E000B0028012E00130031012E001B0050012E00230059012E002B006C012E0033006C012E003B006C012E00430059012E004B0072012E0053006C012E005B006C012E0063008A012E006B00B4012E007300C10108000600430020000480000001000000000000000000000000001C0300000400000000000000000000003A00F400000000000400000000000000000000003A00DF00000000000400000000000000000000003A004203000000000000000000436F6C313000436F6C323000436F6C333000436F6C303100436F6C313100436F6C323100436F6C333100436F6C303200436F6C313200436F6C323200436F6C333200426974566563746F7233320053716C496E74333200436F6C303300436F6C313300436F6C323300436F6C303400436F6C313400436F6C323400436F6C303500436F6C313500436F6C323500436F6C303600436F6C313600436F6C323600436F6C303700436F6C313700436F6C323700436F6C303800436F6C313800436F6C323800436F6C303900436F6C313900436F6C3239003C4D6F64756C653E0053797374656D2E44617461006765745F44617461006D73636F726C69620053797374656D2E436F6C6C656374696F6E732E5370656369616C697A65640053797374656D446174614163636573734B696E64004775696441747472696275746500556E76657269666961626C65436F64654174747269627574650044656275676761626C6541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C795469746C6541747472696275746500417373656D626C7954726164656D61726B417474726962757465005461726765744672616D65776F726B41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053656375726974795065726D697373696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E4174747269627574650053716C46756E6374696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E7341747472696275746500417373656D626C7950726F647563744174747269627574650053716C466163657441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C79436F6D70616E794174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650042797465006765745F56616C75650053797374656D2E52756E74696D652E56657273696F6E696E67004F6262697368004F62626973682E646C6C006765745F49734E756C6C007365745F4974656D0053797374656D005365637572697479416374696F6E0053797374656D2E5265666C656374696F6E004D6963726F736F66742E53716C5365727665722E536572766572002E63746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E496E7465726F7053657276696365730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C54797065730053797374656D2E53656375726974792E5065726D697373696F6E730055736572446566696E656446756E6374696F6E73004F626A656374004E756C6C61626C65496E7473546F42696E6172790053797374656D2E536563757269747900000000000000BD4A7B45200F244C90A9E9CD3AF35EB300042001010803200001052001011111042001010E04200101020520010111410B070411650F05451D050F08032000020320000805200201080208B77A5C561934E089809E2E01808453797374656D2E53656375726974792E5065726D697373696F6E732E53656375726974795065726D697373696F6E4174747269627574652C206D73636F726C69622C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391501540210536B6970566572696669636174696F6E014400201D05116111611161116111611161116111611161116111611161116111611161116111611161116111611161116111611161116111611161116111611161116111610801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000200000000001201000D436C6173734C69627261727932000005010000000017010012436F7079726967687420C2A920203230313900002901002436376433393864652D303534342D343136312D626561372D39353036373564313233646200000C010007312E302E302E3000004D01001C2E4E45544672616D65776F726B2C56657273696F6E3D76342E372E320100540E144672616D65776F726B446973706C61794E616D65142E4E4554204672616D65776F726B20342E372E328146010004005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E6973746963015402094973507265636973650131010003005408074D617853697A658400000054020A49734E756C6C61626C650054020D497346697865644C656E67746801040100000000000000000037D7D5B90000000002000000670000003C3100003C1300000000000000000000000000001000000000000000000000000000000052534453DF8CE89102760D4089AB4EC0C959B93601000000433A5C55736572735C7061756C775C736F757263655C7265706F735C436C6173734C696272617279325C436C6173734C696272617279325C6F626A5C52656C656173655C4F62626973682E70646200CB3100000000000000000000E5310000002000000000000000000000000000000000000000000000D7310000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C000000000000FF25002000100000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000240300000000000000000000240334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00484020000010053007400720069006E006700460069006C00650049006E0066006F0000006002000001003000300030003000300034006200300000001A000100010043006F006D006D0065006E007400730000000000000022000100010043006F006D00700061006E0079004E0061006D006500000000000000000044000E000100460069006C0065004400650073006300720069007000740069006F006E000000000043006C006100730073004C0069006200720061007200790032000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000036000B00010049006E007400650072006E0061006C004E0061006D00650000004F00620062006900730068002E0064006C006C00000000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100390000002A00010001004C006500670061006C00540072006100640065006D00610072006B00730000000000000000003E000B0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004F00620062006900730068002E0064006C006C00000000003C000E000100500072006F0064007500630074004E0061006D0065000000000043006C006100730073004C0069006200720061007200790032000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000F83100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 WITH PERMISSION_SET = UNSAFE; 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
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; } } 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.