T-SQL | CTE-выражения

См. также:

CTE – Common Table Expression – виртуальное представление данных, которое можно использовать, не оформляя хранимой процедурой / функцией или представлением данных (View).

Если для возврата данных удобно строить несколько запросов, каждый из которых решает свою подзадачу, то вам наверняка приходилось строить несколько вьюх, включая результирующую, которая отбирает конечный набор данных.

Подобную задачу можно решить также с помощью CTE-выражений (с версии MS SQL 2005).

CTE позволяет внутри одного запроса строить подзапросы – временные результаты обработки данных, к которым в этом же выражении можно обращаться:

WITH CTE_name (FieldName1, FieldName2) AS

(

SELECT Field1, Field2 FROM Table1

)

SELECT * FROM CTE_name

При больших объемах данных и связей между ними, с помощью CTE-выражений можно оптимизировать время выгрузки, например, если разбить логику отбора данных на CTE, вставить в каждый требуемые фильтры и потом CTE-выражения связать:

WITH CTE_name_1 (FieldName1, FieldName2) AS

(

SELECT Field1 AS ID, Field2 FROM Table1

WHERE Field2 >= 1000

CTE_name_2 (FieldName3, FieldName4) AS

(

SELECT Field3 AS ID, Field4 FROM Table2

WHERE Field4 = 'Москва'

)

SELECT * FROM CTE_name_1 INNER JOIN CTE_name_2 ON CTE_name_2.ID = CTE_name_1.ID

См. также: Пример оптимизации выгрузки данных

Преимущество CTE-выражения, что данные подзапроса при многократном использовании возвращаются быстрее (по сравнению с традиционным SELECT-подзапросом), т.к. CTE компелируется только при первом вызове. Это особенно актуально, если к одному и тому же подзапросу требуется обратиться больше одного раза.

Результат CTE-выражения можно использовать со всеми стандартными операторами DML (SELECT, INSERT, DELETE, UPDATE), также его удобно использовать для рекурсивных вычислений (например, таких как представлены в примерах Рекурсивные функции).

См. также:

Традиционный пример CTE-выражения

Пример оптимизации выгрузки данных