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 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, w ciągu INSERT EXEC lub w 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 większą skalowalność niż równoważne operacje wstawiania zbiorczego. Małe operacje wstawiania wierszy mogą zapewnić niewielką poprawę 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ść