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

  • Статья

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

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

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

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

Льготы

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

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

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

Разрешения

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

Ограничения

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

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

Параметры с табличным значением и операции BULK INSERT

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

Возвращающие табличные значения параметры, используемые повторно, могут использовать кэширование временных таблиц. Кэширование таблицы обеспечивает лучшую масштабируемость, чем эквивалентные операции массового вставки. Небольшие операции вставки строк могут обеспечить небольшое преимущество производительности с помощью списков параметров или пакетных инструкций, а не 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;

Ожидаемый набор результатов:

(181 rows affected)

Связанный контент