Merge ms sql пример: MERGE (Transact-SQL) — SQL Server

Содержание

Оператор MERGE

Добавить комментарий

Оценить

(2 голоса)

SQL Server 2008 вводит SQL-оператор merge. Этот оператор объединяет последовательность условных команд insert и update в одном атомарном операторе в зависимости от существования записи.

Основной областью приложения оператора merge является хранилище данных, где нужно периодически обновлять таблицы с учетом вновь приходящих данных от систем оперативной обработки транзакций (OLTP, online transaction processing). Эти новые данные могут содержать изменения для существующих строк таблиц и/или новые строки, которые нужно добавить. Если строка в новых данных соответствует элементу, который уже существует в таблице, то выполняется оператор update. В противном случае выполняется оператор insert.

В версиях, предшествующих SQL Server 2008, эти операции выполнялись либо как последовательность операторов insert и update, либо как пакеты, где для каждой строки принималось решение, добавлять или изменять данные. Применение обеих этих техник имеет недостатки производительности: первой требуется множественное сканирование данных, а вторая оперирует по принципу «запись за записью». При расширении Transact-SQL новым оператором SQL Server 2008 преодолевает недостатки предыдущих подходов и упрощает реализацию приложений хранения данных.

Примеры 7.22 и 7.23 демонстрируют использование оператора merge.

В примере 7.22 создается таблица bonus, которая содержит одну строку (pi, 100). Эта таблица может быть использована для слияния.

Оператор merge в примере 7.23 изменяет данные в таблице bonus в зависимости от существования значений в столбце ргпо. Если значение столбца projectno таблицы project появляется в столбце ргпо таблицы bonus, ветвь matched будет выполнена и существующее значение будет изменено. В противном случае будет выполняться ветвь not matched и соответствующий оператор insert добавит новую строку в таблицу bonus.

Подробнее в этой категории: « Оператор INSERT Резюме к изменение содержимого таблиц »

Резюме к изменение содержимого таблиц
Оператор INSERT
Добавление одной строки
Добавление множества строк
Конструкторы значений таблицы и оператор INSERT

Добавить комментарий

Вернуться вверх

Оптимизатор запросов MS SQL Server

01. 01.2007

В версии 7.0 существенно переработан оптимизатор запросов. Сервер может использовать несколько индексов на каждую таблицу в запросе, один запрос может исполняться параллельно на нескольких процессорах. В нем реализованы 3 метода выполнения операции слияния таблиц (JOIN):

1.	LOOP JOIN – для каждой записи в одной из таблиц производится цикл по связанным записям второй таблицы. Этот метод наиболее эффективен для малых результирующих наборов данных.

MERGE JOIN – требует, чтобы оба набора данных были отсортированы по сливаемому полю (набору полей). В этом случае сервер осуществляет слияние за один проход по каждому из наборов данных. Т.к. они уже упорядочены, то нет необходимости просматривать все записи, достаточно выбирать их, начиная с текущей, пока значение поля не изменится. Это самый быстрый метод слияния больших наборов данных.

HASH JOIN – используется, когда невозможно использовать MERGE JOIN, а наборы данных велики. По одному из наборов строится хэш-таблица, а затем для каждой записи из второго набора вычисляется та же хэш функция и производится её поиск в таблице. На больших не отсортированных наборах данных этот алгоритм существенно эффективнее, чем LOOP JOIN.

При фильтрации по индексу сервер не осуществляет сразу выборку данных из таблицы. Вместо этого строится набор «закладок» (Bookmark), а затем производится выборка данных в одной операции (Bookmark Lookup). Это позволяет резко снизить количество обращений к диску.

Новые стратегии оптимизации требуют учета при проектировании БД и структуры индексов. Например, для следующей структуры таблиц:


CREATE TABLE T1 (
 Id INTEGER PRIMARY KEY,
 ...
)

CREATE TABLE T2 (
 Id INTEGER PRIMARY KEY,
 . ..
)

CREATE TABLE T3 (
 Id INTEGER PRIMARY KEY,
 T1Id INTEGER REFERENCES T1(Id),
 T2Id INTEGER REFERENCES T2(Id),
 ...
)

Запрос
SELECT *
  FROM T1
   INNER JOIN T3 ON T1.Id = T3.T1Id
   INNER JOIN T2 ON T2.Id = T3.T2Id
WHERE ...

Может быть существенно ускорен созданием индексов:


CREATE INDEX T3_1 ON T3(T1Id, T2Id)

Он позволят после слияния T3 с T1 получить набор данных, упорядоченный по T2Id, который может быть слит с T2 путем эффективного алгоритма MERGE JOIN. Впрочем, возможно лучший эффект даст индекс:


CREATE INDEX T3_2 ON T3(T2Id, T1Id)

