Функции среды CLR с табличным значением

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

Начиная с SQL Server 2005, SQL Server расширяет возможности возвращающих табличное значение функций, позволяя определить функцию с табличным значением на любом управляемом языке. Функция с табличным значением возвращает данные через объект IEnumerable или IEnumerator.

Для функций с табличным значением столбцы возвращаемого табличного типа не могут включать столбцы отметок времени и столбцы строкового типа данных не в Юникоде (например, char, varchar и text). Ограничение NOT NULL не поддерживается.

Различия между функциями Transact-SQL и среды CLR с табличным значением

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

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

Реализация функций с табличным значением

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

Параметры, возвращающие табличные значения

Возвращающие табличное значение параметры — это определяемые пользователем табличные типы, которые передаются в процедуру или функцию, предоставляя эффективный способ передачи на сервер нескольких строк данных. Возвращающие табличное значение параметры выполняют функции, аналогичные массивам параметров, но обладают большей гибкостью и лучше интегрируются с Transact-SQL. Они также обеспечивают возможность повышения производительности. Кроме того, возвращающие табличное значение параметры способствуют сокращению циклов приема-передачи данных с сервера и на сервер. Вместо того чтобы отправлять на сервер несколько запросов (как в случае списка скалярных параметров), данные можно отправить в виде возвращающего табличное значение параметра. Определяемый пользователем табличный тип нельзя передавать в виде возвращающего табличное значение параметра в управляемую хранимую процедуру или функцию, которая выполняется в процессе SQL Server. Кроме того, такие процедуры и функции не могут возвращать определяемые пользователем табличные типы. Дополнительные сведения о возвращающих табличное значение параметрах см. в разделе Использование параметров, возвращающих табличные значения (компонент Database Engine).

Выходные параметры и функции с табличным значением

Возврат данных из функции с табличным значением может производиться через выходные параметры. Соответствующий аргумент в коде реализации функции с табличным значением должен передаваться по ссылке. Следует отметить, что язык Visual Basic не поддерживает выходные параметры так, как они поддерживаются в языке Visual C#. Для представления выходного параметра необходимо задать параметр по ссылке и применить атрибут <Out()>, как показано в следующем примере.

Синтаксис определения функции CLR с табличным значением напоминает синтаксис объявления функции Transact-SQL с табличным значением с добавлением предложения EXTERNAL NAME. Например:

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

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

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

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

Примечание. Функция с табличным значением может производить доступ к данным только через запрос Transact-SQL через метод InitMethod, но не через метод FillRow. Метод InitMethod не должен быть помечен свойством атрибута SqlFunction.DataAccess.Read, если выполняется запрос Transact-SQL.

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

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

После компиляции образца возвращающей табличное значение функции его можно объявить в Transact-SQL следующим образом.

Выполнение объектов базы данных, написанных на языке Visual C++ и скомпилированных с параметром /clr:pure, не поддерживается в SQL Server 2005. Например, в число таких объектов базы данных входят функции с табличным значением.

Чтобы проверить образец, выполните следующий код Transact-SQL.

Образец. Возвращение результатов запроса SQL Server

В следующем образце показана возвращающая табличное значение функция, которая запрашивает базу данных SQL Server. В этом образце используется база данных AdventureWorks Light из SQL Server 2008. Дополнительные сведения о загрузке AdventureWorks см. на веб-узле https://www.codeplex.com/sqlserversamples.

Задайте для файла исходного кода имя FindInvalidEmails.cs или FindInvalidEmails.vb.

Скомпилируйте исходный код в библиотеку DLL и скопируйте ее в корневой каталог диска C. Затем выполните следующий запрос Transact-SQL.