DROP TABLE IF EXISTS dbo.People;

CREATE TABLE dbo.People (
    PersonID bigint PRIMARY KEY,
    FirstName varchar(100) NOT NULL,
    LastName varchar(100) NOT NULL,
    SSN varchar(11)
        MASKED WITH (FUNCTION = 'default()') NULL
);

INSERT INTO dbo.People WITH (TABLOCK)
SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, REPLICATE('A', 10)
, REPLICATE('Z', 12)
, RIGHT('000' + CAST(ABS(CHECKSUM(NewId())) AS VARCHAR(11)), 3)
     + '-' + RIGHT('00' + CAST(ABS(CHECKSUM(NewId())) AS VARCHAR(11)), 2)
     + '-' + RIGHT('0000' + CAST(ABS(CHECKSUM(NewId())) AS VARCHAR(11)), 4)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

-- new columns
ALTER TABLE People ADD SSN1 AS ISNULL(CAST(SUBSTRING(SSN, 1, 3) AS INT), 0) PERSISTED;
ALTER TABLE People ADD SSN2 AS ISNULL(CAST(SUBSTRING(SSN, 5, 2) AS INT), 0) PERSISTED;
ALTER TABLE People ADD SSN3 AS ISNULL(CAST(SUBSTRING(SSN, 8, 4) AS INT), 0) PERSISTED;


SELECT TOP (10000)
    ISNULL(CAST(RN AS INT), 0) NUM
INTO #t_10000
FROM
(
    SELECT -1 + ROW_NUMBER()
        OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) t;

CREATE CLUSTERED INDEX CI ON #t_10000 (NUM);

CREATE TABLE #BATCH_MODE_PLZ (
    I INT
    , INDEX C CLUSTERED COLUMNSTORE
);

DROP TABLE IF EXISTS #t;


SELECT
     p.PersonID
       , p.FirstName
       , p.LastName
       , t1000.NUM
        + '-' + t100.NUM
        + '-' + t10000.NUM AS SSN
    into #t
FROM dbo.People p
LEFT OUTER JOIN #t_10000 t1000
    ON p.SSN1 = t1000.NUM
LEFT OUTER JOIN #t_10000 t100
    ON p.SSN2 = t100.NUM
LEFT OUTER JOIN #t_10000 t10000
    ON p.SSN3 = t10000.NUM
LEFT OUTER JOIN #BATCH_MODE_PLZ ON 1 = 0
OPTION (QUERYTRACEON 176);