NullableIntsToBinaryInline.sql

-- create sample data
DROP TABLE IF EXISTS dbo.TABLE_OF_32_INTS;

CREATE TABLE dbo.TABLE_OF_32_INTS (
    COL01 INT NULL,
    COL02 INT NULL,
    COL03 INT NULL,
    COL04 INT NULL,
    COL05 INT NULL,
    COL06 INT NULL,
    COL07 INT NULL,
    COL08 INT NULL,
    COL09 INT NULL,
    COL10 INT NULL,
    COL11 INT NULL,
    COL12 INT NULL,
    COL13 INT NULL,
    COL14 INT NULL,
    COL15 INT NULL,
    COL16 INT NULL,
    COL17 INT NULL,
    COL18 INT NULL,
    COL19 INT NULL,
    COL20 INT NULL,
    COL21 INT NULL,
    COL22 INT NULL,
    COL23 INT NULL,
    COL24 INT NULL,
    COL25 INT NULL,
    COL26 INT NULL,
    COL27 INT NULL,
    COL28 INT NULL,
    COL29 INT NULL,
    COL30 INT NULL,
    COL31 INT NULL,
    COL32 INT NULL
);

INSERT INTO dbo.TABLE_OF_32_INTS WITH (TABLOCK)
SELECT 0, 123, 12345, 1234567, 123456789
, 0, 123, 12345, 1234567, 123456789
, 0, 123, 12345, 1234567, 123456789
, 0, 123, 12345, 1234567, 123456789
, 0, 123, 12345, 1234567, 123456789
, 0, 123, 12345, 1234567, 123456789
, NULL, -876545321
FROM
(
    SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q
OPTION (MAXDOP 1);
GO

-- Enable CLR
EXEC sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

-- Turn on advanced options (necessary for next statement)
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

-- Since we're in DEV, disable clr strict security to avoid the need for certificates or signing
EXEC sp_configure 'clr strict security', 0;
GO
RECONFIGURE;
GO

DROP FUNCTION IF EXISTS dbo.NullableIntsToBinaryInline
DROP ASSEMBLY IF EXISTS ClrPlayground
GO

-- Create the assembly based on the dll generated by compiling the CLR project
-- You'll need to replace the path with your path to the DLL
CREATE ASSEMBLY ClrPlayground
FROM 'C:\scripts\AdventureWorksCLR\CLR\ClrPlayground\ClrPlayground\bin\Debug\ClrPlayground.dll'
WITH PERMISSION_SET = safe;
GO

--Create a function from the assembly
CREATE OR ALTER FUNCTION dbo.NullableIntsToBinaryInline (
	@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)
AS EXTERNAL NAME ClrPlayground.UserDefinedFunctions.NullableIntsToBinaryInline;
GO

SET STATISTICS TIME ON
GO

DECLARE @dummy BINARY(132)
SELECT @dummy = dbo.NullableIntsToBinaryInline(
	COL01,
	COL02,
	COL03,
	COL04,
	COL05,
	COL06,
	COL07,
	COL08,
	COL09,
	COL10,
	COL11,
	COL12,
	COL13,
	COL14,
	COL15,
	COL16,
	COL17,
	COL18,
	COL19,
	COL20,
	COL21,
	COL22,
	COL23,
	COL24,
	COL25,
	COL26,
	COL27,
	COL28,
	COL29,
	COL30,
	COL31,
	COL32
)
FROM dbo.TABLE_OF_32_INTS
OPTION (MAXDOP 1)
GO

-- For manual testing, review these results, switch some values to NULL and check the NULL bitmap, etc.
SELECT TOP 100 dbo.NullableIntsToBinaryInline(
	COL01,
	COL02,
	COL03,
	COL04,
	COL05,
	COL06,
	COL07,
	COL08,
	COL09,
	COL10,
	COL11,
	COL12,
	COL13,
	COL14,
	COL15,
	COL16,
	COL17,
	COL18,
	COL19,
	COL20,
	COL21,
	COL22,
	COL23,
	COL24,
	COL25,
	COL26,
	COL27,
	COL28,
	COL29,
	COL30,
	COL31,
	COL32
)
FROM dbo.TABLE_OF_32_INTS
OPTION (MAXDOP 1)
GO