Поделиться через

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

  • Статья

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

Параметры табличного типа объявляются с помощью пользовательских табличных типов. Параметры с табличным значением можно использовать для отправки нескольких строк данных в инструкцию 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, над табличными параметрами в теле процедуры.
  • Нельзя использовать табличный параметр в качестве цели инструкции SELECT INTO или INSERT EXEC. Параметр с табличным значением может находиться в 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)

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