Использование параметров, возвращающих табличные значения (ядро СУБД)

  • Статья

Область применения: SQL Server База данных SQL Azure Not supported. Azure Synapse Analytics Not supported. 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 и REFERENCES для типа или схемы или базы данных, содержащей тип.

Ограничения

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

  • 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 AdventureWorks2022.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 AdventureWorks2022.Person.StateProvince;
  
/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;

См. также