В Microsoft SQL Server 2000 появился новый тип table, используемый в языке Transact-SQL. Тип table подобен временным таблицам. Тип table можно использовать с целью сохранения результирующего набора для последующей обработки. Тип table можно использовать только для определения локальных переменных данного типа и возврата значения из функции пользователя. Основное использование типа table – временное хранилище для набора строк, которые возвращаются как результирующий набор таб-личнозначной функции.

Область применения

Подобно другим локальным переменным, переменная table имеет вполне определенную область применения, - это функция, хранимая процедура или пакет, в котором эта пе-ременная объявлена. В пределах этой области переменная table может использоваться так, как будто это обычная таблица. Переменная table может находиться там, где ожидается присутствие таблицы или табличного выражения в операторах SELECT, INSERT, UPDATE и DELETE. Однако вы не можете использоваться переменную table в следующих случаях:

INSERT INTO табличная_переменная EXEC хранимая_процедура
SELECT список_select INTO табличная_переменная

Объявление

При объявлении переменной переменная table инициализируется как пустая таблица. Если вы выполняете оператор SELECT сразу после объявления табличной переменной, то увидите пустой набор строк. Не допускается присвоение одной табличной переменной другой.

Индексы

Другой важной особенностью типа table является то, что не допускается создание индексов на табличных переменных за исключением индексов, которые создаются ограничениями PRIMARY KEY и UNIQUE. При доступе к табличным переменным оптимизатор использует индексы, созданными ограничениями PRIMARY KEY и UNIQUE на табличной переменной.

Следующий вывод демонстрирует Clustered Index Seek (поиск в кластерном индексе):

Set statistics profile on
Go
Declare @Mytable TABLE (lname varchar (30) Primary Key, fname varchar (30))
Insert into @Mytable
Select distinct au_lname, au_fname from authors where au_lname like 'B%'
Select * from @Mytable where lname = 'Bennet'
Go

Транзакции

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

ФУНКЦИИ ПОЛЬЗОВАТЕЛЯ

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

Функция пользователя (UDF) представляет собой модуль Transact-SQL, который принимает один или более входных аргументов и подсчитывает возвращаемое значение. Например, функция может вычислять расстояние между двумя точками, формировать список сотрудников, работающих под управлением заданного менеджера.

UDF подобны хранимым процедурам. Т.е. UDF может содержать как операторы управления ходом выполнения программы, так и операторы DML.

Отличие UDF от хранимых процедур состоит в следующем:

• UDF не поддерживает выходных параметров.
• UDF не может использоваться для получения результатов “FOR XML”. Результат “FOR XML” может быть в хранимой процедуре.
• Ошибки Transact-SQL, которые вызывают отмену оператора с последующим переходом к следующему оператору в модуле (триггере или хранимой процедуре), внутри функции обрабатываются иначе. В функциях ошибки Transact-SQL прерывают выполнение функции. Это, в свою очередь, приводит к отмене оператора, который вызвал функцию.

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

Определение функции пользователя

Прежде чем определить UDF, вы должны определить характеристики UDF, такие как имя UDF, схема (квалификатор), число и типы данных входных параметров, типы возвращаемых значений. Затем вы выполняете оператор CREATE FUNCTION для создания UDF. После определения функции, если вы обнаружите, что некоторые из этих характеристик не подходят для функции, вы можете использовать оператор ALTER FUNCTION для изменения ее определения. Однако Вы не можете использовать оператор ALTER FUNCTION для преобразования скалярнозначной функции в табличнозначную и наоборот. Аналогично, вы не можете использовать ALTER FUNCTION для преобразования однострочной (inline) функции в многооператорную и наоборот. Вы можете удалить UDF при помощи оператора DROP FUNCTION.

Вы должны иметь привилегию CREATE FUNCTION для создания, изменения и удаления UDF. Пользователи, которые не являются владельцем, должны получить соответствующие привилегии на функцию, прежде чем они смогут использовать функцию в операторах Transact-SQL. Чтобы создать или изменить таблицы со ссылками на UDF в ограничении CHECK, предложении DEFAULT или определении вычисляемого столбца, вы должны также иметь привилегию REFERENCES на эти функции.

Типы операторов, которые могут использоваться в функции:

• Операторы DECLARE, которые вы можете использовать для определения переменных и курсоров, локальных относительно функции.
• Присвоение значений объектам, локальным относительно функции, например, с помощью опции SET для присвоения значений скалярным и табличным локальным переменным.
• Операции с локальными курсорами: объявление, открытие, закрытие и освобождение памяти. Операторы FETCH, которые возвращают данные клиенту, не допускаются. Допускаются только такие операторы FETCH, которые присваивают значения локальным переменным в предложении INTO.
• Операторы управления (например, IF..ELSE, WHILE, RETURN, GOTO, BREAK и CONTINUE). Операторы SELECT, которые содержат список выбора с выражениями, которые присваивают значения локальным переменным функции.
• Операторы UPDATE, INSERT и DELETE, которые модифицируют локальные табличные переменные функции. • Операторы EXECUTE, которые вызывают расширенные хранимые процедуры.

