T sql outer apply: CROSS APPLY / OUTER APPLY | 2

Использование оператора APPLY в TSql / Хабр

Недавно, реализуя некоторый код доступа к данным, я столкнулся с задачей выбора последних N записей для каждой сущности. Пользователь kuda78 подсказал вместо многоэтажной выборки использовать метод SelectMany.
Исследуя, какой SQL код создает LinqToSQL, я натолкнулся на интересный SQL оператор APPLY.

Как гласит MSDN эта команда выполняет следующее:
http://technet.microsoft.com/en-us/library/ms175156.aspx
Оператор APPLY позволяет вызывать возвращающую табличное значение функцию для каждой строки, возвращаемой внешним табличным выражением запроса. Возвращающая табличное значение функция выступает в роли правого входа, а внешнее табличное выражение — в роли левого входа. Правый вход оценивается для каждой строки из левого входа, а созданные строки объединяются для конечного вывода. Список столбцов, созданных оператором APPLY, является набором столбцов в левом входе, за которым следует список столбцов, возвращенный правым входом.


Как оказалось APPLY очень хорошо подходит к решению поставленной задачи.

Давайте рассмотрим на примере:
Задача: Выбрать 10 последних заказов для каждого заказчика.


Пускай имеем следующую простую структуру БД:

CREATE TABLE Customer
(
	CustomerID INT PRIMARY KEY,
	CustomerName NVARCHAR(30) NOT NULL
)

CREATE TABLE Nomenclature
(
	NomenclatureID INT PRIMARY KEY,
	NomenclatureName NVARCHAR(30) NOT NULL,
	Price MONEY  NOT NULL
)

CREATE TABLE Deal
(
	DealID INT IDENTITY(1, 1) PRIMARY KEY,
	CustomerID INT NOT NULL,
	NomenclatureID INT NOT NULL,
	[Count] DECIMAL(8,2) NOT NULL,
	DealDate DATETIME NOT NULL
)

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

SELECT
	d.DealDate,
	c.CustomerName,
	n.NomenclatureName,
	n.Price,
	d.Count
FROM
	Customer c JOIN Deal d ON
		d.CustomerID = c.CustomerID
	JOIN (SELECT  c.CustomerID,
		(SELECT MIN(lastDeals.DealDate) FROM (SELECT TOP 10 d1.DealDate FROM Deal d1 WHERE
 d1.CustomerID = c.CustomerID ORDER BY d1.DealDate DESC) LastDeals) LastDealDate
		FROM Customer c) ld ON
		ld.CustomerID = c.CustomerID
	JOIN Nomenclature n ON
		n.NomenclatureID = d.NomenclatureID
WHERE 
	d.DealDate >= ld.LastDealDate 
ORDER BY c.CustomerName, d.DealDate DESC

* Для простоты я специально сделал допущение, что 2 заказа в один и тот же момент времени быть не могут.

С использованием APPLY SQL код приобрел большую читаемость:

SELECT 
	d.DealDate,
	c.CustomerName,
	n.NomenclatureName,
	n.Price,
	d.Count
FROM
	Customer c 
	OUTER APPLY (SELECT TOP 10 d1.* FROM Deal d1 Where d1.CustomerID = c.CustomerID ORDER BY d1.DealDate DESC) d
	INNER JOIN Nomenclature n ON
		n.NomenclatureID = d.NomenclatureID
ORDER BY c.CustomerName, d.DealDate DESC

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

Файл создания бд с индексами: CreateDB.txt

Файл с SQL запросами: Queries.txt

SQL — Основы CROSS APPLY и OUTER APPLY

пример

Применить будет использоваться, когда функция table value в правильном выражении.

создайте таблицу отдела для хранения информации о департаментах. Затем создайте таблицу Employee, в которой хранятся сведения о сотрудниках. Обратите внимание: каждый сотрудник принадлежит отделу, поэтому таблица Employee имеет ссылочную целостность с таблицей Департамента.

Первый запрос выбирает данные из таблицы Department и использует CROSS APPLY для оценки таблицы Employee для каждой записи таблицы Department. Второй запрос просто присоединяется к таблице Департамента с таблицей Employee, и все соответствующие записи создаются.

SELECT *
FROM Department D
CROSS APPLY (
    SELECT *
    FROM Employee E
    WHERE E.DepartmentID = D.DepartmentID
) A
GO
SELECT *
FROM Department D
INNER JOIN Employee E
  ON D.DepartmentID = E.DepartmentID

Если вы посмотрите на результаты, которые они произвели, это точно такой же результат; Чем он отличается от JOIN и как он помогает в написании более эффективных запросов.

Первый запрос в сценарии # 2 выбирает данные из таблицы Department и использует OUTER APPLY для оценки таблицы Employee для каждой записи таблицы Department. Для тех строк, для которых нет совпадений в таблице Employee, эти строки содержат значения NULL, как вы можете видеть в случае строк 5 и 6. Второй запрос просто использует LEFT OUTER JOIN между таблицей Департамента и таблицей Employee. Как и ожидалось, запрос возвращает все строки из таблицы Department; даже для тех строк, для которых нет совпадений в таблице Employee.

SELECT *
FROM Department D
OUTER APPLY (
    SELECT *
    FROM Employee E
    WHERE E.DepartmentID = D.DepartmentID
) A
GO
SELECT *
FROM Department D
LEFT OUTER JOIN Employee E
  ON D.DepartmentID = E.DepartmentID
GO

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

Теперь настало время посмотреть, где действительно нужен оператор APPLY. В сценарии №3 я создаю табличную функцию, которая принимает параметр DepartmentID как параметр и возвращает всех сотрудников, принадлежащих этому отделу. Следующий запрос выбирает данные из таблицы Department и использует CROSS APPLY для объединения с созданной нами функцией. Он передает идентификатор отдела для каждой строки из выражения внешней таблицы (в нашем случае таблица Департамента) и оценивает функцию для каждой строки, подобной коррелированному подзапросу. Следующий запрос использует OUTER APPLY вместо CROSS APPLY и, следовательно, в отличие от CROSS APPLY, который возвращает только коррелированные данные, OUTER APPLY также возвращает некоррелированные данные, помещая NULL в отсутствующие столбцы.

CREATE FUNCTION dbo.fn_GetAllEmployeeOfADepartment (@DeptID AS int)
RETURNS TABLE
AS
  RETURN
  (
  SELECT
    *
  FROM Employee E
  WHERE E.DepartmentID = @DeptID
  )
GO
SELECT
  *
FROM Department D
CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)
GO
SELECT
  *
FROM Department D
OUTER APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)
GO

Итак, теперь, если вам интересно, можем ли мы использовать простое соединение вместо вышеуказанных запросов? Тогда ответ НЕТ, если вы замените CROSS / OUTER APPLY в вышеуказанных запросах INNER JOIN / LEFT OUTER JOIN, укажите предложение ON (что-то как 1 = 1) и запустите запрос, вы получите «Идентификатор с несколькими частями», D.DepartmentID «не может быть связан». ошибка. Это связано с тем, что с JOINs контекст выполнения внешнего запроса отличается от контекста выполнения функции (или производной таблицы), и вы не можете привязать значение / переменную от внешнего запроса к функции в качестве параметра. Следовательно, для таких запросов требуется оператор APPLY.

Пример реальной жизни, когда использовать OUTER / CROSS APPLY в SQL

существуют различные ситуации, когда вы не можете избежать CROSS APPLY или OUTER APPLY.

рассмотрим у вас есть две таблицы.

ГЛАВНЫЙ СТОЛ

x------x--------------------x
| Id   |        Name        |
x------x--------------------x
|  1   |          A         |
|  2   |          B         |
|  3   |          C         |
x------x--------------------x

ТАБЛИЦЕ

x------x--------------------x-------x
| Id   |      PERIOD        |   QTY |
x------x--------------------x-------x
|  1   |   2014-01-13       |   10  |
|  1   |   2014-01-11       |   15  |
|  1   |   2014-01-12       |   20  |
|  2   |   2014-01-06       |   30  |
|  2   |   2014-01-08       |   40  |
x------x--------------------x-------x                                       

КРЕСТ ПРИМЕНИТЬ

есть много ситуаций, когда нам нужно заменить INNER JOIN С CROSS APPLY.

1. Если мы хотим присоединиться к 2 таблицам на TOP n результаты INNER JOIN функциональность

рассмотрим, если нам нужно выбрать Id и Name С Master и последние две даты для каждого Id С Details table.

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
INNER JOIN
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D      
    ORDER BY CAST(PERIOD AS DATE)DESC
)D
ON M.ID=D.ID

приведенный выше запрос выдает следующий результат.

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
x------x---------x--------------x-------x

