FKXDSruy

1DROP TABLE IF EXISTS #CHANGED_IDS;
2
3SELECT stg.ID INTO #CHANGED_IDS
4FROM (
5    SELECT ID,
6    CAST( HASHBYTES ('SHA2_256', 
7        CAST(FK1 AS NVARCHAR(19)) + 
8        CAST(FK2 AS NVARCHAR(19)) + 
9        CAST(FK3 AS NVARCHAR(19)) + 
10        CAST(FK4 AS NVARCHAR(19)) + 
11        CAST(FK5 AS NVARCHAR(19)) + 
12        CAST(FK6 AS NVARCHAR(19)) + 
13        CAST(FK7 AS NVARCHAR(19)) + 
14        CAST(FK8 AS NVARCHAR(19)) + 
15        CAST(FK9 AS NVARCHAR(19)) + 
16        CAST(FK10 AS NVARCHAR(19)) + 
17        CAST(FK11 AS NVARCHAR(19)) + 
18        CAST(FK12 AS NVARCHAR(19)) + 
19        CAST(FK13 AS NVARCHAR(19)) + 
20        CAST(FK14 AS NVARCHAR(19)) + 
21        CAST(FK15 AS NVARCHAR(19)) + 
22        CAST(STR1 AS NVARCHAR(500)) +
23        CAST(STR2 AS NVARCHAR(500)) +
24        CAST(STR3 AS NVARCHAR(500)) +
25        CAST(STR4 AS NVARCHAR(500)) +
26        CAST(STR5 AS NVARCHAR(500)) +
27        CAST(COMP1 AS NVARCHAR(1)) + 
28        CAST(COMP2 AS NVARCHAR(1)) + 
29        CAST(COMP3 AS NVARCHAR(1)) + 
30        CAST(COMP4 AS NVARCHAR(1)) + 
31        CAST(COMP5 AS NVARCHAR(1)))
32     AS BINARY(32)) HASH1
33    FROM HB_TBL WITH (TABLOCK)
34) stg
35INNER JOIN (
36    SELECT ID,
37    CAST(HASHBYTES ('SHA2_256', 
38        CAST(FK1 AS NVARCHAR(19)) + 
39        CAST(FK2 AS NVARCHAR(19)) + 
40        CAST(FK3 AS NVARCHAR(19)) + 
41        CAST(FK4 AS NVARCHAR(19)) + 
42        CAST(FK5 AS NVARCHAR(19)) + 
43        CAST(FK6 AS NVARCHAR(19)) + 
44        CAST(FK7 AS NVARCHAR(19)) + 
45        CAST(FK8 AS NVARCHAR(19)) + 
46        CAST(FK9 AS NVARCHAR(19)) + 
47        CAST(FK10 AS NVARCHAR(19)) + 
48        CAST(FK11 AS NVARCHAR(19)) + 
49        CAST(FK12 AS NVARCHAR(19)) + 
50        CAST(FK13 AS NVARCHAR(19)) + 
51        CAST(FK14 AS NVARCHAR(19)) + 
52        CAST(FK15 AS NVARCHAR(19)) + 
53        CAST(STR1 AS NVARCHAR(500)) +
54        CAST(STR2 AS NVARCHAR(500)) +
55        CAST(STR3 AS NVARCHAR(500)) +
56        CAST(STR4 AS NVARCHAR(500)) +
57        CAST(STR5 AS NVARCHAR(500)) +
58        CAST(COMP1 AS NVARCHAR(1)) + 
59        CAST(COMP2 AS NVARCHAR(1)) + 
60        CAST(COMP3 AS NVARCHAR(1)) + 
61        CAST(COMP4 AS NVARCHAR(1)) + 
62        CAST(COMP5 AS NVARCHAR(1)) )
63 AS BINARY(32)) HASH1
64    FROM HB_TBL_2 WITH (TABLOCK)
65) rpt ON rpt.ID = stg.ID
66WHERE rpt.HASH1 <> stg.HASH1
67OPTION (MAXDOP 8);
68	
69DROP TABLE IF EXISTS HB_TBL;
70
71CREATE TABLE dbo.HB_TBL (
72    ID BIGINT NOT NULL,
73    FK1 BIGINT NOT NULL,
74    FK2 BIGINT NOT NULL,
75    FK3 BIGINT NOT NULL,
76    FK4 BIGINT NOT NULL,
77    FK5 BIGINT NOT NULL,
78    FK6 BIGINT NOT NULL,
79    FK7 BIGINT NOT NULL,
80    FK8 BIGINT NOT NULL,
81    FK9 BIGINT NOT NULL,
82    FK10 BIGINT NOT NULL,
83    FK11 BIGINT NOT NULL,
84    FK12 BIGINT NOT NULL,
85    FK13 BIGINT NOT NULL,
86    FK14 BIGINT NOT NULL,
87    FK15 BIGINT NOT NULL,
88    STR1 NVARCHAR(500) NOT NULL,
89    STR2 NVARCHAR(500) NOT NULL,
90    STR3 NVARCHAR(500) NOT NULL,
91    STR4 NVARCHAR(500) NOT NULL,
92    STR5 NVARCHAR(500) NOT NULL,
93    COMP1 TINYINT NOT NULL,
94    COMP2 TINYINT NOT NULL,
95    COMP3 TINYINT NOT NULL,
96    COMP4 TINYINT NOT NULL,
97    COMP5 TINYINT NOT NULL
98);
99
100
101INSERT INTO HB_TBL WITH (TABLOCK)
102SELECT RN,
103RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000,
104RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000,
105RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000,
106REPLICATE(CHAR(65 + RN % 10 ), 30),REPLICATE(CHAR(65 + RN % 10 ), 30),REPLICATE(CHAR(65 + RN % 10 ), 30),REPLICATE(CHAR(65 + RN % 10 ), 30),REPLICATE(CHAR(65 + RN % 10 ), 30),
1070,1,0,1,0
108FROM (
109    SELECT TOP (40000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
110    FROM master..spt_values t1
111    CROSS JOIN master..spt_values t2
112    CROSS JOIN master..spt_values t3
113) q
114OPTION (MAXDOP 1);
115
116DROP TABLE IF EXISTS HB_TBL_2;
117
118CREATE TABLE dbo.HB_TBL_2 (
119    ID BIGINT NOT NULL,
120    FK1 BIGINT NOT NULL,
121    FK2 BIGINT NOT NULL,
122    FK3 BIGINT NOT NULL,
123    FK4 BIGINT NOT NULL,
124    FK5 BIGINT NOT NULL,
125    FK6 BIGINT NOT NULL,
126    FK7 BIGINT NOT NULL,
127    FK8 BIGINT NOT NULL,
128    FK9 BIGINT NOT NULL,
129    FK10 BIGINT NOT NULL,
130    FK11 BIGINT NOT NULL,
131    FK12 BIGINT NOT NULL,
132    FK13 BIGINT NOT NULL,
133    FK14 BIGINT NOT NULL,
134    FK15 BIGINT NOT NULL,
135    STR1 NVARCHAR(500) NOT NULL,
136    STR2 NVARCHAR(500) NOT NULL,
137    STR3 NVARCHAR(500) NOT NULL,
138    STR4 NVARCHAR(500) NOT NULL,
139    STR5 NVARCHAR(500) NOT NULL,
140    COMP1 TINYINT NOT NULL,
141    COMP2 TINYINT NOT NULL,
142    COMP3 TINYINT NOT NULL,
143    COMP4 TINYINT NOT NULL,
144    COMP5 TINYINT NOT NULL,
145    INDEX CCI99 CLUSTERED COLUMNSTORE
146);
147
148INSERT INTO HB_TBL_2 WITH (TABLOCK)
149SELECT RN,
150RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000,
151RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000,
152RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000,
153REPLICATE(CHAR(65 + RN % 10 ), 30),REPLICATE(CHAR(65 + RN % 10 ), 30),REPLICATE(CHAR(65 + RN % 10 ), 30),REPLICATE(CHAR(65 + RN % 10 ), 30),REPLICATE(CHAR(65 + RN % 10 ), 30),
1540,1,0,1,0
155FROM (
156    SELECT TOP (40000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
157    FROM master..spt_values t1
158    CROSS JOIN master..spt_values t2
159    CROSS JOIN master..spt_values t3
160) q
161OPTION (MAXDOP 1);