В чем преимущество общего выражения таблицы в sql-сервере

пишем CTE sql, как показано ниже

WITH yourCTE AS 
(
 SELECT .... FROM :... WHERE.....
) SELECT * FROM yourCTE

что было бы выгодно, чтобы поместить sql в блок. Я думаю, что если мы добавим сложный sql в блок, тогда мы просто можем написать sql как SELECT * FROM yourCTE. как будто я получаю доступ к просмотру. что добавляет преимущества использования CTE с точки зрения производительности. пожалуйста, обсудите. спасибо

Ответ 1

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

рекурсивные запросы, такие как хождение по дереву иерархии - это чрезвычайно сложно и громоздко без CTE (см. здесь для образец рекурсивного CTE)
в любое время, когда вы хотите использовать одну из ранговых функций, например ROW_NUMBER(), RANK(), NTILE() и т.д. (см. здесь для информации о функциях ранжирования)
в общем случае в любом случае, когда вам нужно сначала выбрать несколько строк/столбцов на основе некоторых критериев, а затем сделать что-то с ними, например. обновить таблицу, удалить дубликаты и т.д.

В одном случае я часто использую CTE для удаления всех, кроме самой последней строки заданного набора данных, например. если у вас есть клиенты и отношение 1: n к их заказам, и вы хотите удалить все, кроме самого последнего заказа (на основе OrderDate), для каждого клиента это становится довольно волосатым, чтобы сделать это в SQL без CTE.

С помощью CTE и ранжирующих функций это бриз:

;WITH CustomerOrders AS
(
    SELECT  
       c.CustomerID, o.OrderID,
       ROW_NUMBER() OVER(PARTITION BY c.CustomerID ORDER BY o.OrderDate DESC) AS 'RowN'
    FROM
       dbo.Customer c
    INNER JOIN
       dbo.Orders o ON o.CustomerID = c.CustomerID
)
DELETE FROM 
    dbo.Orders
FROM 
    CustomerOrders co
WHERE 
  dbo.Orders.OrderID = co.OrderID
  AND co.RowN > 1

При этом вы создаете "встроенное представление", которое разделяет на CustomerID (например, каждый клиент получает рябины, начиная с 1), заказывайте OrderDate DESC (сначала самый новый порядок). Для каждого клиента самый новый, самый последний заказ имеет RowN = 1, поэтому вы можете просто просто удалить все остальные строки, и вы сделали то, что хотели - кусок пирога с CTE - беспорядочным кодом без него....

Ответ 2

Эта статья MSDN описывает ее лучше всего. Суть в том, что если вы уже выбираете данные из представления, вам не нужно обертывать его в CTE и THEN, чтобы выбрать из CTE. Я не думаю, что между CTE и представлением существует большая разница (производительность). По крайней мере, не в моем опыте (и я работал с некоторыми сложными структурами базы данных, в которых хранилось множество записей недавно). Однако CTE идеально подходит для рекурсивных выборок.

Другое дело, однако, что CTE может быть полезным, если вы будете выбирать один и тот же поднабор объединенных данных несколько раз в своих запросах и НЕ иметь представления, определенного для него. Я думаю, что это излишне, если вы будете присоединяться к данным только для одного запроса, а затем заверните его в CTE. Путь к запросу по-прежнему будет кэшироваться, даже если вы не используете CTE...

Ответ 3

Выполнение рекурсивного запроса.
Удерживать вывод запроса практически во временной области, названной так, как указано во время определения.
Не нужно сохранять метаданные.
Полезно, когда вам нужно больше работать на выходе запроса.
Выход запроса сохраняется, пока выполняется запрос
Наилучшее использование временных данных для дальнейшей обработки.
Разрешить больше параметров группировки, чем один запрос.
Разрешить получение скалярных данных из сложного запроса

Ответ 4

Добрые вечерние друзья. Сегодня мы узнаем об общем выражении таблицы, которое является новой функцией, появившейся на SQL Server 2005 и имеющейся в более поздних версиях.

Общая таблица Выражение: - Общее выражение таблицы может быть определено как временный набор результатов или, другими словами, его замена представлений в SQL Server. Обычное табличное выражение действует только в пакетной инструкции, где оно было определено и не может использоваться в других сеансах.

Синтаксис объявления CTE (общее табличное выражение): -

with [Name of CTE]
as
(
Body of common table expression
)

Давайте возьмем пример: -

CREATE TABLE Employee([EID] [int] IDENTITY(10,5) NOT NULL,[Name] [varchar](50) NULL)

insert into Employee(Name) values('Neeraj')
insert into Employee(Name) values('dheeraj')
insert into Employee(Name) values('shayam')
insert into Employee(Name) values('vikas')
insert into Employee(Name) values('raj')

CREATE TABLE DEPT(EID INT,DEPTNAME VARCHAR(100))
insert into dept values(10,'IT')
insert into dept values(15,'Finance')
insert into dept values(20,'Admin')
insert into dept values(25,'HR')
insert into dept values(10,'Payroll')

Я создал двух сотрудников таблицы и Dept и вставил по 5 строк в каждой таблице. Теперь я хотел бы присоединиться к этим таблицам и создать временный набор результатов, чтобы использовать его далее.

With CTE_Example(EID,Name,DeptName)
as
(
select Employee.EID,Name,DeptName from Employee 
inner join DEPT on Employee.EID =DEPT.EID
)
select * from CTE_Example

Давайте возьмем каждую строку утверждения один за другим и поймем.

Чтобы определить CTE, мы пишем предложение "с", затем указываем имя выражения таблицы, здесь я назвал имя "CTE_Example"

Затем мы пишем "As" и заключим наш код в две скобки (---), мы можем присоединиться к нескольким таблицам в заключенных скобках.

В последней строке я использовал "Выбрать * из CTE_Example", мы ссылаемся на выражение "Общая таблица" в последней строке кода. Поэтому мы можем сказать, что "Свое" как вид, где мы определяем и используем представление в одной партии и CTE не сохраняется в базе данных как постоянный объект. Но он ведет себя как взгляд. мы можем выполнить операцию удаления и обновления в CTE, и это будет иметь непосредственное влияние на таблицу, на которую ссылаются те, которые используются в CTE. Давайте возьмем пример, чтобы понять этот факт.

With CTE_Example(EID,DeptName)
as
(
select EID,DeptName from DEPT 
)
delete from CTE_Example where EID=10 and DeptName ='Payroll'

В приведенном выше описании мы удаляем строку из CTE_Example и удаляем данные из таблицы "DEPT", которая используется в CTE.

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