Za4SGn5Y

1INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
2SELECT maybe_new_rows.ID
3FROM dbo.A_HEAP_OF_MOSTLY_NEW_ROWS maybe_new_rows
4WHERE NOT EXISTS (
5    SELECT 1
6    FROM dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR halloween
7    WHERE maybe_new_rows.ID = halloween.ID
8)
9OPTION (MAXDOP 1, QUERYTRACEON 7470); 
10     
11DROP TABLE IF EXISTS #CONSULTANT_RECOMMENDED_TEMP_TABLE;
12CREATE TABLE #CONSULTANT_RECOMMENDED_TEMP_TABLE (
13    ID BIGINT,
14    PRIMARY KEY (ID)
15);
16
17INSERT INTO #CONSULTANT_RECOMMENDED_TEMP_TABLE WITH (TABLOCK)
18SELECT maybe_new_rows.ID
19FROM dbo.A_HEAP_OF_MOSTLY_NEW_ROWS maybe_new_rows
20WHERE NOT EXISTS (
21    SELECT 1
22    FROM dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR halloween
23    WHERE maybe_new_rows.ID = halloween.ID
24)
25OPTION (MAXDOP 1, QUERYTRACEON 7470);
26
27INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
28SELECT new_rows.ID
29FROM #CONSULTANT_RECOMMENDED_TEMP_TABLE new_rows
30OPTION (MAXDOP 1); 
31     
32╔═════════════╦═════════════╦══════════════╗
33â•‘  operator   â•‘ first query â•‘ second query â•‘
34╠═════════════╬═════════════╬══════════════╣
35â•‘ big scan    â•‘ 1771        â•‘ 1744         â•‘
36â•‘ little scan â•‘ 163         â•‘ 166          â•‘
37â•‘ sort        â•‘ 531         â•‘ 530          â•‘
38â•‘ merge join  â•‘ 709         â•‘ 669          â•‘
39â•‘ spool       â•‘ 3202        â•‘ N/A          â•‘
40â•‘ temp insert â•‘ N/A         â•‘ 422          â•‘
41â•‘ temp scan   â•‘ N/A         â•‘ 187          â•‘
42â•‘ insert      â•‘ 3122        â•‘ 1545         â•‘
43╚═════════════╩═════════════╩══════════════╝ 
44     
45DROP TABLE IF EXISTS dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR;
46
47CREATE TABLE dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR (
48ID BIGINT NOT NULL,
49PRIMARY KEY (ID)
50);
51
52INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
53SELECT TOP (20000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
54FROM master..spt_values t1
55CROSS JOIN master..spt_values t2
56CROSS JOIN master..spt_values t3
57OPTION (MAXDOP 1);
58
59
60DROP TABLE IF EXISTS dbo.A_HEAP_OF_MOSTLY_NEW_ROWS;
61
62CREATE TABLE dbo.A_HEAP_OF_MOSTLY_NEW_ROWS (
63ID BIGINT NOT NULL
64);
65
66INSERT INTO dbo.A_HEAP_OF_MOSTLY_NEW_ROWS WITH (TABLOCK)
67SELECT TOP (1900000) 19999999 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
68FROM master..spt_values t1
69CROSS JOIN master..spt_values t2;