см., он сгенерировал результаты для последних двух дат с последними двумя датами Id а затем присоединился к этим записывает только во внешний запрос on Id, а это неправильно. Для этого нам нужно использовать CROSS APPLY.

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
CROSS APPLY
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D  
    WHERE M.ID=D.ID
    ORDER BY CAST(PERIOD AS DATE)DESC
)D

и формирует он следующий результат.

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-08   |  40   |
|   2  |   B     | 2014-01-06   |  30   |
x------x---------x--------------x-------x

вот работа. Запрос внутри CROSS APPLY может ссылаться на внешнюю таблицу, где INNER JOIN не удается сделать это (выдает ошибку компиляции). При нахождении последних двух дат присоединение выполняется внутри CROSS APPLY ie,WHERE M.ID=D.ID.

2. Когда нам нужно INNER JOIN функции с помощью функции.

CROSS APPLY может использоваться в качестве замены на INNER JOIN когда нам нужно получить результат из Master таблица и function.

SELECT M.ID,M.NAME,C.PERIOD,C.QTY
FROM MASTER M
CROSS APPLY dbo.FnGetQty(M.ID) C

и вот функция

CREATE FUNCTION FnGetQty 
(   
    @Id INT 
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT ID,PERIOD,QTY 
    FROM DETAILS
    WHERE ID=@Id
)

который произвел следующий результат

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-11   |  15   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-06   |  30   |
|   2  |   B     | 2014-01-08   |  40   |
x------x---------x--------------x-------x

НАРУЖНОЕ ПРИМЕНЕНИЕ

1. Если мы хотим объединить 2 таблицы по TOP n результаты LEFT JOIN функциональность

рассмотрим, если нам нужно выбрать Id и имя из Master и последние две даты для каждого ID из Details таблица.

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
LEFT JOIN
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D  
    ORDER BY CAST(PERIOD AS DATE)DESC
)D
ON M.ID=D.ID

который формирует следующий результат

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     |   NULL       |  NULL |
|   3  |   C     |   NULL       |  NULL |
x------x---------x--------------x-------x

это приведет к неправильным результатам, т. е. это принесет только последние две нужные сведения из Details таблицы независимо от Id хотя мы присоединяемся к Id. Поэтому правильным решением является использование OUTER APPLY.

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
OUTER APPLY
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D  
    WHERE M.ID=D.ID
    ORDER BY CAST(PERIOD AS DATE)DESC
)D

который формирует следующий желаемый результат

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-08   |  40   |
|   2  |   B     | 2014-01-06   |  30   |
|   3  |   C     |   NULL       |  NULL |
x------x---------x--------------x-------x

2. Когда нам нужно LEFT JOIN функции с помощью functions.

OUTER APPLY может использоваться в качестве замены на LEFT JOIN когда нам нужно получить результат из Master таблица и function.

SELECT M.ID,M.NAME,C.PERIOD,C.QTY
FROM MASTER M
OUTER APPLY dbo.FnGetQty(M.ID) C

и функция идет здесь.

CREATE FUNCTION FnGetQty 
(   
    @Id INT 
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT ID,PERIOD,QTY 
    FROM DETAILS
    WHERE ID=@Id
)

, который был создан следующий результат

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-11   |  15   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-06   |  30   |
|   2  |   B     | 2014-01-08   |  40   |
|   3  |   C     |   NULL       |  NULL |
x------x---------x--------------x-------x

Общая характеристика CROSS APPLY и OUTER APPLY

CROSS APPLY или OUTER APPLY может использоваться для сохранения NULL значения, когда unpivoting, которые являются взаимозаменяемыми.

считают, что у вас есть таблица ниже

x------x-------------x--------------x
|  Id  |   FROMDATE  |   TODATE     |
x------x-------------x--------------x
|   1  |  2014-01-11 | 2014-01-13   | 
|   1  |  2014-02-23 | 2014-02-27   | 
|   2  |  2014-05-06 | 2014-05-30   |    
|   3  |   NULL      |   NULL       | 
x------x-------------x--------------x

при использовании UNPIVOT вывести FROMDATE и TODATE к одной колонке, она исключит NULL значения по умолчанию.

SELECT ID,DATES
FROM MYTABLE
UNPIVOT (DATES FOR COLS IN (FROMDATE,TODATE)) P

, который генерирует результат ниже. Обратите внимание, что мы пропустили запись Id

  x------x-------------x
  | Id   |    DATES    |
  x------x-------------x
  |  1   |  2014-01-11 |
  |  1   |  2014-01-13 |
  |  1   |  2014-02-23 |
  |  1   |  2014-02-27 |
  |  2   |  2014-05-06 |
  |  2   |  2014-05-30 |
  x------x-------------x

в таких случаях a CROSS APPLY или OUTER APPLY будет полезным

SELECT DISTINCT ID,DATES
FROM MYTABLE 
OUTER APPLY(VALUES (FROMDATE),(TODATE))
COLUMNNAMES(DATES)

, которая формирует следующий результат и сохраняет Id, где его стоимость составляет 3

  x------x-------------x
  | Id   |    DATES    |
  x------x-------------x
  |  1   |  2014-01-11 |
  |  1   |  2014-01-13 |
  |  1   |  2014-02-23 |
  |  1   |  2014-02-27 |
  |  2   |  2014-05-06 |
  |  2   |  2014-05-30 |
  |  3   |     NULL    |
  x------x-------------x

Outer apply sql описание

Введение в соединения

По материалам статьи Craig Freedman: Introduction to Joins

Соединение (JOIN) — одна из самых важных операций, выполняемых реляционными системами управления базами данных (РСУБД). РСУБД используют соединения для того, чтобы сопоставить строки одной таблицы строкам другой таблицы. Например, соединения можно использовать для сопоставления продаж — клиентам или книг — авторам. Без соединений, имелись бы раздельные списки продаж и клиентов или книг и авторов, но невозможно было бы определить, какие клиенты что купили, или какой из авторов был заказан.
Можно соединить две таблицы явно, перечислив обе таблицы в предложении FROM запроса. Также можно соединить две таблицы, используя для этого всё разнообразие подзапросов. Наконец, SQL Server во время оптимизации может добавить соединение в план запроса, преследуя свои цели.
Это первая из серии статей, которые я планирую посвятить соединениям. Эту статью я собираюсь посвятить азам соединений, описав назначение логических операторов соединениё, поддерживаемых SQL Server. Вот они:

Для иллюстрации каждого соединения я буду использовать простую схему и набор данных:

Внутренние соединения — самый распространённый тип соединений. Внутреннее соединение просто находит пары строк, которые соединяются и удовлетворяют предикату соединения. Например, показанный ниже запрос использует предикат соединения «S.Cust_Id = C.Cust_Id», позволяющий найти все продажи и сведения о клиенте с одинаковыми значениями Cust_Id:

Cust_Id = 3 купил два наименования, поэтому он фигурирует в двух строках результирующего набора.
Cust_Id = 1 не купил ничто и потому не появляется в результате.
Для Cust_Id = 4 тоже был продан товар, но поскольку в таблице нет такого клиента, сведения о такой продаже не появились в результате.
Внутренние соединения полностью коммутативны. «A inner join B» и «B inner join A» эквивалентны.

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

Обратите внимание, что сервер возвращает вместо данных о клиенте значение NULL, поскольку для проданного товара ‘Printer’ нет соответствующей записи клиента. Обратите внимание на последнюю строку, у которой отсутствующие значения заполнены значением NULL.
Используя полное внешнее соединение, можно найти всех клиентов (независимо от того, покупали ли они что-нибудь), и все продажи (независимо от того, сопоставлен ли им имеющийся клиент):

Следующая таблица показывает, строки какой из соединяемых таблиц попадут в результирующий набор (у оставшейся таблицы возможны замены NULL), она охватывает все типы внешних соединений:

A left outer join B

Все строки A

A right outer join B

Все строки B

A full outer join B

Все строки A и B

Полные внешние соединения коммутативны. Кроме того, «A left outer join B » и «B right outer join A» является эквивалентным.

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

В SQL Server 2005 мы добавили оператор CROSS APPLY, с помощью которого можно соединять таблицу с возвращающей табличное значение функцией (table valued function — TVF), причём TVF будет иметь параметр, который будет изменяться для каждой строки. Например, представленный ниже запрос возвратит тот же результат, что и показанное ранее внутреннее соединение, но с использованием TVF и CROSS APPLY:

Также можно использовать внешнее обращение — OUTER APPLY, позволяющее нам найти всех клиентов независимо от того, купили ли они что-нибудь или нет. Это будет похоже на внешнее соединение.

Полусоединение и анти-полусоединение