Это зависит от количества записей в T1, T2 и распределения их сочетаний в T3. В OLAP системе (или в слабо загруженной OLTP) лучше построить оба этих индекса, в то время как при интенсивном обновлении таблицы T3 возможно от одного из них придется отказаться. Сервер может сам выдать рекомендации по построению индексов – для этого в него включен Index Tuning Wizard, доступный через Query Analyzer. Он анализирует запрос (или поток команд, собранный при помощи SQL Trace) и выдает рекомендации по структуре индексов в конкретной БД.

В процессе работы с MS SQL Server мною были обнаружены два «тонких» места в оптимизаторе запросов, которые рекомендуется учитывать.

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


DECLARE @I INTEGER

SET @I = 10

SELECT * 
  FROM History H
   INNER JOIN Objects O ON O.Id = H.ObjectId
 WHERE H.StatusId = @I

Сервер сгенерировал следующий план исполнения:

Обращаю внимание – в качестве параметра выступает переменная при этом сервер не может точно оценить в какой диапазон статистики она попадет. В этом случае он делает предположение, что количество записей, полученных из History, будет равно средней селективности по используемому полю, помноженной на количество записей в таблице, в данном случае — 10151. Исходя из этого выбирается алгоритм слияния HASH JOIN, требующий значительных накладных расходов на построение хэш-таблицы. В случае, если реальное количество записей ощутимо меньше (реально этот запрос выбирает 100-200 записей за последний день, имеющих соответствующий StatusId), алгоритм LOOP JOIN дает во много раз лучшую производительность. Итак, если Вы точно знаете, что фильтрация по конкретному полю даст ограниченный набор данных (не более нескольких сотен записей), а сервер об этом «не догадывается» – укажите ему алгоритм слияния явно.


SELECT * 
  FROM History H
   INNER LOOP JOIN Objects O ON O.Id = H.ObjectId
 WHERE H.StatusId = @I

Делать это надо, только если Вы уверены, что этот запрос будет выполняться со значениями параметра, имеющими высокую селективность. На больших наборах данных LOOP JOIN будет гораздо медленнее.

Цена операции Bookmark Lookup (извлечение данных из таблицы по известным значениям индекса) явно завышена. Поэтому иногда, даже при наличии подходящего, индекса вместо INDEX SCAN (поиск по индексу) с последующим Bookmark Lookup (выборка из таблицы) сервер принимает решение о полном сканировании таблицы (TABLE SCAN или CLUSTERED INDEX SCAN). Пример такого запроса приведен на рисунке. Обратите внимание на предполагаемую стоимость запроса (Estimated subtree cost) для случая, когда для таблицы явно задан поиск по индексу. Она чрезвычайно завышена. Видно, что 100% расчетной стоимости выполнения дает операция Bookmark Lookup. Реально же этот запрос выполняется быстрее при индексном доступе, чем при сканировании таблицы. В этом случае рекомендуется попробовать явно указать индекс для доступа к таблице.

Однако считаю нужным предостеречь от слишком частого использования подсказок оптимизатору. Их можно использовать, только если Вы знаете, что этот запрос будет выполняться в конкретных условиях и Вам лучше, чем оптимизатору известно распределение данных в таблице. В большинстве случаев оптимизатор запросов сам хорошо планирует его выполнение. Предпочтительным способом оптимизации представляется грамотное планирование структуры индексов.

Объединить SQL | Примеры и синтаксис оператора слияния в SQL

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

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

Синтаксис слияния SQL

Синтаксис оператора MERGE в SQL показан ниже:

 MERGE <целевая таблица> [AS TARGET]
ИСПОЛЬЗОВАНИЕ <исходной таблицы> [КАК ИСТОЧНИК]
ON <условие_поиска>
[КОГДА СООТВЕТСТВУЕТ
THEN <объединить совпадающие записи> ]
[КОГДА НЕ СООТВЕТСТВУЕТ [ПО ЦЕЛИ]
THEN <выполнить операции слияния, если соответствующая запись не найдена в целевой таблице> ]
[ЕСЛИ НЕ СООТВЕТСТВУЕТ ИСТОЧНИКУ
THEN <выполнить операции слияния, если соответствующая запись не найдена в исходной таблице> ];

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

В приведенном выше синтаксисе целевая таблица — это имя таблицы, над которой должны выполняться операции при появлении результатов сравнения условий. Исходная таблица — это имя таблицы, изменения которой будут действовать как событие, вызывающее изменения в целевой таблице путем выполнения оператора слияния. Условием поиска может быть несколько условий, разделенных логическими операторами, такими как И и ИЛИ, аналогичными спецификации условия в предложении on объединенного оператора. Далее в результате заданных условий поток будет переведен либо в совпавший кейс, либо в не совпавший кейс по цели или источнику, и выполнит нужные операции.

Примеры слияния SQL

