Использование параметров, возвращающих табличные значения (компонент Database Engine)

Статья
06/25/2013

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

Возвращающие табличные значения параметры похожи на массивы параметров в OLE DB и ODBC, но они обеспечивают большую гибкость и больше интегрированы с Transact-SQL. Преимуществом возвращающих табличные значения параметров также является возможность участия в операциях, основанных на наборах.

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

В этом разделе:

Преимущества

Ограничения

Возвращающие табличное значение параметры и операции BULK INSERT

Пример

Преимущества

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

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

Не запрашивают блокировки для первичного заполнения данными от клиента.
Предоставляют простую модель программирования.
Позволяют включать в одиночную процедуру сложную бизнес-логику.
Сокращают количество циклов приема-передачи с сервером.
Могут иметь структуру таблицы с другим количеством элементов.
Строго типизированы.
Позволяют клиенту указать порядок сортировки и уникальные ключи.

Значок стрелки, используемый со ссылкой «В начало» [Top]

Ограничения

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

SQL Server не ведет статистику столбцов возвращающих табличные значения параметров.
Возвращающие табличные значения параметры должны передаваться процедурам Transact-SQL как входные параметры типа READONLY. Над возвращающими табличные значения параметрами, находящимися в теле процедуры, нельзя выполнять операции DML, такие как UPDATE, DELETE или INSERT.
Возвращающий табличное значение параметр не может быть использован в качестве цели для инструкции SELECT INTO или INSERT EXEC. Возвращающий табличное значение параметр может присутствовать в предложении FROM инструкции SELECT INTO, в строке или хранимой процедуре INSERT EXEC.

Значок стрелки, используемый со ссылкой «В начало» [Top]

Возвращающие табличное значение параметры иоперации BULK INSERT

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

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

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

Значок стрелки, используемый со ссылкой «В начало» [Top]

Пример

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

USE AdventureWorks2012;
GO

/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE 
( LocationName VARCHAR(50)
, CostRate INT );
GO

/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE dbo. usp_InsertProductionLocation
    @TVP LocationTableType READONLY
    AS 
    SET NOCOUNT ON
    INSERT INTO AdventureWorks2012.Production.Location
           (Name
           ,CostRate
           ,Availability
           ,ModifiedDate)
        SELECT *, 0, GETDATE()
        FROM  @TVP;
        GO

/* Declare a variable that references the type. */
DECLARE @LocationTVP AS LocationTableType;

/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
    SELECT Name, 0.00
    FROM AdventureWorks2012.Person.StateProvince;

/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO

Значок стрелки, используемый со ссылкой «В начало» [Top]