Полусоединение — semi-join возвращает строки только одной из соединяемых таблиц, без выполнения соединения полностью. Анти-полусоединение возвращает те строки таблицы, которые не годятся для соединения с другой таблицей; т.е. они в обычном внешнем соединении выдавали бы NULL.
В отличие от других операторов соединений, не существует явного синтаксиса для указания исполнения полусоединения, но SQL Server, в целом ряде случаев, использует в плане исполнения именно полусоединения. Например, полусоединение может использоваться в плане подзапроса с EXISTS:

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

В плане запроса видно, что SQL Server действительно использует полусоединение:

Существуют левые и правые полусоединения. Левое полусоединение возвращает строки левой (первой) таблицы, которые соответствуют строкам из правой (второй) таблицы, в то время как правое полусоединение возвращает строки из правой таблицы, которые соответствуют строкам из левой таблицы.
Подобным образом может использоваться анти-полусоединение для обработки подзапроса с NOT EXISTS.

Во всех представленных в статье примерах использовались предикаты соединения, который сравнивали, являются ли оба столбца каждой из соединяемых таблицы равными. Такой тип предикатов соединений принято называть «соединением по эквивалентности». Другие предикаты соединений (например, неравенства) тоже возможны, но соединения по эквивалентности распространены наиболее широко. В SQL Server заложено много альтернативных вариантов оптимизации соединений по эквивалентности и оптимизации соединений с более сложными предикатами.
SQL Server более гибок в выборе порядка соединения и его алгоритма при оптимизации внутренних соединений, чем при оптимизации внешних соединений и CROSS APPLY. Таким образом, если взять два запроса, которые отличаются только тем, что один использует исключительно внутренние соединения, а другой использует внешние соединения и/или CROSS APPLY, SQL Server сможет найти лучший план исполнения для запроса, который использует только внутренние соединения.

Кто-нить может объяснить, чем мог не устроить такой гипотетический синтаксис (который чисто логически должен работать, но вызывает ошибку в точке A.BId «не удалось выполнить привязку составного идентификатора A.BId», типа из-за того, что там нельзя получить доступ к таблице A):

что разрабам T-SQL пришлось придумывать ему такую замену:

?
Аналогично для OUTER APPLY, для чего он нужен, когда можно было сделать через JOIN (ведь что такое JOIN знают все, а про APPLY знает намного меньше, и большинство в таких ситуациях начнет писать запрос с джойном, как в первом примере, который не будет работать).

И еще получается, что Right Join через APPLY никак не сделать, а если нужно?

• Вопрос задан более двух лет назад
• 2146 просмотров

На словах — это может быть быстрее. А еще может применить параметризованную функцию к каждой строке и присоединить ее результат.

CROSS APPLY has its obvious usage in allowing a set to depend on another (unlike the JOIN operator)

Не понял, а разве сджойненные таблицы не означают то же самое, что набор (set, данные) слева зависят от другого набора справа (в той же строке)?

it behaves like a function that operates over each member of the left set, so, in SQL Server terms it always perform a Loop Join, which almost never is the best way to join sets.

Снова не понял, а разве join не оперирует точно так же каждой строкой в левом наборе (джойня ее с соответствием в правом наборе)?

А еще может применить параметризованную функцию к каждой строке и присоединить ее результат.

footballer: Я вам рекомендую пойти и прочитать документацию по данному оператору.

Применение табличного оператора APPLY в процессе логической обработки запросов

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

В предыдущей статье мы начали рассматривать предложения FROM, сделав акцент на объединениях. На этот раз я продолжу тему, но теперь основное внимание будет уделено оператору APPLY. В этой статье я буду пользоваться той же демонстрационной базой данных TSQLV4, которая использовалась в предыдущих статьях. Исходный код для создания и заполнения этой базы вы можете найти в предыдущей статье, опубликованной в этом же номере журнала. Для использования демонстрационной базы данных в вашем сеансе запустите следующий код:

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

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

Оператор APPLY используется как средство языка T-SQL; подобно оператору JOIN, он является табличным оператором. В чем-то он подобен объединению, а в чем-то отличается от него. Сначала я покажу, в чем состоят ограничения объединений и подзапросов. Затем мы рассмотрим, как оператор APPLY помогает преодолевать эти ограничения. В заключение я объясню, каким образом мы можем задействовать оператор APPLY для определения псевдонимов столбцов и обеспечить их использование на первых этапах логической обработки запросов

Ограничения объединений и подзапросов

Причина, побудившая разработчиков Microsoft включить в T-SQL оператор APPLY, состояла в том, что подзапросы и объединения, альтернативой которым должен был стать этот оператор, имеют ряд ограничений. Приведу пример. Допустим, вам необходимо написать запрос к базе TSQLV4, который возвращает последний по времени заказ от каждого клиента. В запросе будут использоваться таблицы Sales.Customers и Sales.Orders. Из таблицы Customers вы должны извлечь идентификатор клиента и название компании, а из квалифицирующих строк таблицы Orders — идентификатор заказа, дату заказа, а также идентификатор служащего.

Эта задача известна как задача выделения в группе N элементов с максимальными показателями. Следующий код создает рекомендуемый индекс в таблице Orders для типичных запросных решений для данной задачи:

Для себя этот индекс я называю «индексом POC», где аббревиатура POC означает partitioning, ordering and covering, или выделение элементов, их упорядочение и наполнение данными. Он определяет список ключей на основе элемента выделения (custid), за которым следует элемент упорядочения (orderdate DESC, orderid DESC). Для наполнения в список включаются оставшиеся элементы запроса (empid).

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

Но поскольку T-SQL не поддерживает такое векторное выражение и ограничивает подзапрос возвращением одного выражения, мы получаем следующую ошибку:

Всего лишь одно выражение может быть указано в списке выбора в случае, когда подзапрос не вводится оператором EXISTS.

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

Это довольно громоздкое решение. Почти вся логика запроса повторяется трижды, что затрудняет его обслуживание. Кроме того, по состоянию на сегодня оптимизатор SQL Server не пытается внутренними средствами свести логику трех подзапросов к одной операции физического доступа к данным. Это явно просматривается в плане выполнения запроса, представленного на рисунке 1 (с помощью средства Plan Explorer программы SQL Sentry).

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

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

Возможно, вы полагаете, что эта задача решается с использованием перекрестного объединения, как, например, в листинге 2.

Однако надо отметить, что в процессе объединения два набора входных данных рассматриваются как множество, а множество не имеет атрибута упорядочения. Это означает, что на одной стороне объединения мы не имеем возможности ссылаться на элементы с другой его стороны. Здесь производный табличный запрос к таблице Orders коррелируется через фильтр с элементом из таблицы Customers (C.custid). И только на последующих этапах логической обработки запроса будет предоставляться доступ к элементам с обеих сторон объединения. Если вы попытаетесь выполнить данный запрос, то получите следующее сообщение об ошибке:

То есть состоящий из нескольких частей идентификатор C.custid не может быть связан.

APPLY идет на помощь

Оператор APPLY как раз создавался для решения описанных мною проблем, имеющих отношение к подзапросам и объединениям. Существует два вида таких операторов; один из них именуется CROSS APPLY; он реализует только один логический шаг в обработке. Второй вид — OUTER APPLY, он реализует два шага.

Оператор CROSS APPLY во многом подобен оператору CROSS JOIN, только он не рассматривает два входящих набора данных как множества. Вместо этого данный оператор прежде всего оценивает левый входной набор, а затем применяет правую сторону к каждой строке в левой части. Что же из этого следует? Если правая часть представляет собой табличное выражение, такое как производная таблица, внутри этого выражения вы можете обращаться к элементам левой стороны. Таким образом, если в последнем запросе вы замените оператор CROSS JOIN оператором CROSS APPLY, запрос станет допустимым и решит нашу задачу. Код, которым нужно воспользоваться, указан в листинге 3.

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

План выполнения этого запроса показан на рисунке 2.

Следует отметить, что для каждого клиента существует лишь одна возможность доступа к индексу POC.

Как уже говорилось, оператор APPLY применяется только в среде T-SQL. Любопытно, что стандарт SQL включает в себя аналогичное средство, так называемую вторичную производную таблицу, lateral derived table. Стандартное решение, параллельное применению CROSS APPLY, состоит в использовании оператора CROSS JOIN, однако перед производной таблицей следует в виде префикса указывать ключевое слово LATERAL, которое будет квалифицировать производную таблицу как вторичную. Оператор начинает обработку с левой стороны и применяет вторичную производную таблицу к каждой строке слева. Язык T-SQL не предоставляет возможности работать со вторичными производными таблицами, поскольку предусматривает собственное решение с помощью оператора APPLY. Но если бы такая возможность была, соответствующий код выглядел бы так, как в листинге 4. Не пытайтесь запускать его, так как этот код не поддерживается системой SQL Server.

