sql-servert-sqlsql-server-2012common-table-expressionmaterialized

T-SQL CTE materializing techniques not working on SQL Server 2012


I have to use the following techniques to materialized my CTEs and increase the view performance:

WITH CTE AS(
    SELECT TOP 100 PERCENT
    ORDER BY ...
)

WITH CTE AS(
    SELECT TOP 2147483647
    ORDER BY ...
)

Now, neither of this ways works. Has anyone face the same issue or know if in SQL Server 2012 this things are not valid?


Solution

  • You could try using a multi-step table valued function. This way, the server is forced to materialize the TVF's results into a table variable. Also, you could try using declarative constraints when declaring the this table type (PRIMARY KEY, UNIQUE, CHECK) to improve the performance of the final query:

    CREATE FUNCTION CocoJamboSchema.CocoJamboFunction(@parameters ...)
    RETURNS @Results TABLE (
        Col1 INT NOT NULL,
        Col2 VARCHAR(10) NULL,
        ...
        PRIMARY KEY(Col1)
    )
    AS
    BEGIN
        WITH MyCTE (...)
        AS
        (
            ...
        )
        INSERT @Results (...)
            FROM MyCTE;
    
        RETURN;
    END;
    
    SELECT ...
    FROM CocoJamboSchema.CocoJamboFunction(param values) f
    INNER JOIN MySchema.MyTable t ON f.Col1=t.Col1
    ORDER BY t.Col1;
    

    Don't forget to add the ORDER BY clause to your final query.

    Recently, I used this solution to optimize a view (ViewA, DISTINCT + LEFT JOIN + GETDATE()) used by another views (ViewB). In this case (ViewA) was impossible to create a indexed view (because of DISTINCT + LEFT JOIN + GETDATE()). Instead, I created a multi-statement TVF that improved the performance by reducing the logical reads (drasticaly in some cases) of the final query.

    Note: Off course, you could try using an index view.