Давайте разберемся с реализацией оператора слияния на примере. Всякий раз, когда используется оператор слияния, необходимо идентифицировать исходную таблицу, целевую таблицу и операции, которые необходимо выполнять в целевой таблице всякий раз, когда в исходный оператор вносятся изменения. Рассмотрим две существующие таблицы с именами Articles и Updated Articles, содержимое и структура которых соответствуют выводам следующего оператора запроса:

 ВЫБЕРИТЕ * ИЗ статей;

Выполнение приведенного выше оператора запроса дает вывод, который показан ниже:

 SELECT * FROM UpdatedArticles;

Выполнение приведенного выше оператора запроса дает вывод, который показан ниже:

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

Оператор слияния можно использовать, рассматривая статьи как целевую таблицу, а обновленные статьи как исходную таблицу. Мы можем выполнить объединение и сопоставление на основе уникальных столбцов первичного ключа обеих таблиц с именем article id и решить, соответствует ли запись или нет, сопоставив содержимое этого столбца обеих таблиц. Действия, которые необходимо выполнить при сравнении записей, указаны ниже:

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

Следовательно, в соответствии с указанными выше требованиями для выполнения нескольких операций с целевой таблицей с использованием одного оператора слияния, мы можем написать оператор слияния, как показано ниже, который будет удовлетворять всем условиям и выполнять необходимые операции.

 ОБЪЕДИНИТЬ Статьи КАК ЦЕЛИ
ИСПОЛЬЗОВАНИЕ UpdatedArticles В КАЧЕСТВЕ ИСТОЧНИКА
ВКЛ (TARGET.ArticleID = SOURCE.ArticleID)
ПРИ СОВПАДАНИИ И TARGET.ArticleTopic <> SOURCE.ArticleTopic ИЛИ TARGET.Rate <> SOURCE.Rate
ЗАТЕМ ОБНОВИТЕ SET TARGET.ArticleTopic = SOURCE.ArticleTopic, TARGET.Rate = SOURCE.Rate
КОГДА НЕ СООТВЕТСТВУЕТ ЦЕЛИ
ЗАТЕМ ВСТАВЬТЕ (ID статьи, Тема статьи, Оценить) ЗНАЧЕНИЯ (ИСТОЧНИК.ID статьи, ИСТОЧНИК.Тема статьи, ИСТОЧНИК.Рейт)
ЕСЛИ НЕ СООТВЕТСТВУЕТ ИСТОЧНИКУ
ЗАТЕМ УДАЛИТЬ
ВЫВОД $ДЕЙСТВИЕ,
DELETED. ArticleID AS TargetArticleID,
DELETED.ArticleTopic AS TargetArticleTopic,
DELETED.Rate AS TargetRate,
INSERTED.ArticleID AS SourceArticleID,
INSERTED.ArticleTopic AS SourceArticleTopic,
INSERTED.Rate AS SourceRate;
ВЫБЕРИТЕ @@ROWCOUNT;
ВПЕРЕД

Выполнение приведенного выше оператора запроса дает результат, который показан ниже:

Мы можем заметить, что записи с идентификаторами 2 и 3 существовали в обеих таблицах, но различались по скорости, поэтому скорость исходной таблицы была обновляется в целевой таблице путем выполнения запроса на обновление целевой таблицы. Для идентификатора 4 в исходной таблице обновленных статей не было записи, поэтому она была удалена из целевой таблицы, а для идентификатора 5 в обновленные статьи целевой таблицы была вставлена новая запись, поскольку в ней не было такой записи.

Заключение

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

Слияние T-SQL в триггере SQL Server для обслуживания сводной таблицы

Команда T-SQL Merge является новой для разработчиков Transact-SQL.
Когда инструкция SQL Merge используется в триггере SQL Server, разработчики могут вести сводную таблицу на основе изменений в целевой таблице.
Команда Merge позволяет разработчикам выполнять команды Insert, Update, Delete одновременно в одном операторе Merge.
Команда Merge проверяет наличие данных в целевой таблице, а затем обновляет их.
Если данных нет в целевой таблице, Merge выполняет оператор Insert, чтобы создать новую соответствующую строку в целевой таблице.
И если данные больше не выходят из исходной таблицы, но все еще находятся в целевой таблице, команда Merge также может удалить строку из целевой таблицы.

В примере ниже я попытаюсь продемонстрировать эти возможности команды SQL Server Merge.

В этом руководстве по SQL я создам две таблицы базы данных SQL ProductDetails и ProductSummary.
ProductSummary имеет аналогичные столбцы с таблицей ProductDetails.
Когда новая информация о продукте добавляется в таблицу ProductDetails, новая запись также будет создана в таблице ProductSummary.
При обновлении данных в таблице сведений сводная таблица также будет обновлена.
Конечно, когда запись таблицы удаляется из таблицы «Подробности», соответствующая ей строка также будет удалена из таблицы «Сводка».