Следующие правила действуют для функций, вызывающих расширенные хранимые процедуры:

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

Функции, которые вызывают расширенные хранимые процедуры, помечаются как недетерминированные.

• Вызываемые расширенные хранимые процедуры выполняются в контексте учетной записи Microsoft Windows® security, под которой запускается SQL Server. Владелец хранимой процедуры должен иметь это в виду, предоставляя привилегии EXECUTE пользователям.

Детерминизм функций

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

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

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

Чтобы функция была детерминированной, необходимо выполнение следующих условий:

• Она не должна осуществлять доступ ни к каким таблицам, кроме тех, которые определены локально.
• Она не вызывает никакой недетерминированной встроенной функции.
• Она не вызывает никакой недетерминированной UDF. • Она привязана к схеме.
• Она не вызывает никакой расширенной хранимой процедуры (xp - extended stored procedure).

Функции, связанные со схемой

Если для функции определено WITH SCHEMABINDING, функция оказывается схемно-привязанной к объектам базы данных (таблицам, но не табличным переменным, представлениям и другим UDF), на которые функция имеет ссылки.

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

• UDF и представления, на которые имеются ссылки в функции, являются также привязанными к схеме.
• Объекты, на которые имеются ссылки в функции, находятся все в той же базе данных, что и сама функция. Ссылки на все такие объекты должны использовать имена, состоящие из двух частей или одной.
• Пользователь, выполняющий оператор CREATE FUNCTION имеет привилегию REFERENCES на все объекты БД (таблицы, представления и UDFs), на которые функция содержит ссылки.

Типы функций

SQL Server 2000 поддерживает три типа UDF:

• Скалярнозначные функции
• Однострочные (inline) табличнозначные функции
• Многооператорные табличнозначные фунции

Скалярнозначные функции

Пользовательская скалярная функция возвращает в качестве ответа единственное значение при каждом вызове функции. Функции являются скалярнозначными, если предложение RETURNS определяет один из скалярных типов данных. Скалярнозначные функции могут быть определены с помощью нескольких операторов Transact-SQL. Например:

CREATE FUNCTION CubicVolume
(@CubeLength decimal (4,1), @CubeWidth decimal (4,1), @CubeHeight decimal (4,1))
RETURNS decimal (12,3) -- тип данных возвращаемого значения.
AS
BEGIN
     RETURN (@CubeLength * @CubeWidth * @CubeHeight)
END

В данном примере предложение RETURNS определяет, что функция будет возвращать скалярное значение типа decimal. Предложение RETURNS может использовать любой из скалярных типов данных, которые поддерживает SQL Server, за исключением timestamp, text, ntext или image.

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

Вы должны вызывать скалярнозначную функцию либо при помощи двойного имени в форме ИмяВладельца.ИмяФункции или тройного имени в форме ИмяБД. ИмяВладельца.ИмяФункции. Вы не можете вызывать скалярнозначную функцию при помощи простого (неуточненного) имени, чтобы отличать вызовы системных функций и UDF. Приведенную выше функцию можно вызвать следующим образом:

Select dbo.CubicVolume(12.2,10.6,10.0)

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

Для однострочных табличнозначных функций предложение RETURNS задает TABLE без указания списка столбцов. Однострочные функции представляют собой табличнозначные функции, которые определяются единственным оператором SELECT, представляющим тело функции. Столбцы, включающие типы данных таблицы, возвращаемой функцией, определяются списком SELECT оператора SELECT, который определяет функцию. Например:

CREATE FUNCTION fn_CustomerNamesInRegion
      (@RegionParameter nvarchar (30))
RETURNS TABLE
AS
RETURN 
     (
     SELECT CustomerID, CompanyName 
         FROM Northwind.dbo.Customers 
         WHERE Region = @RegionParameter 
     )

Предложение RETURNS в примере просто указывает TABLE без списка столбцов. Также единственный оператор RETURN, который содержит оператор SELECT, определяет тело функции.

Ниже приведен пример вызова рассмотренной выше однострочной табличнозначной функции:

SELECT * FROM fn_CustomerNamesInRegion (N'WA')

Многооператорные табличнозначные функции

Если предложение RETURNS задает тип TABLE с определением столбцов и их типов данных, функция является многооператорной табличнозначной функцией. Например:

CREATE FUNCTION LargeOrderShippers 
    (@FreightParm money)
