Delen via

Parameters met tabelwaarden gebruiken (database-engine)

  • Artikel

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL-database in Microsoft Fabric

Parameters met tabelwaarden worden gedeclareerd met behulp van door de gebruiker gedefinieerde tabeltypen. U kunt parameters met tabelwaarden gebruiken om meerdere rijen met gegevens te verzenden naar een Transact-SQL-instructie of een routine, zoals een opgeslagen procedure of functie, zonder een tijdelijke tabel of veel parameters te maken.

Parameters met tabelwaarden zijn als parametermatrices in OLE DB en ODBC, maar bieden meer flexibiliteit en een betere integratie met Transact-SQL. Parameters met tabelwaarden hebben ook het voordeel dat ze kunnen deelnemen aan op set gebaseerde bewerkingen.

Transact-SQL geeft parameters met tabelwaarden door aan routines door ze met een referentie te verzenden, zodat er geen kopie van de invoergegevens wordt gemaakt. U kunt Transact-SQL routines maken en uitvoeren met parameters met tabelwaarden en deze aanroepen vanuit Transact-SQL code, beheerde en systeemeigen clients in elke beheerde taal.

Voordelen

Een tabelwaardeparameter is gericht op de opgeslagen procedure, functie of dynamische Transact-SQL tekst, precies zoals andere parameters. Op dezelfde manier heeft een variabele van het tabeltype bereik als elke andere lokale variabele die wordt gemaakt met behulp van een DECLARE-instructie. U kunt variabelen met tabelwaarden declareren binnen dynamische Transact-SQL-instructies en deze variabelen doorgeven als parameters met tabelwaarden aan opgeslagen procedures en functies.

Parameters met tabelwaarde bieden meer flexibiliteit en in sommige gevallen betere prestaties dan tijdelijke tabellen of andere manieren om een lijst met parameters door te geven. Parameters met tabelwaarden bieden de volgende voordelen:

  • Verwerf geen vergrendelingen voor de initiële populatie van gegevens van een client.
  • Een eenvoudig programmeermodel bieden.
  • Hiermee kunt u complexe bedrijfslogica opnemen in één routine.
  • Verminder rondreizen naar de server.
  • Kan een tabelstructuur van verschillende kardinaliteit hebben.
  • Zijn sterk getypt.
  • Schakel de client in om de sorteervolgorde en unieke sleutels op te geven.
  • Worden in de cache opgeslagen als een tijdelijke tabel wanneer deze wordt gebruikt in een opgeslagen procedure. Vanaf SQL Server 2012 (11.x) en latere versies worden parameters met tabelwaarden ook in de cache opgeslagen voor geparameteriseerde query's.

Machtigingen

Als u een exemplaar van een door de gebruiker gedefinieerde tabeltypewilt maken of een opgeslagen procedure wilt aanroepen met een parameter met tabelwaarde, moet de gebruiker de machtiging EXECUTE en REFERENCES hebben voor het type, of voor het schema of de database met het type.

Beperkingen

Parameters met tabelwaarden hebben de volgende beperkingen:

  • SQL Server onderhoudt geen statistieken voor kolommen met parameters met tabelwaarden.
  • Parameters met tabelwaarde moeten worden doorgegeven als invoer READONLY-parameters voor Transact-SQL routines. U kunt DML-bewerkingen zoals UPDATE, DELETE of INSERT niet uitvoeren op een tabelwaardeparameter in de hoofdtekst van een routine.
  • U kunt geen tabelwaardeparameter gebruiken als doel van een SELECT INTO- of INSERT EXEC-instructie. Een tabelwaardeparameter kan zich in de FROM component van SELECT INTO of in de INSERT EXEC tekenreeks of opgeslagen procedure bevinden.

Parameters met tabelwaarde versus BULK INSERT-bewerkingen

Het gebruik van parameters met tabelwaarden is vergelijkbaar met andere manieren om op set gebaseerde variabelen te gebruiken; Het gebruik van parameters met tabelwaarden kan echter vaak sneller zijn voor grote gegevenssets. Vergeleken met bulkbewerkingen met een hogere opstartkosten dan parameters met tabelwaarden, presteren parameters met tabelwaarden goed voor het invoegen van minder dan 1000 rijen.

Parameters met tabelwaarden die opnieuw worden gebruikt, profiteren van tijdelijke tabelcaching. Deze tabelcaching maakt betere schaalbaarheid mogelijk dan gelijkwaardige bulkinvoegbewerkingen. Kleine bewerkingen voor het invoegen van rijen kunnen een klein prestatievoordeel opleveren door parameterlijsten of batchinstructies te gebruiken in plaats van BULK INSERT bewerkingen of parameters met tabelwaarden. Deze methoden zijn echter minder handig om te programmeren en de prestaties nemen snel af naarmate rijen toenemen.

Tabelwaardeparameters presteren even goed of beter dan een equivalente parametermatrix-implementatie.

Voorbeelden

In het volgende voorbeeld wordt gebruikgemaakt van Transact-SQL en ziet u hoe u een parametertype met tabelwaarden maakt, een variabele declareert om ernaar te verwijzen, de lijst met parameters vult en vervolgens de waarden doorgeeft aan een opgeslagen procedure in de voorbeelddatabase 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 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;

De verwachte resultatenset is:

(181 rows affected)

Verwante inhoud