Как уже отмечалось, оператор CROSS APPLY реализует только один этап логической обработки запроса: применение выражения из правой таблицы к каждой строке с левой стороны. Если правая сторона возвращает пустой набор для данной строки слева, оператор CROSS APPLY не будет возвращать эту строку. Это напоминает случай с перекрестным объединением таблицы с одной строкой и таблицы с нулевым количеством строк: в ответ вы получаете пустой набор данных. Если вы хотите сохранить все левые строки, используйте второй тип оператора, а именно OUTER APPLY. Этот оператор включает в себя второй логический шаг, сохраняющий внешние строки. Подобно оператору LEFT OUTER JOIN, оператор OUTER APPLY использует значения NULL в качестве заполнителей на правой стороне, поскольку соответствий нет.

В результате выполнения предыдущего запроса, в котором применяется оператор CROSS APPLY, мы получаем 263 строки (см. рисунок 3).

В полученный результат не входят два клиента, представленные в таблице Customers, но не в таблице Orders.

Во фрагменте кода в листинге 5 оператор CROSS APPLY из предыдущего примера заменяется оператором OUTER APPLY.

Этот запрос возвращает 265 строк, в которые включены два клиента, не имеющие соответствующих заказов (см. рисунок 4).

Если вас интересует, какова стандартная альтернатива применению оператора OUTER APPLY, могу сказать, что она состоит в использовании оператора LEFT OUTER JOIN со вторичной производной таблицей и с предикатом объединения, всегда имеющим значение true, как в листинге 6 (опять-таки не запускайте этот код, ибо он не поддерживается системой SQL Server).

На рисунке 5 представлены этапы логической обработки запроса для оператора APPLY в дополнение к этапам для объединений, продемонстрированным в предыдущей статье.

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

Далее внешний запрос может использовать эту функцию в качестве правого потока входных данных оператора APPLY вместо производной таблицы и передать C.custid, а также число недавних заказов в качестве входных данных:

Эта функция встраивается до этапа оптимизации, так что получаемый вами план запроса идентичен плану, показанному на рисунке 2.

Повторное использование псевдонимов столбцов

Один из раздражающих аспектов работы с языком SQL сводится к следующей ситуации. Вы назначаете псевдонимы столбцов для выражений в предложении SELECT, а затем пытаетесь ссылаться на эти псевдонимы в других предложениях запроса, таких как WHERE или GROUP BY. Рассмотрим следующий запрос:

Возникает у вас ощущение, что здесь что-то не так? Если нет, попробуйте запустить этот код. Вы получите сообщение об ошибке (см. рисунок 6).

В рамках логической обработки запроса предложение SELECT анализируется после предложения GROUP, а не наоборот. Псевдонимы, назначенные внутри предложения SELECT, недоступны выражениям, которые появляются на более ранних этапах логической обработки запросов. Следовательно, мы не можем ссылаться на такие псевдонимы в предложении GROUP BY. По той же причине на эти псевдонимы нельзя ссылаться в предложении WHERE.

Этот вопрос мы рассмотрим более подробно в следующих статьях, где речь пойдет о предложениях WHERE и GROUP BY. Пока же я хочу остановиться на операторе APPLY. Поскольку APPLY является табличным оператором, он оценивается как часть предложения FROM, которое анализируется прежде всех остальных предложений запроса. Отсюда следует, что, если вы определяете псевдонимы столбцов с помощью APPLY, эти псевдонимы, естественно, будут доступны всем выражениям в остальной части предложений запроса. Вы можете разрешить проблемы, возникшие при выполнении предыдущего запроса, с помощью оператора APPLY и производной таблицы, основывающейся на предложении VALUES (известном как конструктор значений), используя код из листинга 8.

Предложение VALUES определяет таблицу, состоящую из одной строки с двумя столбцами. Оно присваивает псевдоним столбца orderyear выражению YEAR (orderdate) и псевдоним ordermonth выражению MONTH (orderdate). Обратите внимание: APPLY обеспечивает доступ к выражениям на правой стороне столбцу orderdate с левой стороны. Как уже отмечалось, поскольку псевдонимы создаются на первых этапах процесса логической обработки запроса, они становятся доступными для предложений запроса, которые анализируются в ходе последующих этапов, например для предложения GROUP BY в данном запросе.

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

Обратите внимание на попытку сослаться на псевдоним orderyear в выражении, которое определяет столбец endofyear. Поскольку orderyear определяется на том же шаге логической обработки запроса, что и endofyear, ссылка на orderyear в этом выражении недопустима. Ссылка на псевдоним, созданный на том или ином этапе, допускается только из выражений, появляющихся на последующих этапах, но не на том же самом этапе и тем более не на предшествующих этапах. Если вы попытаетесь запустить помещенный выше запрос, то получите сообщение об ошибке (см. рисунок 7).

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

Итак, вы получили представление о функционировании операторов JOIN и APPLY в процессе логической обработки запросов и теперь можете сами разобраться с тем, какую роль играет предложение FROM при выполнении сложного демонстрационного запроса. В листинге 11 показана часть этого запроса с полным предложением FROM и с упрощенным предложением SELECT.

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

Запрос выполняет левое внешнее объединение между таблицей Customers и результатом внутреннего объединения между таблицами Orders и OrderDetails. Объединение является левым внешним, поэтому сохраняются все клиенты, даже те, которые не размещали заказы в период с начала 2016 года. У этих клиентов атрибуты с несохраненной стороны объединения будут иметь значение NULL.

Далее запрос создает с помощью оператора CROSS APPLY псевдонимы столбцов custlocation и val. Первый из них представляет собой составную строку, состоящую из атрибутов страны, региона и города с разделителями между элементами. Функция CONCAT объединяет входные данные в цепочку, заменяя значения NULL пустыми строками. Второй псевдоним — это значение строки заказов, рассчитанное как количество, умноженное на цену единицы продукции; при этом учитывается скидка. Поскольку эти псевдонимы столбцов создаются в предложении FROM, они доступны для всех остающихся предложений в запросе.

Предложение SELECT в данном примере просто возвращает некоторые из атрибутов, включая те, что были рассчитаны с помощью оператора CROSS APPLY. Мы получаем на выходе рассматриваемого запроса данные, показанные на рисунке 8.

По завершении работы выполните следующий фрагмент кода для наведения порядка:

Что дальше?

В данной статье я уделил основное внимание использованию табличного оператора APPLY в процессе логической обработки запросов. Я описал два вида этого оператора: CROSS APPLY и OUTER APPLY. Первый из них применяет выражение из правой таблицы к каждой строке с левой стороны, а второй к тому же сохраняет все левые строки. Мы рассмотрели преимущества, которые применение оператора APPLY дает по сравнению с использованием объединений и подзапросов. Кроме того, я объяснил, как оператор APPLY используется для создания псевдонимов столбцов для выражений на первых этапах процесса логической обработки запроса, так чтобы эти псевдонимы были доступны для всех остальных предложений запроса. В следующей статье я продолжу описание предложения FROM. Речь пойдет об использовании в процессе логической обработки запросов операторов PIVOT и UNPIVOT.

Разница между CROSS APPLY и OUTER APPLY в SQL Server

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

Операции JOIN в SQL Server используются для объединения двух или более таблиц. Однако операции JOIN нельзя использовать для соединения таблицы с выходными данными функции с табличным значением.

Для этого используются операторы APPLY.

Есть два основных типа операторов APPLY.1) КРЕСТНОЕ ПРИМЕНЕНИЕ и 2) НАРУЖНОЕ ПРИМЕНЕНИЕ.

Оператор CROSS APPLY семантически аналогичен оператору INNER JOIN. Он извлекает эти записи из функции с табличным значением и присоединяемой таблицы, где находит совпадающие строки между ними.

С другой стороны, OUTER APPLY извлекает все записи как из функции с табличным значением, так и из таблицы, независимо от совпадения.

В этой статье мы рассмотрим операторы CROSS APPLY и OUTER APPLY.Посмотрим, как они реализуются на практике, на примере, а также обсудим, чем они отличаются друг от друга.

Подготовка фиктивных данных

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

Выполните следующий сценарий:

В приведенном выше сценарии мы создали базу данных с именем Library.В базе есть две таблицы: Автор и Книга. В книге есть столбец author_id, который содержит значения из столбца id таблицы Author. Это означает, что между столбцами «Автор» и «Книга» существует отношение «один ко многим».

Объединение таблиц с помощью операторов JOIN

Давайте сначала воспользуемся оператором INNER JOIN для извлечения совпадающих строк из обеих таблиц.

Выполните следующий сценарий:

Будут выбраны следующие записи.

Вы можете видеть, что из таблицы «Автор» были выбраны только те записи, в которых есть соответствующая строка в таблице «Книга». Чтобы получить все записи из таблицы Author, можно использовать LEFT JOIN.

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

Вы можете видеть, что все записи извлекаются из таблицы Author, независимо от того, есть ли какие-либо совпадающие строки в таблице Book.