RETURNS @OrderShipperTab TABLE 
    ( 
    ShipperID int, 
    ShipperName nvarchar (80), 
    OrderID int, 
    ShippedDate datetime, 
    Freight money 
    )
AS
BEGIN 
    INSERT @OrderShipperTab 
        SELECT S.ShipperID, S.CompanyName, 
            O.OrderID, O.ShippedDate, O.Freight 
        FROM Shippers AS S INNER JOIN Orders AS O 
            ON S.ShipperID = O.ShipVia 
        WHERE O.Freight > @FreightParm 
    RETURN
END

Предложение RETURNS в данном примере определяет локальную возвращаемую переменную типа table с именем @OrderShipperTab, а также определяет структуру таблицы с определениями столбцов.

Операторы в теле функции вставляют строки в переменную @OrderShipperTab, создавая табличный набор строк, который возвращается функцией. Заметим, что оператор RETURN не имеет аргумента. Значение возвращаемой переменной функции возвращается как значение функции.

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

SELECT *
FROM LargeOrderShippers ($500)

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

Правила вызова функций пользователя

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

Как вызвать скалярнозначную функцию пользователя?

Вычисляемые столбцы

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

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

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

Вы можете использовать данный запрос для получения списка сотрудников, которые получили премию больше $1000.00:

Select EmpId, Fname, Lname from Employees where dbo.BONUS(Salary,Comm) > 1000

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

Если вычисляемый столбец создается с помощью пользовательской функции, которая рассчитывает премию (BONUS)

CREATE TABLE [Employees] 
       (
       [EmpId] [Char] (9) NOT NULL, 
       [Salary] [Decimal](10,2),
       [Comm] [Decimal](10,2), 
       BONUS as dbo.BONUS(Salary,Comm)
       )

и вы создаете индекс на вычисляемом столбце BONUS

CREATE NONCLUSTERED INDEX Nc_Idx3 ON Employees (BONUS)

то для следующего запроса оптимизатор выполнит индексный поиск (Index Seek):

Select EmpId, Fname, Lname from Employees where BONUS > 1000

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

Запросы

Скалярнозначные функции могут вызываться в запросе, и они логически вызываются по разу на строку.

Список Select

Наиболее частое использование UDF находит в вызовах в предложении select оператора SELECT.

Скалярнозначная функция MyDateFormat возвращает строковое представление значения datetime в пользовательском формате, который не поддерживается SQL Server. Входное значение datetime и символ-разделитель используются в качестве параметров. Например:

use pubs
go
create function MyDateFormat(@indate datetime, @Separator char(1)=’-‘)
returns nchar(20)
as
begin 
    return 
    convert(nvarchar(20),
    datepart(dd, @indate))
    + @Separator
    + convert(nvarchar(20), 
    datepart(mm, @indate))
    + @Separator
    + convert(nvarchar(20),
    datepart(yy, @indate))
end
go

-- Выборка
select ord_num, dbo.MyDateFormat(ord_date, ‘:’)
from sales
go

Другим хорошим примером использования UDF в списке выбора оператора SELECT является Web-сайт, обеспечивающий персонализацию пользователя. Web-сайт отображает фондовые квоты, счет бейсбольных матчей и десятку новостейдня, а также обеспечивает пользователя локализованным прогнозом погоды. Лучшим способом получить информацию о погоде является написание функции, которая возвращает высшую и низшую температуры на основе вводимого пользователем zip-кода места или города проживания. Например:

Select dbo.weather (Zip) from Address

Эта функция использует информацию zip-кода или города и адресует запрос к БД на ближайшей метеостанции. Затем функция возвращает данные типа varchar, которые содержат строку с разделенными запятыми высшую и низшую температуры для каждого дня и характеристику погодных условий.

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

Select PROPER (name), address, city, state, zip from customers

Как отмечалось выше, UDF в списке SELECT (или в предложении WHERE) выполняются один раз для каждой строки табличного выражения в предложении FROM. Планы выполнения скалярнозначной функции кешируются, поэтому не будет перекомпиляции, связанной к каждым вызовом UDF в запросе. Однако имеются потери производительности, связанные с вызовом кешированного плана, установкой параметров и возвратом результирующего значения. Из-за этих потерь, если тело UDF содержит единственное скалярное выражение, запрос, вызывающий UDF, выполняется медленнее, чем запрос, в котором записано это выражение. Например:

create function multiply (int a, int b)
Returns int as
Begin return a*b
End
Select multiply (T.a, T.b) from T,
-- will be slower than
Select a*b from T

Если запрос включает большое число строк и если вычисление ограничивается единственным выражением, рассмотрите вариант отказаться от использования UDF; вместо этого используйте встроенное в запрос выражение. Однако если вычисление не может быть выражено в виде единственного скалярного выражения, пишите запрос с использованием UDF.