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

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server даБаза данных SQL Azure нетAzure Synapse Analytics (хранилище данных SQL) нетParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Возвращающие табличные значения параметры объявляются с помощью определяемых пользователем табличных типов.Table-valued parameters are declared by using user-defined table types. Возвращающие табличные значения параметры можно использовать, чтобы отправить несколько строк данных в инструкцию Transact-SQLTransact-SQL или в процедуру, например хранимую процедуру или функцию, не создавая при этом временной таблицы или большого количества параметров.You can use table-valued parameters to send multiple rows of data to a Transact-SQLTransact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

Возвращающие табличные значения параметры похожи на массивы параметров в OLE DB и ODBC, но они обеспечивают большую гибкость и больше интегрированы с Transact-SQLTransact-SQL.Table-valued parameters are like parameter arrays in OLE DB and ODBC, but offer more flexibility and closer integration with Transact-SQLTransact-SQL. Преимуществом возвращающих табличные значения параметров также является возможность участия в операциях, основанных на наборах.Table-valued parameters also have the benefit of being able to participate in set-based operations.

Transact-SQLTransact-SQL передает возвращающие табличные значения параметры подпрограммам по ссылке, чтобы избежать создания копий входных данных.passes table-valued parameters to routines by reference to avoid making a copy of the input data. Можно создавать и выполнять процедуры Transact-SQLTransact-SQL с возвращающими табличные значения параметрами и вызывать их из кода Transact-SQLTransact-SQL , управляемых и собственных клиентов на любом управляемом языке.You can create and execute Transact-SQLTransact-SQL routines with table-valued parameters, and call them from Transact-SQLTransact-SQL code, managed and native clients in any managed language.

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

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

ОграниченияRestrictions

Возвращающие табличные значения параметры и операции BULK INSERTTable-Valued Parameters vs. BULK INSERT Operations

ПримерExample

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

Область действия возвращающего табличное значение параметра такая же, как и у других параметров, — хранимая процедура, функция или динамический текст Transact-SQLTransact-SQL .A table-valued parameter is scoped to the stored procedure, function, or dynamic Transact-SQLTransact-SQL text, exactly like other parameters. Аналогично область действия у переменной типа table точно такая же, как и у любой другой переменной, созданной с помощью инструкции DECLARE.Similarly, a variable of table type has scope like any other local variable that is created by using a DECLARE statement. Возвращающие табличные значения переменные можно объявлять в динамических инструкциях Transact-SQLTransact-SQL , а затем передавать эти переменные как возвращающие табличные значения параметры хранимым процедурам и функциям.You can declare table-valued variables within dynamic Transact-SQLTransact-SQL statements and pass these variables as table-valued parameters to stored procedures and functions.

Возвращающие табличные значения параметры обеспечивают большую гибкость и в некоторых случаях более высокую производительность, чем временные таблицы или другие методы передачи списка параметров.Table-valued parameters offer more flexibility and in some cases better performance than temporary tables or other ways to pass a list of parameters. Возвращающие табличные значения параметры имеют следующие преимущества.Table-valued parameters offer the following benefits:

  • Не запрашивают блокировки для первичного заполнения данными от клиента.Do not acquire locks for the initial population of data from a client.
  • Предоставляют простую модель программирования.Provide a simple programming model.
  • Позволяют включать в одиночную процедуру сложную бизнес-логику.Enable you to include complex business logic in a single routine.
  • Сокращают количество циклов приема-передачи с сервером.Reduce round trips to the server.
  • Могут иметь структуру таблицы с другим количеством элементов.Can have a table structure of different cardinality.
  • Строго типизированы.Are strongly typed.
  • Позволяют клиенту указать порядок сортировки и уникальные ключи.Enable the client to specify sort order and unique keys.
  • Кэшируются как временная таблица при использовании в хранимой процедуре.Are cached like a temp table when used in a stored procedure. Начиная с SQL Server 2012 (11.x)SQL Server 2012 (11.x), возвращающие табличное значение параметры также кэшируется для параметризированных запросов.Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x), table-valued parameters are also cached for parameterized queries.

PermissionsPermissions

Чтобы создать экземпляр определяемого пользователем типа таблицы или вызвать хранимую процедуру с параметром, возвращающим значение таблицы, пользователь должен иметь разрешение EXECUTE на тип, схему или базу данных, содержащую этот тип.To create an instance of a User-Defined Table Type or call a stored procedure with a Table-valued parameter the user must have EXECUTE permission on the type, or on the schema or database containing the type.

ОграниченияRestrictions

Возвращающие табличные значения параметры имеют следующие ограничения.Table-valued parameters have the following restrictions:

  • SQL ServerSQL Server не ведет статистику столбцов возвращающих табличные значения параметров.does not maintain statistics on columns of table-valued parameters.
  • Возвращающие табличные значения параметры должны передаваться процедурам Transact-SQLTransact-SQL как входные параметры типа READONLY.Table-valued parameters must be passed as input READONLY parameters to Transact-SQLTransact-SQL routines. Над возвращающими табличные значения параметрами, находящимися в теле процедуры, нельзя выполнять операции DML, такие как UPDATE, DELETE или INSERT.You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.
  • Возвращающий табличное значение параметр не может быть использован в качестве цели для инструкции SELECT INTO или INSERT EXEC.You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. Возвращающий табличное значение параметр может присутствовать в предложении FROM инструкции SELECT INTO, в строке или хранимой процедуре INSERT EXEC.A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored procedure.

Возвращающие табличные значения параметры и Операции BULK INSERTTable-Valued Parameters vs. BULK INSERT Operations

Использование возвращающих табличные значения параметров похоже на другие способы использования переменных, основанных на наборах. Однако применение возвращающих табличные значения параметров при работе с большими наборами данных часто позволяет добиться увеличения производительности.Using table-valued parameters is comparable to other ways of using set-based variables; however, using table-valued parameters frequently can be faster for large data sets. По сравнению с массовыми операциями, имеющими большие начальные затраты, возвращающие табличные значения параметры показывают хорошую производительность при вставке менее 1000 строк.Compared to bulk operations that have a greater startup cost than table-valued parameters, table-valued parameters perform well for inserting less than 1000 rows.

Возвращающие табличные значения параметры, используемые повторно, могут использовать кэширование временных таблиц.Table-valued parameters that are reused benefit from temporary table caching. Это кэширование таблиц позволяет обеспечить лучшую масштабируемость, чем в эквивалентных операциях BULK INSERT.This table caching enables better scalability than equivalent BULK INSERT operations. С помощью маленьких операций вставки строк можно добиться небольшого увеличения производительности, применяя списки параметров или пакетные инструкции вместо операций BULK INSERT или возвращающих табличные значения параметров.By using small row-insert operations a small performance benefit might be gained by using parameter lists or batched statements instead of BULK INSERT operations or table-valued parameters. Однако эти методы сложнее программировать, а производительность быстро падает при увеличении количества строк.However, these methods are less convenient to program, and performance decreases quickly as rows increase.

Возвращающие табличные значения параметры работают также хорошо или даже лучше, чем эквивалентная реализация массива параметров.Table-valued parameters perform equally well or better than an equivalent parameter array implementation.

ПримерExample

В следующем примере с использованием языка Transact-SQLTransact-SQL показывается, как создать тип параметра с табличным значением, объявить ссылающуюся на него переменную, заполнить список параметров, а затем передать значения хранимой процедуре в базе данных AdventureWorks.The following example uses Transact-SQLTransact-SQL and shows you how to create a table-valued parameter type, declare a variable to reference it, fill the parameter list, and then pass the values to a stored procedure in the AdventureWorks database.

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

См. также:See Also