Dela via

Använda tabellvärdesparametrar (databasmotor)

  • Artikel

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL-databas i Microsoft Fabric

Tabellvärdesparametrar deklareras med hjälp av användardefinierade tabelltyper. Du kan använda tabellvärdesparametrar för att skicka flera rader med data till en Transact-SQL-instruktion eller en rutin, till exempel en lagrad procedur eller funktion, utan att skapa en tillfällig tabell eller många parametrar.

Tabellvärdesparametrar är som parametermatriser i OLE DB och ODBC, men ger mer flexibilitet och närmare integrering med Transact-SQL. Tabellvärdesparametrar har också fördelen att kunna delta i uppsättningsbaserade åtgärder.

Transact-SQL skickar tabellvärdesparametrar till rutiner med referens för att undvika att göra en kopia av indata. Du kan skapa och köra Transact-SQL rutiner med tabellvärdesparametrar och anropa dem från Transact-SQL kod, hanterade och interna klienter på alla hanterade språk.

Fördelar

En tabellvärdesparameter är begränsad till den lagrade proceduren, funktionen eller den dynamiska Transact-SQL text, precis som andra parametrar. På samma sätt har en variabel av tabelltyp omfång som alla andra lokala variabler som skapas med hjälp av en DECLARE-instruktion. Du kan deklarera tabellvärdesvariabler i dynamiska Transact-SQL-instruktioner och skicka dessa variabler som tabellvärdeparametrar till lagrade procedurer och funktioner.

Tabellvärdesparametrar ger större flexibilitet och i vissa fall bättre prestanda än tillfälliga tabeller eller andra sätt att skicka en lista med parametrar. Tabellvärdesparametrar erbjuder följande fördelar:

  • Hämta inte lås för den ursprungliga populationen av data från en klient.
  • Tillhandahålla en enkel programmeringsmodell.
  • Gör att du kan inkludera komplex affärslogik i en enda rutin.
  • Minska tur och retur till servern.
  • Kan ha en tabellstruktur med olika kardinalitet.
  • Är starkt typade.
  • Aktivera klienten för att ange sorteringsordning och unika nycklar.
  • Lagrads som en temporär tabell när de används i en lagrad procedur. Från och med SQL Server 2012 (11.x) och senare versioner cachelagras även tabellvärdesparametrar för parametriserade frågor.

Behörigheter

Om du vill skapa en instans av en användardefinierad tabelltyp, eller anropa en lagrad procedur med en tabellvärdesparameter, måste användaren ha behörigheten EXECUTE och REFERENCES för typen eller på schemat eller databasen som innehåller typen.

Begränsningar

Tabellvärdeparametrar har följande begränsningar:

  • SQL Server upprätthåller inte statistik för kolumner med tabellvärdesparametrar.
  • Tabellvärdesparametrar måste skickas som READONLY-indataparametrar till Transact-SQL rutiner. Du kan inte utföra DML-åtgärder som UPDATE, DELETE eller INSERT på en tabellvärdeparameter i brödtexten i en rutin.
  • Du kan inte använda en tabellvärdeparameter som mål för en SELECT INTO- eller INSERT EXEC-instruktion. En tabellvärdeparameter kan finnas i FROM-satsen i SELECT INTO eller i INSERT EXEC sträng eller lagrad procedur.

Tabellvärdesparametrar jämfört med BULK INSERT-åtgärder

Att använda tabellvärdesparametrar är jämförbart med andra sätt att använda uppsättningsbaserade variabler. Men att använda tabellvärdesparametrar ofta kan vara snabbare för stora datamängder. Jämfört med massåtgärder som har en större startkostnad än tabellvärdesparametrar presterar tabellvärdesparametrar bra för att infoga mindre än 1 000 rader.

Tabellvärdesparametrar som återanvänds drar nytta av tillfällig cachelagring av tabeller. Den här tabellcachelagringen ger bättre skalbarhet än motsvarande massinfogningsåtgärder. Små radinfogningsåtgärder kan ge en liten prestandafördel med hjälp av parameterlistor eller batchinstruktioner, i stället för BULK INSERT åtgärder eller tabellvärdesparametrar. Dessa metoder är dock mindre praktiska att programmera, och prestandan minskar snabbt när raderna ökar.

Tabellvärdesparametrar fungerar lika bra eller bättre än en motsvarande parametermatrisimplementering.

Exempel

I följande exempel används Transact-SQL och visar hur du skapar en tabellvärdeparametertyp, deklarerar en variabel för att referera till den, fyller i parameterlistan och skickar sedan värdena till en lagrad procedur i exemplet AdventureWorks databas.

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

Den förväntade resultatmängden är:

(181 rows affected)

Relaterat innehåll