Daniels bidrag till SQLUG Challenge 2017

--- En dummy-tabell med clustered columnstore index.
--- Jag gör LEFT JOIN #ColStoreTable ON 1=0 på flera ställen för
--- att få till s.k. batch mode processing.
---
--- http://sqlmag.com/sql-server/what-you-need-know-about-batch-mode-window-aggregate-operator-sql-server-2016-part-1
---
CREATE TABLE #ColStoreTable (
    col     tinyint NOT NULL
);
CREATE CLUSTERED COLUMNSTORE INDEX ColStoreIndex ON #ColStoreTable;
INSERT INTO #ColStoreTable VALUES (0);
ALTER INDEX ColStoreIndex ON #ColStoreTable REORGANIZE;



BEGIN TRANSACTION;

    TRUNCATE TABLE dbo.Customers;


    --- Steg 1 av 2:
    WITH mappings AS (
        SELECT ISNULL(b._from, a.PurchaseID) AS _from, MIN(c._to) AS _to
        FROM (

            --- För varje inköp, hitta det lägsta PurchaseID som har samma kombination av
            --- (förnamn, mail), (förnamn, adress) respektive (förnamn, kreditkort).
            SELECT p.PurchaseID,
                   MIN(p.PurchaseID) OVER (PARTITION BY p.FirstName, p.Email) AS _mailID,
                   MIN(p.PurchaseID) OVER (PARTITION BY p.FirstName, p.LastName, p.StreetAddress, p.ZipCode) AS _addrID,
                   MIN(p.PurchaseID) OVER (PARTITION BY p.FirstName, p.CreditCard) AS _ccID
            FROM dbo.Purchases AS p
            LEFT JOIN #ColStoreTable AS x ON 1=0
            ) AS a
        OUTER APPLY (

            --- "Unpivot"era dessa fyra värden:
            SELECT _from FROM (
                VALUES
                    (a._mailID),
                    (a._addrID),
                    (a._ccID),
                    (a.PurchaseID)
                ) AS sub(_from)
            WHERE _include=1) AS b
        OUTER APPLY (

            --- Ta fram det lägsta av (_mailID, _addrID och _ccID)
            VALUES ((CASE
                WHEN a._ccID  <=a._mailID AND a._ccID  <=a._addrID THEN a._ccID
                WHEN a._mailID<=a._ccID   AND a._mailID<=a._addrID THEN a._mailID
                WHEN a._addrID<=a._mailID AND a._addrID<=a._ccID   THEN a._addrID END))
            ) AS c(_to)

        --- DISTINCT'a utdatan för att eliminera dubletter som uppstår:
        GROUP BY ISNULL(b._from, a.PurchaseID)
        )

    --- Dumpa ut allting till dbo.Customers.
    INSERT INTO dbo.Customers WITH (TABLOCKX, HOLDLOCK) (PurchaseID, CustomerID)
    SELECT _from AS PurchaseID, _to AS CustomerID
    FROM mappings;



    --- I dbo.Customers ligger det nu i princip en parent-child-struktur
    --- där en parent (CustomerID) kan ha flera children (PurchaseID). Det vi
    --- vill göra nu är att alla parents ska ligga på "toppnivån".



    --- Steg 2 av 2:
    --- Rekursiv CTE som mappar alla relationer vidare "uppåt" i hierarkin:
    WITH cte AS (
        --- Utgå från de relationer som antas vara toppnoder just nu
        --- (PurchaseID=CustomerID).
        SELECT PurchaseID, CustomerID
        FROM dbo.Customers WITH (TABLOCKX, HOLDLOCK)
        WHERE PurchaseID=CustomerID

        UNION ALL

        --- Mappa dem till en ny parent-nod.
        SELECT p.PurchaseID, cte.CustomerID
        FROM cte
        INNER JOIN dbo.Customers AS p WITH (TABLOCKX, HOLDLOCK) ON
            p.PurchaseID>cte.PurchaseID AND
            p.CustomerID=cte.PurchaseID AND
            p.CustomerID!=p.PurchaseID)

    --- Uppdatera tillbaka till dbo.Customers i förekommande fall.
    --- Tvingar en HASH join för att bättra på prestanda med batch mode-processning
    --- och sätter MAXRECURSION till 0 ifall någon relation har mer än 100 nivåer (köp)
    --- till toppnoden.
    UPDATE c
    SET c.CustomerID=cte.CustomerID
    FROM dbo.Customers AS c WITH (TABLOCKX, HOLDLOCK)
    INNER HASH JOIN cte ON
        cte.PurchaseID!=cte.CustomerID
        AND c.PurchaseID=cte.PurchaseID
    LEFT JOIN #ColStoreTable AS cs ON 1=0
    WHERE c.CustomerID!=cte.CustomerID
    OPTION (MAXRECURSION 0);

--- Vild chansning: Om DELAYED_DURABILITY är påslagen för databasen så gör
--- vi det bästa av det här:
COMMIT TRANSACTION WITH (DELAYED_DURABILITY=ON);

DROP TABLE #ColStoreTable;