Блог

Главная - Вопросы по программированию - Обновление CTE обновляет базовую таблицу

Обновление CTE обновляет базовую таблицу

#sql-server #tsql #common-table-expression

#sql-server #tsql #common-table-expression

Вопрос:

Платформа БД — MS SQL Server 2008R2 У меня есть эта таблица — 

 CREATE TABLE [dbo].[CaseTest](
    [SampleText] [varchar](5) NULL,
    [ID] [int] NULL,
    [Name] [nchar](10) NULL
)

GO

Заполняется образцами данных

 INSERT INTO CaseTest
VALUES ('Text1',1,NULL),
('Text2',2,NULL),
('Text3',3,NULL),
('Text4',4,NULL),
('Text5',5,NULL)

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

 WITH CTE AS(
SELECT * FROM casetest
)
UPDATE CTE SET Name = 'NameText'

Это ожидаемое поведение? Как это происходит на самом деле?

Комментарии:

1. Учитывая, что CTE существует только для одного оператора, который следует за ним, и поэтому UPDATE это был единственный оператор, который мог «видеть» этот конкретный CTE, чего вы ожидали UPDATE , что он будет делать каким-либо значимым образом, если это не повлияет на базовую таблицу?

2. Верно. Однако, поскольку CTE — это просто объект в памяти, я действительно не ожидал, что он выполнит фактическое обновление. Я предположил, что это будет бессмысленно, поскольку CTE будет обновлен и больше не будет доступен. Думаю, я предположил неправильно 🙂

3. CTE не является «объектом в памяти» — это именованный запрос, который может быть включен в более крупный запрос — ничего не материализуется (ни в памяти, ни на диске), и он также не оптимизируется отдельно.

4. Это ожидаемое поведение и очень полезно, когда вы хотите использовать оконные функции в delete update операторе or, например, для удаления дубликатов или заполнения последовательных номеров.

Ответ №1:

Как вы сами заметили в комментарии:

Я предположил, что это было бы бессмысленно

И, действительно, это было бы для любого из операторов, которые фактически вносят изменения в базу данных — каждая такая операция может быть оптимизирована как неоперативная. И поэтому в таком случае на самом деле не имело бы смысла даже разрешать вам писать что-то другое, кроме a SELECT , после создания CTE.

Но это не тот маршрут, который они выбрали для перехода — они позволяют UPDATE записывать , INSERT , DELETE , и т. Д. По той же логике, по которой они позволяют применять определенные операции обновления к представлениям. При условии, что компонент database engine может принять ваше UPDATE заявление и через CTE может определить единственную целевую таблицу UPDATE , UPDATE доступ к которой будет разрешен через CTE.

поскольку CTE — это просто объект в памяти

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

Компонент database engine может решить, в момент оптимизации конечного запроса, материализовать часть или все CTE в хранилище (с помощью своих операторов спула), но это решение об оптимизации, которое принимается позже, и которое механизм в равной степени может решить использовать даже для простых, неЗапросы CTE.