xTie5mnt

1CAST(COL1 AS BINARY(4)) + CAST(COL2 AS BINARY(4)) + .. 
2     
3-- create sample data
4DROP TABLE IF EXISTS dbo.TABLE_OF_32_INTS;
5
6CREATE TABLE dbo.TABLE_OF_32_INTS (
7    COL1 INT NULL,
8    COL2 INT NULL,
9    COL3 INT NULL,
10    COL4 INT NULL,
11    COL5 INT NULL,
12    COL6 INT NULL,
13    COL7 INT NULL,
14    COL8 INT NULL,
15    COL9 INT NULL,
16    COL10 INT NULL,
17    COL11 INT NULL,
18    COL12 INT NULL,
19    COL13 INT NULL,
20    COL14 INT NULL,
21    COL15 INT NULL,
22    COL16 INT NULL,
23    COL17 INT NULL,
24    COL18 INT NULL,
25    COL19 INT NULL,
26    COL20 INT NULL,
27    COL21 INT NULL,
28    COL22 INT NULL,
29    COL23 INT NULL,
30    COL24 INT NULL,
31    COL25 INT NULL,
32    COL26 INT NULL,
33    COL27 INT NULL,
34    COL28 INT NULL,
35    COL29 INT NULL,
36    COL30 INT NULL,
37    COL31 INT NULL,
38    COL32 INT NULL
39);
40
41INSERT INTO dbo.TABLE_OF_32_INTS WITH (TABLOCK)
42SELECT 0, 123, 12345, 1234567, 123456789
43, 0, 123, 12345, 1234567, 123456789
44, 0, 123, 12345, 1234567, 123456789
45, 0, 123, 12345, 1234567, 123456789
46, 0, 123, 12345, 1234567, 123456789
47, 0, 123, 12345, 1234567, 123456789
48, NULL, -876545321
49FROM
50(
51    SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
52    FROM master..spt_values t1
53    CROSS JOIN master..spt_values t2
54) q
55OPTION (MAXDOP 1);
56
57
58GO
59
60
61-- procedure to test performance
62CREATE OR ALTER PROCEDURE #p AS 
63BEGIN
64
65SET NOCOUNT ON;
66
67DECLARE
68@counter INT = 0,
69@dummy VARBINARY(8000);
70
71WHILE @counter < 10
72BEGIN
73    SELECT @dummy = -- this code is clearly incomplete as it does not handle NULLs
74        CAST(COL1 AS BINARY(4)) + 
75        CAST(COL2 AS BINARY(4)) + 
76        CAST(COL3 AS BINARY(4)) + 
77        CAST(COL4 AS BINARY(4)) + 
78        CAST(COL5 AS BINARY(4)) + 
79        CAST(COL6 AS BINARY(4)) + 
80        CAST(COL7 AS BINARY(4)) + 
81        CAST(COL8 AS BINARY(4)) + 
82        CAST(COL9 AS BINARY(4)) + 
83        CAST(COL10 AS BINARY(4)) + 
84        CAST(COL11 AS BINARY(4)) + 
85        CAST(COL12 AS BINARY(4)) + 
86        CAST(COL13 AS BINARY(4)) + 
87        CAST(COL14 AS BINARY(4)) + 
88        CAST(COL15 AS BINARY(4)) + 
89        CAST(COL16 AS BINARY(4)) + 
90        CAST(COL17 AS BINARY(4)) + 
91        CAST(COL18 AS BINARY(4)) + 
92        CAST(COL19 AS BINARY(4)) + 
93        CAST(COL20 AS BINARY(4)) + 
94        CAST(COL21 AS BINARY(4)) + 
95        CAST(COL22 AS BINARY(4)) + 
96        CAST(COL23 AS BINARY(4)) + 
97        CAST(COL24 AS BINARY(4)) + 
98        CAST(COL25 AS BINARY(4)) + 
99        CAST(COL26 AS BINARY(4)) + 
100        CAST(COL27 AS BINARY(4)) + 
101        CAST(COL28 AS BINARY(4)) + 
102        CAST(COL29 AS BINARY(4)) + 
103        CAST(COL30 AS BINARY(4)) + 
104        CAST(COL31 AS BINARY(4)) + 
105        CAST(COL32 AS BINARY(4))
106    FROM dbo.TABLE_OF_32_INTS
107    OPTION (MAXDOP 1);
108
109    SET @counter = @counter + 1;
110END;
111
112SELECT cpu_time
113FROM sys.dm_exec_requests
114WHERE session_id = @@SPID;
115
116END;
117
118GO
119
120-- run procedure
121EXEC #p;