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

Использование параметров Table-Valued (ядро СУБД)

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

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

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

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

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

Ограничения

Table-Valued параметры и операции BULK INSERT

Пример

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

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

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

  • Не запрашивайте блокировки для первичного заполнения данными от клиента.

  • Предоставляют простую модель программирования.

  • Позволяют включать в одиночную процедуру сложную бизнес-логику.

  • Уменьшите количество обращений к серверу.

  • Могут иметь структуру таблицы с другим количеством элементов.

  • Строго типизированы.

  • Позволяют клиенту указать порядок сортировки и уникальные ключи.

  • Кэшируются как временная таблица при использовании в хранимой процедуре. Начиная с SQL Server 2012, табличное значение параметров также кэшируются для параметризованных запросов.

Ограничения

Параметры табличных значений имеют следующие ограничения.

  • SQL Server не поддерживает статистику по столбцам табличных параметров.

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

  • Нельзя использовать табличное значение параметра в качестве целевого объекта инструкции SELECT INTO или INSERT EXEC. Параметр с табличным значением может находиться в предложении FROM SELECT INTO или в строке INSERT EXEC или хранимой процедуре.

Table-Valued Параметры vs. операции BULK INSERT

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

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

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

Пример

В следующем примере используется 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

См. также

CREATE TYPE (Transact-SQL)
DECLARE @local_variable (Transact-SQL)
sys.types (Transact-SQL)
sys.parameters (Transact-SQL)
sys.parameter_type_usages (Transact-SQL)
СОЗДАТЬ ПРОЦЕДУРУ (Transact-SQL)
CREATE FUNCTION (Transact-SQL)

  • Last updated on