Объединение табличных функций с таблицами с помощью операторов APPLY

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

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

Выполните следующий сценарий:

Давайте проверим вышеуказанную функцию.Мы передадим 3 в качестве идентификатора автора функции fnGetBooksByAuthorId. Он должен вернуть book4 и book6, поскольку это две книги, написанные автором с идентификатором three.

Выполните следующий сценарий:

Результат вышеупомянутого скрипта выглядит так:

Давайте попробуем использовать оператор INNER JOIN, чтобы присоединиться к таблице Author с функцией fnGetBooksByAuthorId с табличным значением.

Взгляните на следующий сценарий:

Здесь мы используем оператор INNER JOIN для соединения физической таблицы (Author) с функцией fnGetBooksByAuthorId с табличным значением.Все идентификаторы из таблицы Author передаются функции. Однако приведенный выше сценарий вызывает ошибку, которая выглядит следующим образом:

Соединение таблицы и функции с табличным значением с помощью CROSS APPLY

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

Взгляните на следующий сценарий:

В приведенном выше скрипте все идентификаторы из таблицы Author передаются в функцию fnGetBooksByAuthorId. Для каждого идентификатора в таблице Author функция возвращает соответствующие записи из таблицы Book.Результат этой функции с табличным значением объединяется с таблицей Author.

Результат вышеупомянутого скрипта выглядит так:

Это похоже на операцию INNER JOIN, выполняемую с таблицами Author и Book. CROSS APPLY возвращает только те записи из физической таблицы, в которых есть совпадающие строки в выходных данных функции с табличным значением.

Объединение табличных и табличных функций с помощью OUTER APPLY

Чтобы получить все строки как из физической таблицы, так и из вывода функции с табличным значением, используется OUTER APPLY.OUTER APPLY семантически аналогична операции OUTER JOIN.

Взгляните на следующий сценарий, чтобы увидеть OUTER APPLY в действии.

Результат вышеупомянутой функции выглядит следующим образом:

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

Заключение

В этой статье мы изучили, что такое функции CROSS APPLY и OUTER APPLY, и как их можно использовать для выполнения операций соединения между физической таблицей и функцией с табличным значением.

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

Бен Ричардсон руководит Acuity Training, ведущим поставщиком обучения SQL в Великобритании.Он предлагает полный спектр обучения SQL от вводных курсов до углубленного обучения администрированию и хранилищам данных — подробнее см. Здесь. У Acuity есть офисы в Лондоне и Гилфорде, графство Суррей. Он также иногда ведет блог в блоге Acuity

Просмотреть все сообщения Бена Ричардсона

Последние сообщения Бена Ричардсона (посмотреть все)

.Сервер

sql — пример из реальной жизни, когда использовать OUTER / CROSS APPLY в SQL

Переполнение стека

1. Около

2. Продукты

3. Для команд

1. Переполнение стека
   Общественные вопросы и ответы

2. Переполнение стека для команд
   Где разработчики и технологи делятся частными знаниями с коллегами

3. Вакансии
   Программирование и связанные с ним технические возможности карьерного роста

4. Талант
   Нанимайте технических специалистов и создавайте свой бренд работодателя

5. Реклама
   Обратитесь к разработчикам и технологам со всего мира

6. О компании

.

CROSS APPLY и OUTER APPLY — {coding} Прицел

В этой статье мы рассмотрим оператор APPLY и его варианты — CROSS APPLY и OUTER APPLY, а также примеры их использования.

В частности, узнаем:

• разница между CROSS APPLY и предложением JOIN
• как объединить вывод SQL-запросов с табличными функциями
• , как определять проблемы с производительностью, запрашивая динамические представления управления и функции динамического управления.

Что такое APPLY Clause

Microsoft представила оператор APPLY в SQL Server 2005. Оператор APPLY аналогичен предложению T-SQL JOIN, поскольку он также позволяет объединить две таблицы — например, вы можете объединить внешнюю таблицу с внутренней таблицей. Оператор APPLY — хороший вариант, когда с одной стороны у нас есть вычисленное по таблице выражение, которое мы хотим вычислить для каждой строки из таблицы, которая у нас есть с другой стороны. Итак, правая таблица обрабатывается для каждой строки левой таблицы.Сначала оценивается левая таблица, а затем правая таблица сравнивается с каждой строкой левой таблицы для генерации окончательного набора результатов. Окончательный набор результатов включает все столбцы из обеих таблиц.

Оператор APPLY имеет два варианта:

КРЕСТ ПРИМЕНИТЬ

CROSS APPLY похож на INNER JOIN, но также может использоваться для соединения вычисляемых с помощью таблиц функций с таблицами SQL. Окончательный результат CROSS APPLY состоит из записей, совпадающих между выходными данными функции с вычислением таблицы и таблицей SQL.

НАРУЖНОЕ ПРИМЕНЕНИЕ

OUTER APPLY напоминает LEFT JOIN, но имеет возможность объединять функции с вычислением таблиц с таблицами SQL. Окончательный вывод OUTER APPLY содержит все записи из левой таблицы или функции с табличным значением, даже если они не совпадают с записями в правой таблице или функции с табличным значением.

Теперь позвольте мне объяснить оба варианта на примерах.

Примеры использования

Подготовка демонстрационной установки

Чтобы подготовить демонстрационную установку, вам нужно будет создать таблицы с именами «Сотрудники» и «Отдел» в базе данных, которую мы назовем «DemoDatabase».Для этого запустите следующий код:

 ИСПОЛЬЗОВАТЬ БАЗУ ДЕМОДАННЫХ
ИДТИ

СОЗДАТЬ ТАБЛИЦУ [DBO]. [СОТРУДНИКИ]
(
[EMPLOYEENAME] [VARCHAR] (MAX) NULL,
[BIRTHDATE] [DATETIME] NULL,
[JOBTITLE] [VARCHAR] (150) NULL,
[EMAILID] [VARCHAR] (100) NULL,
[PHONENUMBER] [VARCHAR] (20) NULL,
[HIREDATE] [DATETIME] NULL,
[DEPARTMENTID] [INT] NULL
)

ИДТИ

СОЗДАТЬ ТАБЛИЦУ [DBO]. [ОТДЕЛ]
(
[DEPARTMENTID] INT IDENTITY (1, 1),
[НАЗВАНИЕ ОТДЕЛА] [VARCHAR] (MAX) NULL
)
GO 

Затем вставьте фиктивные данные в обе таблицы.Следующий скрипт вставит данные в таблицу « Employee s »:

ПОЛНЫЙ ЗАПРОС

 INSERT [DBO]. [СОТРУДНИКИ]
