Udostępnij za pośrednictwem

Używanie parametrów typu tabelarycznego (silnik bazy danych)

  • Artykuł

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL Database w usłudze Microsoft Fabric

Parametry wartości tabeli są deklarowane przy użyciu typów tabel zdefiniowanych przez użytkownika. Można użyć parametrów wartości tabeli do wysyłania wielu wierszy danych do instrukcji Transact-SQL lub procedury, takiej jak procedura składowana lub funkcja, bez tworzenia tabeli tymczasowej lub wielu parametrów.

Parametry wartości tabeli są podobne do tablic parametrów w bazach OLE DB i ODBC, ale oferują większą elastyczność i ściślejszą integrację z językiem Transact-SQL. Parametry wyceniane w tabeli mają również korzyść z możliwości uczestnictwa w operacjach opartych na zestawie.

Transact-SQL przekazuje parametry tabelaryczne do procedur za pomocą odwołania, aby uniknąć tworzenia kopii danych wejściowych. Można tworzyć i wykonywać procedury Transact-SQL z parametrami tabelowymi i wywoływać je z kodu Transact-SQL, z klientów zarządzanych i natywnych w dowolnym języku zarządzanym.

Korzyści

Parametr tabelaryczny jest ograniczony do procedury składowanej, funkcji lub dynamicznego tekstu Transact-SQL, tak samo jak inne parametry. Podobnie zmienna typu tabeli ma zakres jak każda inna zmienna lokalna tworzona przy użyciu instrukcji DECLARE. Zmienne wartości tabeli można zadeklarować w instrukcjach dynamicznych Transact-SQL i przekazać te zmienne jako parametry wartości tabeli do procedur składowanych i funkcji.

Parametry wartości tabeli zapewniają większą elastyczność i w niektórych przypadkach lepszą wydajność niż tabele tymczasowe lub inne sposoby przekazywania listy parametrów. Parametry wartości tabeli oferują następujące korzyści:

  • Nie pobieraj blokad dla początkowej populacji danych od klienta.
  • Podaj prosty model programowania.
  • Umożliwia uwzględnienie złożonej logiki biznesowej w jednej procedurze.
  • Zmniejsz liczbę odwołań do serwera.
  • Może mieć strukturę tabeli o różnej kardynalności.
  • Są silnie typizowane.
  • Umożliwia klientowi określenie kolejności sortowania i unikatowych kluczy.
  • Są buforowane jak tabela tymczasowa, gdy jest używana w procedurze składowanej. Począwszy od programu SQL Server 2012 (11.x) i nowszych wersji, parametry z wartością tabeli są również buforowane dla sparametryzowanych zapytań.

Uprawnienia

Aby utworzyć wystąpienie typu tabeli zdefiniowanej przez użytkownikalub wywołać procedurę składowaną z parametrem wartości tabeli, użytkownik musi mieć uprawnienia EXECUTE i REFERENCES dla typu lub schematu lub bazy danych zawierającej typ.

Ograniczenia

Parametry wartości tabeli mają następujące ograniczenia:

  • Program SQL Server nie przechowuje statystyk dotyczących kolumn parametrów wartości tabeli.
  • Parametry wartości tabeli muszą być przekazywane jako parametry wejściowe READONLY (tylko do odczytu) do procedur Transact-SQL. Nie można wykonywać operacji DML, takich jak UPDATE, DELETE lub INSERT w parametrze wartości tabeli w treści procedury.
  • Nie można użyć parametru o wartości tabeli jako celu instrukcji SELECT INTO lub INSERT EXEC. Parametr o wartości tabeli może znajdować się w klauzuli FROMSELECT INTO lub w ciągu INSERT EXEC lub procedurze składowanej.

Parametry wartości tabeli a operacje BULK INSERT

Używanie parametrów wartości tabeli jest porównywalne z innymi sposobami używania zmiennych opartych na zestawie; jednak używanie parametrów wartości tabeli często może być szybsze w przypadku dużych zestawów danych. W porównaniu do operacji zbiorczych, które mają większy koszt uruchamiania niż parametry z wartością tabeli, parametry z wartością tabeli działają dobrze w przypadku wstawiania mniej niż 1000 wierszy.

Parametry wartości tabeli, które są ponownie używane, korzystają z tymczasowego buforowania tabeli. Buforowanie tej tabeli umożliwia lepszą skalowalność niż równoważne operacje wstawiania zbiorczego. Małe operacje wstawiania wierszy mogą zapewnić niewielką korzyść w zakresie wydajności przy użyciu list parametrów lub instrukcji wsadowych, zamiast operacji BULK INSERT lub parametrów tabelarycznych. Jednak te metody są mniej wygodne do programowania, a wydajność spada szybko w miarę wzrostu wierszy.

Parametry wartości tabeli działają równie dobrze lub lepiej niż równoważna implementacja tablicy parametrów.

Przykłady

W poniższym przykładzie użyto Transact-SQL i pokazano, jak utworzyć typ parametru wartości tabeli, zadeklarować zmienną w celu odwołania się do niej, wypełnić listę parametrów, a następnie przekazać wartości do procedury składowanej w przykładowej bazie danych AdventureWorks.

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

Oczekiwany zestaw wyników to:

(181 rows affected)

Powiązana zawartość