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

Применимо к: SQL Server Azure SQL Database Azure Synapse Analytics Analytics Platform System (PDW)

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

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

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

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

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

Ограничения

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

Пример

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

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

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

  • Не запрашивают блокировки для первичного заполнения данными от клиента.
  • Предоставляют простую модель программирования.
  • Позволяют включать в одиночную процедуру сложную бизнес-логику.
  • Сокращают количество циклов приема-передачи с сервером.
  • Могут иметь структуру таблицы с другим количеством элементов.
  • Строго типизированы.
  • Позволяют клиенту указать порядок сортировки и уникальные ключи.
  • Кэшируются как временная таблица при использовании в хранимой процедуре. Начиная с SQL Server 2012 (11.x), возвращаемые табличным значением параметры также кэшируются для параметризованных запросов.

Разрешения

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

Ограничения

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

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

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

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

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

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

Пример

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

/* 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;

См. также