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?
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.