([ИМЯ СОТРУДНИКА],
[ДАТА РОЖДЕНИЯ],
[ДОЛЖНОСТЬ],
[EMAIL ID],
[ТЕЛЕФОННЫЙ НОМЕР],
[ДАТА ПРИЕМА НА РАБОТУ],
[DEPARTMENTID])
ЦЕННОСТИ (N'KEN J SÁNCHEZ ',
CAST (N'1969-01-29T00: 00: 00.000 'КАК ДАТА ВРЕМЕНИ),
Н'ГЛАВНЫЙ ИСПОЛНИТЕЛЬНЫЙ ДИРЕКТОР ',
N'[email protected] ',
N'697-555-0142 ',
CAST (N'2009-01-14T00: 00: 00.000 'КАК ДАТА ВРЕМЕНИ),
1),
(Н'ТЕРРИ ЛИ ДАФФИ ',
CAST (N'1971-08-01T00: 00: 00.000 'КАК ДАТА ВРЕМЕНИ),
N'VICE PRESIDENT OF ENGINEERING ',
N'[email protected] ',
N'819-555-0175 ',
CAST (N'2008-01-31T00: 00: 00.000 'КАК ДАТА ВРЕМЕНИ),
ЗНАЧЕНИЕ NULL),
(Н'РОБЕРТО ТАМБУРЕЛЛО,
CAST (N'1974-11-12T00: 00: 00.000 'КАК ДАТА ВРЕМЕНИ),
МЕНЕДЖЕР ПО ТЕХНИКЕ »,
N'[email protected] ',
N'212-555-0187 ',
CAST (N'2007-11-11T00: 00: 00.000 'КАК ДАТА ВРЕМЕНИ),
ЗНАЧЕНИЕ NULL),
(Н'РОБ УОЛТЕРС,
CAST (N'1974-12-23T00: 00: 00.000 'КАК ДАТА ВРЕМЕНИ),
N'SENIOR TOOL DESIGNER ',
N'[email protected] ',
N'612-555-0100 ',
CAST (N'2007-12-05T00: 00: 00.000 'КАК ДАТА ВРЕМЕНИ),
ЗНАЧЕНИЕ NULL),
(Н'ГЕЙЛ ЭРИКСОН,
CAST (N'1952-09-27T00: 00: 00.000 'КАК ДАТА ВРЕМЕНИ),
N'DESIGN ENGINEER ',
N'[email protected] ',
N'849-555-0139 ',
CAST (N'2008-01-06T00: 00: 00.000 'КАК ДАТА ВРЕМЕНИ),
ЗНАЧЕНИЕ NULL),
(N'JOSSEF H GOLDBERG ',
CAST (N'1959-03-11T00: 00: 00.000 'КАК ДАТА ВРЕМЕНИ),
N'DESIGN ENGINEER ',
N'[email protected] ',
N'122-555-0189 ',
CAST (N'2008-01-24T00: 00: 00.000 'КАК ДАТА ВРЕМЕНИ),
ЗНАЧЕНИЕ NULL),
(Н'ДЫЛАН А МИЛЛЕР,
CAST (N'1987-02-24T00: 00: 00.000 'КАК ДАТА ВРЕМЕНИ),
N'RESEARCH AND DEVELOPMENT MANAGER ',
N'[email protected] ',
N'181-555-0156 ',
CAST (N'2009-02-08T00: 00: 00.000 'КАК ДАТА ВРЕМЕНИ),
3),
(Н'ДИАНА Л МАРГЕЙМ,
CAST (N'1986-06-05T00: 00: 00.000 'КАК ДАТА ВРЕМЕНИ),
ИНЖЕНЕР ПО ИССЛЕДОВАНИЯМ И РАЗРАБОТКАМ ',
N'[email protected] ',
N'815-555-0138 ',
CAST (N'2008-12-29T00: 00: 00.000 'КАК ДАТА ВРЕМЕНИ),
3),
(Н'ГИГИ Н МЭТЬЮ ',
CAST (N'1979-01-21T00: 00: 00.000 'КАК ДАТА ВРЕМЕНИ),
ИНЖЕНЕР ПО ИССЛЕДОВАНИЯМ И РАЗРАБОТКАМ ',
N'[email protected] ',
N'185-555-0186 ',
CAST (N'2009-01-16T00: 00: 00.000 'КАК ДАТА ВРЕМЕНИ),
3),
(Н'МИХАЭЛЬ РАХИМ,
CAST (N'1984-11-30T00: 00: 00.000 'КАК ДАТА ВРЕМЕНИ),
N'RESEARCH AND DEVELOPMENT MANAGER ',
N'[email protected] ',
N'330-555-2568 ',
CAST (N'2009-05-03T00: 00: 00.000 'КАК ДАТА ВРЕМЕНИ),
3) 

Чтобы добавить данные в нашу таблицу « Отдел », запустите следующий сценарий:

 INSERT [DBO]. [ОТДЕЛ]
([DEPARTMENTID],
[НАЗВАНИЕ ОТДЕЛА])
ЗНАЧЕНИЯ (1,
N'IT '),
(2,
N'TECHNICAL '),
(3,
N'ИССЛЕДОВАНИЯ И РАЗРАБОТКИ ') 

Теперь, чтобы проверить данные, выполните код, который вы видите ниже:

 ВЫБЕРИТЕ [ИМЯ СОТРУДНИКА],
[ДАТА РОЖДЕНИЯ],
[ДОЛЖНОСТЬ],
[EMAIL ID],
[ТЕЛЕФОННЫЙ НОМЕР],
[ДАТА ПРИЕМА НА РАБОТУ],
[DEPARTMENTID]
ОТ [СОТРУДНИКОВ]

ИДТИ

ВЫБЕРИТЕ [DEPARTMENTID],
[НАЗВАНИЕ ОТДЕЛА]
ОТ [ОТДЕЛ]
GO 

Вот желаемый результат:

Создание и тестирование функции с табличным вычислением

Как я уже упоминал, « CROSS APPLY » и « OUTER APPLY » используются для объединения таблиц SQL с функциями, оцениваемыми таблицами.Чтобы продемонстрировать это, давайте создадим табличную функцию с именем « getEmployeeData ». Эта функция будет использовать значение из столбца DepartmentID в качестве входного параметра и вернет всех сотрудников из соответствующего отдела.

Чтобы создать функцию, запустите следующий скрипт:

 СОЗДАТЬ ФУНКЦИЮ Getemployeesbydepartment (@DEPARTMENTID INT)
ТАБЛИЦА ВОЗВРАТОВ @ СОТРУДНИКИ
EMPLOYEENAME VARCHAR (MAX),
ДАТА РОЖДЕНИЯ, ВРЕМЯ,
РАБОТА ВАРЧАР (150),
EMAILID VARCHAR (100),
PHONENUMBER VARCHAR (20),
ПРИЕМ СРОКА,
DEPARTMENTID VARCHAR (500))
КАК
НАЧАТЬ
ВСТАВИТЬ В @ СОТРУДНИКОВ
ВЫБЕРИТЕ.ИМЯ СОТРУДНИКА,
А. ДАТА РОЖДЕНИЯ,
A. РАБОТА,
A.EMAILID,
A.PHONNUMBER,
A.HIREDATE,
A.DEPARTMENTID
ОТ [СОТРУДНИКОВ] A
ГДЕ A.DEPARTMENTID = @DEPARTMENTID

ВОЗВРАЩЕНИЕ
КОНЕЦ 

Теперь, чтобы протестировать функцию, мы передадим « 1 » как « DepartmentID » в функцию « Getemployeesbydepartment ». Для этого выполните сценарий, представленный ниже:

 ИСПОЛЬЗОВАТЬ БАЗУ ДЕМОДАННЫХ
ИДТИ
ВЫБЕРИТЕ ИМЯ СОТРУДНИКА,
       ДАТА РОЖДЕНИЯ,
       ДОЛЖНОСТЬ,
       EMAIL ID,
       ТЕЛЕФОННЫЙ НОМЕР,
       ДАТА ПРИЕМА НА РАБОТУ,
       DEPARTMENTID
ОТ ОТДЕЛЕНИЯ ПОЛУЧИТЬСЯ ОТПРАВЛЕНИЯ (1) 

Результат должен быть следующим:

Соединение таблицы с функцией вычисления таблицы с помощью CROSS APPLY

Теперь давайте попробуем объединить таблицу Employees с табличной функцией « Getemployeesbydepartment », используя CROSS APPLY .Как я уже упоминал, оператор CROSS APPLY похож на предложение Join. Он заполнит все записи из таблицы « Employee », для которых есть совпадающие строки в выходных данных « Getemployeesbydepartment ».

Запустите следующий сценарий:

 ВЫБЕРИТЕ A. [ИМЯ СОТРУДНИКА],
A. [ДАТА РОЖДЕНИЯ],
A. [РАБОТА],
A. [EMAILID],
A. [ТЕЛЕФОННЫЙ НОМЕР],
A. [НАЙМ],
Б. [НАЗВАНИЕ ОТДЕЛА]
ИЗ ОТДЕЛЕНИЯ B
ПЕРЕКРЕСТНОЕ ЗАЯВЛЕНИЕ ПОЛУЧИТЬ СОТРУДНИКОВ ОТДЕЛЕНИЕМ (Б.DEPARTMENTID) A 

Результат должен быть следующим:

Соединение таблицы с функцией, оцениваемой таблицей, с помощью OUTER APPLY

Теперь давайте попробуем объединить таблицу «Сотрудники» с табличной функцией « Getemployeesbydepartment », используя OUTER APPLY . Как я упоминал ранее, оператор OUTER APPLY похож на предложение « OUTER JOIN ». Он заполняет все записи из таблицы « Сотрудник » и выходных данных функции « Getemployeesbydepartment ».

Запустите следующий сценарий:

 ВЫБЕРИТЕ A. [ИМЯ СОТРУДНИКА],
A. [ДАТА РОЖДЕНИЯ],
A. [РАБОТА],
A. [EMAILID],
A. [ТЕЛЕФОННЫЙ НОМЕР],
A. [НАЙМ],
Б. [НАЗВАНИЕ ОТДЕЛА]
ИЗ ОТДЕЛЕНИЯ B
ВНЕШНЯЯ ЗАЯВКА НА РАБОТУ В ОТДЕЛЕНИИ (B.DEPARTMENTID) A 

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

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

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

В демонстрационных целях я создал таблицу с именем « SmokeTestResults » в «DemoDatabase». Он содержит результаты дымового теста приложения. Представим, что по ошибке разработчик выполняет SQL-запрос для заполнения данных из « SmokeTestResults » без добавления фильтра, что значительно снижает производительность базы данных.

Как администратор баз данных, нам необходимо определить ресурсоемкий запрос. Для этого мы будем использовать представление « sys.dm_exec_requests » и функцию « sys.dm_exec_sql_text ».

