Версия для печати

На главную страницу

Вызов UDF, которые принимают таблицы с помощью оператора APPLY в SQL Server 2005

Определенная пользователем функция (UDF) - объект базы данных, введенный в SQL Server 2000. Такие функции бывают двух типов: скалярнозначные и табличнозначные UDF. Скалярнозначная функция возвращает единственное значение при каждом вызове функции, в то время как табличнозначная UDF возвращает табличный набор записей, которой можно соединить с другими таблицами/результирующими наборами. Для более детального знакомства с табличнозначными функциями UDF, см.статью Артура Фаллера.

Ограничения

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

Сначала, я создаю таблицу и наполняю ее данными:

Листинг А

CREATE TABLE TempTable

(
      IDCol INT IDENTITY(1,1),
      Fld1 VARCHAR(10),
      Fld2 INT
)

GO

INSERT INTO TempTable(Fld1, Fld2) VALUES('tim1',1)
INSERT INTO TempTable(Fld1, Fld2) VALUES('tim2',2)
INSERT INTO TempTable(Fld1, Fld2) VALUES('tim3',3)
INSERT INTO TempTable(Fld1, Fld2) VALUES('tim4',4)
INSERT INTO TempTable(Fld1, Fld2) VALUES('tim5',5)
INSERT INTO TempTable(Fld1, Fld2) VALUES('tim6',6)
INSERT INTO TempTable(Fld1, Fld2) VALUES('tim7',7)
INSERT INTO TempTable(Fld1, Fld2) VALUES('tim8',8)
INSERT INTO TempTable(Fld1, Fld2) VALUES('tim9',9)
INSERT INTO TempTable(Fld1, Fld2) VALUES('tim10',10)

Go

Затем я создаю UDF, чтобы вытянуть данные из таблицы, как показано в листинге B:

Листинг В

CREATE FUNCTION udf_TestFunction
(
      @IDCol INT
)

RETURNS @Tim TABLE
(
      IDCol INT ,
      Fld1 VARCHAR(10),
      Fld2 int
)
AS
BEGIN
      INSERT INTO @Tim
      SELECT IDCol, Fld1, Fld2 FROM TempTable
      WHERE IDCol = @IDCol
      RETURN
END

GO

Это очень простая табличнозначная функция. Я передаю значение IDCol в функцию, и возвращаю строку, соответствующую IDCol в таблице TempTable. Я могу вызвать эту функцию с помощью простого запроса SELECT к UDF:

SELECT * FROM dbo. udf_TestFunction(5)

Я могу даже соединить эту функцию с таблицей TempTable для возвращения, например, такого набора записей:

SELECT *
FROM dbo.udf_TestFunction(5) f
INNER JOIN TempTable t ON f.IDCol = t.IDCol

Хотя это не практический пример, он иллюстрирует, что Вы можете соединить табличнозначную UDF с другими объектами (даже другими табличнозначными функциями). В SQL Server 2000 это работает, если параметры, которые Вы передаете в UDF, - скалярные переменные. SQL Server 2000 не поддерживает передачу значений строки в табличнозначную UDF в операции JOIN. Например, следующий запрос приводит к ошибке в SQL Server 2000:

SELECT *
FROM TempTable t
INNER JOIN dbo.fn_Test(t.IDCol)  f on t.ColID = f.ColID

Однако в SQL Server 2005 эта проблема решена с введением оператора APPLY.

Оператор APPLY

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

Есть два варианта оператора APPLY: CROSS APPLY и OUTER APPLY. Оператор CROSS APPLY подобен конструкции INNER JOIN в transact-sql. Возвращаемые результаты основаны на критериях, содержащихся в UDF, и возвращаются после того, как необходимые значения из внешней таблицы передаются в UDF как параметры. Вы легко можете переписать вышеприведенный запрос для SQL Server 2005, используя конструкцию CROSS APPLY следующим образом:

SELECT * FROM TempTable t CROSS APPLY dbo.fn_Test(t.IDCol)

Оператор OUTER APPLY подобен конструкции OUTER JOIN в transact-sql. Подобно OUTER JOIN, OUTER APPLY возвращает все строки из внешней таблицы и любые строки из табличнозначной UDF, которые удовлетворяют критериям для переменных, передаваемых в функцию. Чтобы использовать в своих интересах эти функциональные возможности, просто замените CROSS APPLY на OUTER APPLY в нашем предыдущем операторе:

SELECT * FROM TempTable t OUTER APPLY dbo.fn_Test(t.IDCol)

Конструкция CROSS APPLY (когда она относится к табличнозначной UDF) весьма подобна способу, которым работает коррелированый подзапрос. За дополнительной информацией относительно коррелированого подзапроса обратитесь к моей недавней статье. Похожесть состоит в том, что значение из внешнего запроса (в нашей ситуации это первая таблица) извлекается и передается в подзапрос, которым в этом случае является UDF.

Информация к размышлению

Ценно иметь возможность передавать значения из строк таблицы в UDF; это позволит Вам решать много проблем, с которыми Вы можете столкнуться в ваших приложениях. Однако всегда приходится чем-то жертвовать, когда Вы получаете дополнительные функциональные возможности.

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

Очень частное мнение (комментарий к статье)

Joe Celko

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

Правильный ответ - никогда не передавать имя таблицы как параметр. Вы должны понять основную идею модели данных и что означает таблица применительно к модели данных. Вернемся к основам. Что такое таблица? Модель есть множество сущностей или отношений. КАЖДАЯ ТАБЛИЦА ДОЛЖНА БЫТЬ РАЗЛИЧНОГО ВИДА СУЩНОСТЬЮ. Когда Вы имеете много таблиц, которые моделируют ту же самую сущность, то Вы имеете файловую систему на магнитной ленте, написанную на SQL, а совсем не РСУБД.

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

20-09-2007

На главную страницу

  Версия для печати