adw merge join working.sql

-- Create one million rows
IF OBJECT_ID('dbo.numbers') IS NOT NULL DROP TABLE dbo.numbers
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
GO

CREATE TABLE dbo.Numbers (
    Number  INT NOT NULL
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN,     --!!TODO try distibuting?
    --CLUSTERED INDEX ( Number )
    CLUSTERED COLUMNSTORE INDEX
)
GO

-- Generate numbers table
DECLARE @UpperLimit INT = 1000000;

;WITH n AS
(
    SELECT
        x = ROW_NUMBER() OVER ( ORDER BY s1.[object_id] )
    FROM       sys.all_objects AS s1
    CROSS JOIN sys.all_objects AS s2
    CROSS JOIN sys.all_objects AS s3
)
SELECT x
INTO #tmp
FROM n
WHERE x BETWEEN 1 AND @UpperLimit
GO



INSERT INTO dbo.Numbers ( Number )
SELECT x
FROM #tmp
GO

CREATE STATISTICS _stat_Numbers__Number ON dbo.Numbers ( Number )
GO
:exit

EXPLAIN
SELECT *
FROM dbo.Numbers a
	INNER JOIN 
		(
		SELECT TOP 10 *
		FROM dbo.Numbers 
		) b ON a.Number = b.Number
OPTION ( MERGE JOIN )



EXPLAIN
SELECT *
FROM dbo.Numbers a
	INNER JOIN 
		(
		SELECT DISTINCT TOP 10 *
		FROM dbo.Numbers 
		) b ON a.Number = b.Number
OPTION ( MERGE JOIN )