Методы материализации T-SQL CTE не работают на SQL Server 2012

Question

Я должен использовать следующие методы для материализации моих CTE и повышения производительности просмотра:

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

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

Теперь ни один из этих способов не работает. Кто-нибудь сталкивался с такой же проблемой или знает, если в SQL Server 2012 эти вещи не действительны?

Answer 1

Вы можете попробовать использовать многошаговую табличную функцию. Таким образом, сервер вынужден материализовать результаты TVF в табличную переменную. Кроме того, вы можете попробовать использовать декларативные ограничения при объявлении этого типа таблицы (PRIMARY KEY, UNIQUE, CHECK), чтобы повысить производительность окончательного запроса:

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;

Не забудьте добавить ORDER BY пункт к вашему последнему запросу.

Недавно я использовал это решение для оптимизации представления (ViewA, DISTINCT + LEFT JOIN + GETDATE()), используемого другими представлениями (ViewB). В этом случае (ViewA) было невозможно создать индексированное представление (из-за DISTINCT + LEFT JOIN + GETDATE()). Вместо этого я создал TVF с несколькими утверждениями, который улучшил производительность за счет уменьшения логического чтения (в некоторых случаях радикально) окончательного запроса.

Примечание. Конечно, вы можете попробовать использовать индексное представление.