Sdílet prostřednictvím

Použití parametrů s tabulkovou hodnotou (Databázový stroj)

  • Článek

platí pro:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL database v Microsoft Fabric

Parametry s hodnotou tabulky se deklarují pomocí uživatelem definovaných typů tabulek. Parametry hodnot tabulky můžete použít k odeslání více řádků dat do příkazu Transact-SQL nebo rutiny, jako je uložená procedura nebo funkce, bez vytvoření dočasné tabulky nebo mnoha parametrů.

Parametry hodnotné tabulkou jsou jako pole parametrů v OLE DB a ODBC, ale nabízejí větší flexibilitu a užší integraci s Jazykem Transact-SQL. Parametry s hodnotou tabulky mají také výhodu, že se mohou účastnit operací sady.

Transact-SQL předává parametry typu tabulky rutinám pomocí odkazu, aby se zabránilo kopírování vstupních dat. Můžete vytvářet a spouštět rutiny Transact-SQL s parametry s hodnotami tabulky a volat je z Transact-SQL kódu, spravovaných a nativních klientů v libovolném spravovaném jazyce.

Výhody

Parametr s hodnotou tabulky je vymezený na uloženou proceduru, funkci nebo dynamický Transact-SQL text, stejně jako ostatní parametry. Podobně má proměnná typu tabulky obor jako jakákoli jiná místní proměnná vytvořená pomocí příkazu DECLARE. Proměnné hodnot tabulky můžete deklarovat v rámci dynamických příkazů Transact-SQL a předávat tyto proměnné jako parametry hodnot tabulky uloženým procedurám a funkcím.

Parametry s hodnotou tabulky nabízejí větší flexibilitu a v některých případech lepší výkon než dočasné tabulky nebo jiné způsoby předání seznamu parametrů. Parametry s hodnotou tabulky nabízejí následující výhody:

  • Nezískajte zámky pro počáteční populaci dat z klienta.
  • Zadejte jednoduchý programovací model.
  • Umožňuje zahrnout komplexní obchodní logiku do jedné rutiny.
  • Snižte počet komunikací se serverem.
  • Může mít strukturu tabulky s různou kardinalitou.
  • Jsou silně typované.
  • Povolte klientovi zadat pořadí řazení a jedinečné klíče.
  • Jsou ukládány do mezipaměti jako dočasná tabulka při použití v uložené proceduře. Počínaje SQL Serverem 2012 (11.x) a novějšími verzemi jsou parametry hodnot tabulky také uloženy v mezipaměti pro parametrizované dotazy.

Dovolení

Chcete-li vytvořit instanci uživatelem definovaného typu tabulkynebo volat uloženou proceduru s parametrem s hodnotou tabulky, musí mít uživatel oprávnění EXECUTE a REFERENCES pro typ nebo ve schématu nebo databázi obsahující tento typ.

Omezení

Parametry s hodnotou tabulky mají následující omezení:

  • SQL Server neudržuje statistiky sloupců parametrů s hodnotami tabulky.
  • Parametry hodnot tabulky musí být předány jako vstupní parametry READONLY Transact-SQL rutin. U parametru s hodnotou tabulky v těle rutiny nelze provádět operace DML, jako je UPDATE, DELETE nebo INSERT.
  • Parametr s hodnotou tabulky nelze použít jako cíl příkazu SELECT INTO nebo INSERT EXEC. Parametr s hodnotou tabulky může být v klauzuli FROM nebo v řetězci INSERT EXEC nebo uložené proceduře SELECT INTO.

Parametry hodnot tabulky vs. operace BULK INSERT

Použití parametrů s hodnotou tabulky je srovnatelné s jinými způsoby použití proměnných založených na sadě; Použití parametrů s hodnotou tabulky však může být pro velké datové sady rychlejší. V porovnání s hromadnými operacemi, které mají vyšší počáteční náklady než parametry s hodnotami tabulky, fungují parametry s hodnotami tabulky dobře pro vložení méně než 1 000 řádků.

Parametry s hodnotou tabulky, které se opakovaně používají, využívají dočasné ukládání tabulek do mezipaměti. Ukládání této tabulky do mezipaměti umožňuje lepší škálovatelnost než ekvivalentní operace hromadného vložení. Malé operace vložení řádků mohou přinést malou výhodu výkonu při použití seznamů parametrů nebo dávkových příkazů místo operací BULK INSERT nebo parametrů typu tabulky. Tyto metody jsou ale pro program méně vhodné a výkon se rychle snižuje při zvyšování počtu řádků.

Parametry s hodnotou tabulky fungují stejně dobře nebo lépe než ekvivalentní implementace pole parametrů.

Příklady

Následující příklad používá Transact-SQL a ukazuje, jak vytvořit typ parametru s hodnotou tabulky, deklarovat proměnnou, která na ni odkazuje, vyplnit seznam parametrů a předat hodnoty uložené procedurě v ukázkové AdventureWorks databázi.

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

Očekávaná sada výsledků je:

(181 rows affected)

Související obsah