T-SQL CTE materializing techniques not working on

Question

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?

Answer 1

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.