« Sys.dm_exec_requests » — это динамическое представление управления, которое предоставляет следующие важные сведения, которые мы можем использовать для идентификации запроса, потребляющего ресурсы:

1. Идентификатор сеанса
2. Процессорное время
3. Тип ожидания
4. Идентификатор базы данных
5. Чтений (физический)
6. Запись (физическая)
7. Логические чтения
8. Обработчик SQL
9. План-ручка
10. Статус запроса
11. Команда
12. Идентификатор транзакции

“ sys.dm_exec_sql_text ”- это функция динамического управления, которая принимает дескриптор SQL в качестве входного параметра и предоставляет следующие сведения:

1. Идентификатор базы данных
2. Идентификатор объекта
3. зашифровано
4. Текст запроса SQL

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

 ИСПОЛЬЗУЙТЕ КАК МОЖНО СКОРЕЕ
ИДТИ

ВЫБЕРИТЕ TSID,
ИДЕНТИФИКАТОР ПОЛЬЗОВАТЕЛЯ,
EXECUTIONID,
EX_RESULTFILE,
EX_TESTDATAFILE,
EX_ZIPFILE,
EX_STARTTIME,
EX_ENDTIME,
EX_REMARKS
ОТ [КАК МОЖНО СКОРЕЕ].[DBO]. [SMOKETESTRESULTS] 

SQL Server выделяет идентификатор сеанса «66» и начинает выполнение запроса. Смотрите следующее изображение:

Теперь для устранения проблемы нам потребуется идентификатор базы данных , логические чтения, SQL запрос, команда, идентификатор сеанса, тип ожидания и обработчик SQL . Как я уже упоминал, мы можем получить идентификатор базы данных, логические чтения, команду, идентификатор сеанса, тип ожидания и дескриптор SQL из sys.dm_exec_requests.Чтобы получить SQL Query , мы должны использовать « sys.dm_exec_sql_text. »Это функция динамического управления, поэтому потребуется объединить« sys.dm_exec_requests »с« sys.dm_exec_sql_text »с помощью CROSS APPLY.

В окне редактора нового запроса выполните следующий запрос:

 ВЫБРАТЬ B.TEXT,
A.WAIT_TYPE,
A.LAST_WAIT_TYPE,
A. КОМАНДА,
A.SESSION_ID,
CPU_TIME,
A.BLOCKING_SESSION_ID,
A.LOGICAL_READS
ОТ SYS.DM_EXEC_REQUESTS A
КРЕСТ ПРИМЕНИТЬ SYS.DM_EXEC_SQL_TEXT (A.SQL_HANDLE) B 

Должен выдать следующий результат:

Как видно на скриншоте выше, запрос вернул всю информацию, необходимую для определения проблемы с производительностью.

Теперь, помимо текста запроса, мы хотим получить план выполнения, который использовался для выполнения рассматриваемого запроса. Для этого воспользуемся функцией sys.dm_exec_query_plan .

“ sys.dm_exec_query_plan ”- это функция динамического управления, которая принимает дескриптор плана в качестве входного параметра и предоставляет следующие сведения:

1. Идентификатор базы данных
2. Идентификатор объекта
3. зашифровано
4. План запроса SQL в формате XML

Чтобы заполнить план выполнения запроса, мы должны использовать CROSS APPLY для соединения « sys.dm_exec_requests » и « sys.dm_exec_query_plan. ”

Откройте окно редактора нового запроса и выполните следующий запрос:

 ВЫБРАТЬ B.ТЕКСТ,
A.WAIT_TYPE,
A.LAST_WAIT_TYPE,
A. КОМАНДА,
A.SESSION_ID,
CPU_TIME,
A.BLOCKING_SESSION_ID,
A.LOGICAL_READS,
C.QUERY_PLAN
ОТ SYS.DM_EXEC_REQUESTS A
ПЕРЕКРЕСТИТЬ ПРИМЕНИТЬ SYS.DM_EXEC_SQL_TEXT (A.SQL_HANDLE) B
CROSS APPLY SYS.DM_EXEC_QUERY_PLAN (A.PLAN_HANDLE) C 

Результат должен быть следующим:

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

Получение списка таблиц с сильно фрагментированными индексами с использованием динамических административных представлений и функций

Рассмотрим еще один пример. Я хочу получить список таблиц с индексами, которые имеют 50% или более фрагментацию в данной базе данных. Чтобы получить эти таблицы, нам нужно будет использовать представление « sys.dm_db_index_physical_stats » и представление « sys.столы ”функция.

« Sys.tables » — это динамическое административное представление, которое заполняет список таблиц в конкретной базе данных.

« sys.dm_db_index_physical_stats » — это функция динамического управления, которая принимает следующие входные параметры:

1. Идентификатор базы данных
2. Идентификатор объекта
3. Идентификатор индекса
4. Номер раздела
5. Режим

Возвращает подробную информацию о физическом состоянии указанного индекса.

Теперь, чтобы заполнить список фрагментированных индексов, мы должны объединить « sys.dm_db_index_physical_stats » и « sys.tables » с помощью CROSS APPLY. Выполните следующий запрос:

 ВЫБРАТЬ ТАБЛИЦЫ.ИМЯ,
INDEXSTATISTICS.ALLOC_UNIT_TYPE_DESC,
ПРЕОБРАЗОВАТЬ (ЧИСЛО (10; 2); ИНДЕКССТАТИСТИКА.AVG_FRAGMENTATION_IN_PERCENT) КАК
ПРОЦЕНТНАЯ ДЕФРАГМЕНТАЦИЯ,
INDEXSTATISTICS.PAGE_COUNT
ИЗ SYS.TABLES КАК ТАБЛИЦЫ
ПЕРЕКРЕСТИТЬ ПРИМЕНИТЬ SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID (), ТАБЛИЦЫ.OBJECT_ID,
ЗНАЧЕНИЕ NULL,
NULL, NULL) КАК
ИНДЕКССТАТИСТИКА
ГДЕ INDEXSTATISTICS.DATABASE_ID = DB_ID ()
И AVG_FRAGMENTATION_IN_PERCENT> = 50
ЗАКАЗАТЬ ПО INDEXSTATISTICS.AVG_FRAGMENTATION_IN_PERCENT DESC 

Запрос должен выдать следующий результат:

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

Nisarg — администратор баз данных SQL Server и сертифицированный специалист Microsoft, имеющий более 5 лет опыта в администрировании SQL Server и 2 года в администрировании баз данных Oracle 10g. Он имеет опыт проектирования баз данных, настройки производительности, резервного копирования и восстановления, настройки высокой доступности и аварийного восстановления, миграции и обновления баз данных. Он получил степень бакалавра информационных технологий Университета Ганпат.

Последние сообщения от Nisarg Upadhyay (посмотреть все).

Использование T-SQL CROSS APPLY и OUTER APPLY

Введение

Частью моей добровольной работы всегда было изучение и внедрение новых технологий. В рамках этого процесса я делюсь тем, что узнал, написав об этом. Количество информации и контента, которые производятся каждый год, намного, намного больше, чем кто-либо может освоить, поэтому я выбираю. Как правило, я сосредотачиваюсь на объектно-ориентированных технологиях и языках, UML, шаблонах проектирования, рефакторинге и SQL. Как правило, эти области у меня хорошо проработаны.К сожалению, компромисс состоит в том, что я никогда не смогу освоить Ruby или по-настоящему понять разницу между Ruby и Ruby on Rails. Я согласен с этим решением.

Тем не менее, несмотря на то, что я сосредоточен в основном на ОО, я все равно скучаю по вещам. SQL Server 2005 вышел с операторами CROSS APPLY и OUTER APPLY, и я только начал изучать, как использовать APPLY в последний месяц или около того. Когда мне кажется, что я понял это, мне повезло, что некоторым из вас интересно прочитать о моем понимании технологии.

Из справки MSDN «оператор APPLY позволяет вызывать возвращающую табличное значение функцию для каждой строки, возвращаемой внешним табличным выражением запроса. Возвращающая табличное значение функция действует как правый ввод, а внешняя таблица действует как левый ввод. . » А ?! Технически я понимаю табличные функции, левый и правый ввод, но это объяснение на самом деле не говорит мне о том, зачем мне это нужно или когда это необходимо. В справочной документации мне не сказано, что делать с APPLY. Итак, эта статья представляет мою обработку и разделение APPLY, что поможет мне узнать, когда его следует вытащить из моего набора инструментов и использовать.

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

Общие сведения об иерархии отдельных таблиц

Распространенная проблема — представление иерархии данных в базе данных. Решение состоит в том, чтобы поместить все данные в иерархию в единую таблицу — например, сотрудников и менеджеров — и установить отношения с ключами. Например, AdventureWorks делает это с помощью HumanResources.Таблицы сотрудников и лиц. Сотрудник содержит сотрудников, которые, в свою очередь, включают менеджеров и представляют иерархическую информацию, а Контакт содержит такую ​​информацию, как имя и фамилия Сотрудника.