Конечно, самый простой способ обновить другую таблицу, когда данные изменяются в таблице базы данных, — это зафиксировать изменения с помощью триггера SQL Server.
В этом руководстве я создам триггер SQL для таблицы Details.
Таким образом, при выполнении команды DML я смогу уловить изменения и изменить целевую сводную таблицу с помощью команды T-SQL Merge в коде триггера SQL.
Команда T-SQL Merge позволяет разработчикам SQL вставлять/обновлять или удалять в одном и том же операторе одновременно.

Теперь создадим образцы таблиц базы данных ProductDetails и ProductSummary.

Создать таблицу ProductDetails ( ProductId int identity(1,1), ProductName nvarchar(100), ProductNumber varchar(25), CategoryId int, Model varchar(50), Color smallint, Size smallint, VendorId, целое число , ContactPerson nvarchar(50), ReOrderAmount int, Column1 varchar(5), Column2 varchar(5), Column3 varchar(5), CreateDate datetime, UpdateDate datetime 9008 8 )

Создать таблицу ProductSummary ( ProductId int, ProductName nvarchar(100), ProductNumber varchar(25), CategoryId int, ContactPerson nvarchar(50), UpdateDate datetime )

Код

Перед заполнением этих двух примеров таблиц SQL я покажу, как создать триггер SQL, в котором будет выполняться оператор слияния SQL.
Триггер SQL Server сработает после выполнения команды Insert, Update или Delete в таблице базы данных ProductDetails.
Обратите внимание на команду Merge SQL.
Он формируется из части «Использование», в которой выбираются данные для сравнения.
И подходящая транзакция создается в операторе слияния в опциях «КОГДА СООТВЕТСТВУЕТ ТО», «КОГДА НЕ СООТВЕТСТВУЕТ ПО ЦЕЛЕВОМУ ТО» и «КОГДА НЕ СООТВЕТСТВУЕТ ПО ИСТОЧНИКУ, ТО».

Создать триггер trMergeProductSummary для ProductDetails After Insert, Update, Delete AS BEGIN

Объединить ProductSummary Используя ( SELECT i.ProductId, i.ProductName, i. ProductNumber, i.CategoryId, i.ContactPerson, i.UpdateDate из вставленного i ) MergeData ON ProductSummary.ProductId = MergeData.ProductId WHEN MATCHEN THEN UPDATE SET ProductSummary.ProductName = MergeData.ProductName, ProductSummary.ProductNumber = MergeData.ProductNumber, ProductSummary. CategoryId = MergeData.CategoryId, ProductSummary.ContactPerson = MergeData.ContactPerson, ProductSummary.UpdateDate = GetDate() ЕСЛИ НЕ СООТВЕТСТВУЕТ ЦЕЛИ ВСТАВИТЬ ЗНАЧЕНИЯ (ProductId, ProductName, ProductNumber, CategoryId, ContactPerson, GetDate()) ЕСЛИ НЕ СООТВЕТСТВУЕТ ИСТОЧНИКУ, УДАЛИТЬ

END GO

Код

Дополнительные сведения и примеры использования команды Merge см. в руководстве по SQL Server Transact-SQL Merge.
Также вы можете прочитать Что нового в SQL Server 2008 — Руководство по объединению команд T-SQL

Давайте теперь сделаем несколько транзакций DML (язык манипулирования данными) и проверим, будут ли основные данные в таблице Details вставляться, обновляться или удаляться одновременно в сводной таблице.
Сначала начните со вставки новых строк в таблицу базы данных Details SQL.
После команды «Вставить» выберите данные в обеих таблицах, чтобы увидеть, копируются ли данные и в сводную таблицу.

Я ожидаю, что оператор SQL Merge не найдет совпадения в целевой таблице.
Затем команда Merge запустит оператор Insert в разделе WHEN NOT MATCHED BY TARGET THEN.

вставить в ProductDetails (название продукта, номер продукта, контактное лицо) значения («Инструмент сравнения данных SQL», «SQL-DC-001», «Eralper Yilmaz») выбрать * из ProductDetails выбрать * из ProductSummary

Код

Теперь мы можем выполнить команду Update для таблицы Details. Я ожидаю увидеть изменения и в сводной таблице.
Оператор обновления в команде SQL Merge Раздел WHEN MATCHED THEN будет выполняться для строк, в которых ProductId совпадают.

обновить ProductDetails установить contactperson = 'Bill Gates' где ProductId = 1 выбрать * из ProductDetails выбрать * из ProductSummary

Код

И последний тестовый сценарий — удаление строки из основной таблицы.
Я ожидаю, что оператор слияния удалит строки из сводной таблицы, используя код WHEN NOT MATCHED BY SOURCE THEN DELETE.

imacrosoft.ru