Использование CTE несколько раз в процедуре SQL: решение

Быстрый ответ

WITH cte AS (
    -- Здесь объявляем наше общее табличное выражение
)
SELECT * FROM cte  -- Первый вызов
UNION ALL
SELECT * FROM cte; -- Второй вызов

Для многократного использования общего табличного выражения (Common Table Expression, CTE) в рамках одного запроса, вы можете воспользоваться операцией UNION ALL.

Сказ о превращении общих табличных выражений в наших верных помощниках

Сохранение результатов работы CTE для последующего использования

Вы можете заранее сохранить результаты работы общего табличного выражения для упрощения дальнейшего использования:

WITH cte AS (
    -- Объявляем наше общее табличное выражение
)
SELECT * INTO #TempTable FROM cte;

-- Работаем с временной таблицей
SELECT * FROM #TempTable;
SELECT COUNT(*) FROM #TempTable;

Подробнее об этом расскажет наш спикер на видео

Открываем мир возможностей с табличными функциями

Табличные функции (Inline Table-Valued Functions, Inline TVF) позволяют вам использовать CTE в совершенно новом ключе:

CREATE FUNCTION dbo.GetCteData()
RETURNS TABLE AS
RETURN (
    -- Объявление CTE здесь
)
-- Использование функции:
SELECT * FROM dbo.GetCteData();

Повторное написание кода теперь не нужно: преобразуйте ваше общее табличное выражение в функцию, которая сделает работу за вас.

Упрощаем подсчет строк и ускоряем процесс

Сочетание ROW_NUMBER() и COUNT() сделает реализацию пагинации и подсчета общего числа строк намного проще:

WITH cte AS (
    SELECT *,
           ROW_NUMBER() OVER(ORDER BY SomeColumn) AS RowNum,
           COUNT(1) OVER() AS TotalCount
    FROM SomeTable
)
SELECT *, TotalCount
FROM cte
WHERE RowNum BETWEEN @StartRow AND @EndRow;

Профессиональные советы: Продвинутое использование CTE

Поддержание производительности и согласованности: дуэт в вашем арсенале

Для оптимизации производительности CTE:

• Регулярно тестируйте хранимые процедуры с различными параметрами, чтобы избежать неожиданных сюрпризов.
• Придерживайтесь ясного именования CTE, чтобы облегчить понимание и поддержку кода.

Пункт контроля и аватара героя

• Будьте внимательны к ссылкам на CTE для обеспечения корректной работы.
• Проверяйте связи и условия в CTE, чтобы избежать ошибок и неожиданностей.

Меньше – это больше: упрощаем использование COUNT с CTE

Делаем все максимально просто и определяем, что именно мы подсчитываем:

WITH cte AS (
    -- Объявление CTE здесь
)
SELECT COUNT(*) FROM cte;

Визуализация

Представьте себе ящик с инструментами (🧰), из которого вы можете вытаскивать нужные инструменты для разных проектов.

Если использовать CTE один раз — это как взять инструмент для одноразового использования: ToDo – Починить кран!:

WITH Toolbox AS (SELECT 🔨, 🔧, 🪛 FROM 🧰)
SELECT * FROM Toolbox;

При многократном использовании CTE представляется, словно у вас есть несколько клонированных ящиков с инструментами для выполнения различных задач.

Преодоление границ: преодоление ограничений в CTE

Будьте готовы к системным ограничениям

Стоит знать о системных ограничениях вашей базы данных: CTE могут иметь ограничения на количество ссылок.

Соблюдайте целостность данных

Внимательно следите за зависимостями данных. Помните о возможном влиянии множественного использования логики CTE на целостность данных.

Работайте с вычислениями в CTE

При необходимости пагинации, не стесняйтесь включать соответствующие вычисления в CTE.

Гарантируйте последовательность для пагинации

Получите упорядоченность данных с помощью функции ROW_NUMBER():

WITH OrderedCTE AS (
    SELECT *, ROW_NUMBER() OVER(ORDER BY KeyColumn) AS RN
)
SELECT * FROM OrderedCTE WHERE RN BETWEEN @Start AND @End;

Полезные материалы

1. Как удалить дубликаты строк? – Stack Overflow — Методы борьбы с дублированием данных, которые могут возникнуть при работе с CTE.
2. Настройка оповещений и уведомлений оператора SQL Server через электронную почту – MSSQLTips — Информация о лучших практиках управления SQL Server. Материал хоть и относится не непосредственно к CTE, но крайне полезен для профессиональной работы с SQL Server.