Классический способ представления иерархии — генеалогические отношения: мама и папа, дедушка и бабушка, дочери и сыновья. Например, у любого человека есть один набор родителей, поэтому два ключа — один для мамы и один для папы — и любая строка, представляющая человека, имеет два ключа, относящихся к родителям этого человека.Братья и сестры будут иметь идентичные родительские идентификаторы, сводные братья и сестры будут иметь по крайней мере один общий родительский ключ, а также могут быть определены тети, дяди, кузены и т. Д.

Чтобы сгенерировать CROSS APPLY и OUTER APPLY, вы можете использовать сценарий из листинга 1, чтобы создать простую таблицу, содержащую одного человека с дополнительными ключами к его родителям. Скопируйте и вставьте код в Microsoft SQL Server Management Studio (его версию Expression) и запустите сценарий.

Листинг 1: Таблица с именем People, в которой есть столбцы, которые ссылаются на другие строки в той же таблице; другие строки представляют «родителей».

Используйте генеалогию
Идти

--Создайте таблицу People и вставьте значения.
СОЗДАТЬ ТАБЛИЦЫ Люди
(
   PersonID int NOT NULL,
   MotherID int NULL,
   FatherID int NULL,
   Имя varchar (25) NOT NULL,
   ОГРАНИЧЕНИЕ PK_People ПЕРВИЧНЫЙ КЛЮЧ (PersonID),
)
ИДТИ

ВСТАВИТЬ В ЗНАЧЕНИЯ людей (1, NULL, NULL, 'Джек Саймонс')
ВСТАВИТЬ В ЗНАЧЕНИЯ людей (2, NULL, NULL, 'Marvel Symons')
ВСТАВИТЬ В ЗНАЧЕНИЯ людей (3, NULL, NULL, 'Anna Kimmel')
ВСТАВИТЬ В ЗНАЧЕНИЯ людей (4, NULL, NULL, 'Фрэнк Киммел')
ВСТАВИТЬ В ЦЕННОСТИ людей (5, 2, 1, «Жаклин Бенавидес»)
ВСТАВЬТЕ В ЦЕННОСТИ людей (6, 3, 4, 'Джеральд Киммел')
ВСТАВИТЬ В ЦЕННОСТИ людей (7, 5, 6, «Кэти Хеменуэй»)
ВСТАВИТЬ В ЦЕННОСТИ людей (8, 5, 6, 'Daniel Kimmel')
ВСТАВЬТЕ В ЦЕННОСТИ людей (9, 5, 6, 'Дэвид Киммел')
ВСТАВИТЬ В ЦЕННОСТИ людей (10, 5, 7, Роберт Бенавидес)
ВСТАВЬТЕ В ЦЕННОСТИ людей (11, 5, 7, 'Николас Бенавидес')
ВСТАВЬТЕ В ЦЕННОСТИ людей (12, 5, 6, 'Джеймс Киммел')
ВСТАВЬТЕ В ЦЕННОСТИ людей (13, 5, 6, 'Paul Kimmel')
ВСТАВИТЬ В ЗНАЧЕНИЯ людей (14, NULL, NULL, 'Lori Kimmel')
ВСТАВИТЬ В ЗНАЧЕНИЯ людей (15, NULL, NULL, 'Дэвид Бенавидес')
ВСТАВЬТЕ В ЦЕННОСТИ людей (16, 14, 13, 'Alex Kimmel')
ВСТАВЬТЕ В ЦЕННОСТИ людей (17, 14, 13, «Ной Киммел»)

ИДТИ
 

Если вы это нанесете на карту, одна ветвь иерархии покажет Джеральда Киммела и Жаклин Бенавидес как моих родителей.Мой PersonID — 13, мой MotherID — 5, а FatherID — 6. Все, у кого идентификаторы родителей 5 и 6, являются моими братьями и сестрами.

Задача заключается в следующем: «Как можно написать единый запрос, который будет правильно возвращать иерархию отношений для одной таблицы?»

Использование CROSS APPLY

Стоит отметить, что ребенок, мать и отец могут быть извлечены на основе таблицы People с помощью самостоятельного соединения (см. Листинг 2). Присоединяясь к People.MotherID на People.PersonID и People.FatherID на People.PersonID, вы можете построить иерархию в предыдущем сценарии.

Листинг 2: Используйте несколько самостоятельных соединений для построения результатов иерархии.

выберите p1.Name как MyName, p2.Name как Mother, p3.Name As Father
от людей p1 left присоединиться к людям p2 на p1.MotherID =
   p2.PersonID left присоединяется к людям p3 на p1.FatherID = p3.PersonID
 

Однако есть ограничения. Вы не можете использовать функцию с табличным значением, передавая внешний параметр запроса, и вы не можете использовать вложенный подзапрос, который возвращает несколько строк.Например, учитывая функцию GetParents (см. Листинг 3), которая принимает PersonID и возвращает информацию о мамах и папах, вы не можете использовать внешний запрос PersonID для вызова функции (см. Листинг 4).

Листинг 3: Функция табличного значения, возвращающая информацию о мамах и папах.

ИСПОЛЬЗУЙТЕ [Генеалогия]
ИДТИ
/ ****** Объект: UserDefinedFunction [dbo]. [GetParents]
        Дата сценария: 22.01.2009 14:29:00 ****** /
УСТАНОВИТЬ ANSI_NULLS ON
ИДТИ
ВКЛЮЧИТЬ QUOTED_IDENTIFIER
ИДТИ
ИЗМЕНИТЬ ФУНКЦИЮ [dbo].[GetParents] (@ PersonID int)
ВОЗВРАТ @Parents TABLE
(
   [PersonID] [int] PRIMARY KEY NOT NULL,
   [Self] [varchar] (25),
   [Мать] [varchar] (25) NULL,
   [Отец] [varchar] (25) NULL
)
КАК
НАЧАТЬ
   ВСТАВИТЬ @Parents
   ВЫБРАТЬ
      p1.PersonID,
      p1.Name AS [Self],
      p2. [Имя] КАК Мать,
      p3. [Имя] КАК Отец ОТ
      Люди p1 INNER JOIN Люди p2 ON
      p1.MotherID = p2.PersonID INNER JOIN
      Люди p3 НА p1.FatherID = p3.PersonID
   ГДЕ
      p1.PersonID = @PersonID;
   ВОЗВРАЩЕНИЕ;
КОНЕЦ;
 

Листинг 4: Этот код не будет работать, потому что значение внешнего запроса не может быть передано в функцию.

выберите p1.PersonID, p1.Name, GetParents (p1.PersonID)
ОТ людей p1
 

Есть много причин, по которым вы можете предпочесть функцию: функция уже существует, повторное использование кода и изоляция. Вот где сияет CROSS APPLY. При наличии функции вы можете использовать CROSS APPLY для вызова функции GetParents, передав PersonID от select функции (см. Листинг 5).

Листинг 5: CROSS APPLY возвращает те же данные, что и несколько внутренних соединений, и вызывает функцию со значением из оператора select.

выберите p1.PersonID, p1.Name, p2.Mother, p2.Father
ОТ людей p1
ПЕРЕКРЕСТНОЕ ПРИМЕНЕНИЕ GetParents (p1.PersonID) p2
 

Листинг 5 возвращает иерархию, в которой есть отец и мать. Если вы измените CROSS APPLY на OUTER APPLY, вы получите строки без родителей. Короче говоря, CROSS APPLY отвечает аналогично INNER JOIN, а OUTER APPLY аналогично LEFT JOIN. В листинге 6 представлено полное решение с CROSS APPLY и без функции, а в листинге 7 показано OUTER APPLY без функции.

Листинг 6: Сборка данных о себе, матери и отце, где есть матери и отцы, с использованием CROSS APPLY.

ВЫБЕРИТЕ p1.PersonID, p1. [Имя], M.Name как Mother,
   F. Имя отца ОТ ЛЮДЕЙ стр.
КРЕСТНОЕ ПРИМЕНЕНИЕ
(ВЫБЕРИТЕ p2.PersonID, p2. [Имя] ОТ ЛЮДЕЙ p2
   ГДЕ p1.MotherID = p2.PersonID) M
КРЕСТНОЕ ПРИМЕНЕНИЕ
(ВЫБЕРИТЕ PersonID, [Имя] ОТ ЛЮДЕЙ p3
   ГДЕ p1.FatherID = p3.PersonID) F
 

Листинг 7: Сборка данных о себе, отце и матери, где данные отца и / или матери имеют значение NULL, с помощью OUTER APPLY.

ВЫБЕРИТЕ p1.PersonID, p1 

.