table (Transact-SQL)

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

tabeli jest specjalnym typem danych używanym do przechowywania zestawu wyników do przetwarzania w późniejszym czasie. tabeli jest używana głównie do tymczasowego przechowywania zestawu wierszy zwracanych jako zestaw wyników funkcji wartości tabeli. Funkcje i zmienne można zadeklarować jako typ tabeli. zmienne tabeli mogą być używane w funkcjach, procedurach składowanych i partiach. Aby zadeklarować zmienne typu tabeli, użyj DECLARE @local_variable.

Transact-SQL konwencje składni

Składnia

table_type_definition ::=
    TABLE ( { <column_definition> | <table_constraint> } [ , ...n ] )

<column_definition> ::=
    column_name scalar_data_type
    [ COLLATE <collation_definition> ]
    [ [ DEFAULT constant_expression ] | IDENTITY [ ( seed , increment ) ] ]
    [ ROWGUIDCOL ]
    [ column_constraint ] [ ...n ]

 <column_constraint> ::=
    { [ NULL | NOT NULL ]
    | [ PRIMARY KEY | UNIQUE ]
    | CHECK ( logical_expression )
    }

<table_constraint> ::=
     { { PRIMARY KEY | UNIQUE } ( column_name [ , ...n ] )
     | CHECK ( logical_expression )
     }

Argumenty

table_type_definition

Ten sam podzbiór informacji używany do definiowania tabeli w tabeli CREATE TABLE. Deklaracja tabeli zawiera definicje kolumn, nazwy, typy danych i ograniczenia. Jedynymi dozwolonymi typami ograniczeń są KLUCZ PODSTAWOWY, UNIKATOWY KLUCZ i WARTOŚĆ NULL.

Aby uzyskać więcej informacji na temat składni, zobacz CREATE TABLE (Transact-SQL), CREATE FUNCTION (Transact-SQL)i DECLARE @local_variable (Transact-SQL).

collation_definition

Sortowanie kolumny, która składa się z ustawień regionalnych systemu Microsoft Windows i stylu porównania, ustawień regionalnych systemu Windows i notacji binarnej lub sortowania programu Microsoft SQL Server. Jeśli nie określono collation_definition, kolumna dziedziczy sortowanie bieżącej bazy danych. Jeśli kolumna jest zdefiniowana jako typ środowiska uruchomieniowego języka wspólnego (CLR), kolumna dziedziczy sortowanie typu zdefiniowanego przez użytkownika.

Uwagi

tabeli Zmienne referencyjne według nazwy w klauzuli FROM partii, jak pokazano w poniższym przykładzie:

SELECT Employee_ID, Department_ID FROM @MyTableVar;

Poza klauzulą FROM należy odwoływać się do tabeli przy użyciu aliasu, jak pokazano w poniższym przykładzie:

SELECT EmployeeID,
    DepartmentID
FROM @MyTableVar m
INNER JOIN Employee
    ON m.EmployeeID = Employee.EmployeeID
    AND m.DepartmentID = Employee.DepartmentID;

tabeli zmiennych zapewniają następujące korzyści w przypadku tabel tymczasowych dla zapytań o małą skalę, które nie zmieniają się, a gdy problemy dotyczące ponownej kompilacji są dominujące:

• Zmienna tabeli zachowuje się jak zmienna lokalna. Ma dobrze zdefiniowany zakres. Ta zmienna może być używana w funkcji, procedurze składowanej lub partii, w której jest zadeklarowana.

  W jej zakresie zmienna tabeli może być używana jak zwykła tabela. Można go zastosować w dowolnym miejscu, w których jest używana tabela lub wyrażenie tabeli w instrukcjach SELECT, INSERT, UPDATE i DELETE. Nie można jednak użyć tabeli w następującej instrukcji:

SELECT select_list INTO table_variable;

tabeli zmienne są automatycznie czyszczone na końcu funkcji, procedury składowanej lub partii, w której są zdefiniowane.

• tabeli zmiennych, które są używane w procedurach składowanych, powodują mniejszą liczbę ponownych kompilacji procedury składowanej niż wtedy, gdy tabele tymczasowe są używane, gdy nie ma opcji opartych na kosztach, które mają wpływ na wydajność.

  Zmienne tabeli są całkowicie odizolowane od partii, która je tworzy, więc nie ponownego rozpoznawania musi wystąpić, gdy odbywa się instrukcja CREATE lub ALTER, która może wystąpić z tabelą tymczasową. Tabele tymczasowe wymagają tej ponownej rozdzielczości, aby można było odwoływać się do tabeli z zagnieżdżonej procedury składowanej. Zmienne tabeli całkowicie unikają tego kroku, więc procedury składowane mogą używać planu, który jest już kompilowany, co pozwala zaoszczędzić zasoby na potrzeby przetwarzania procedury składowanej.

• Transakcje obejmujące tabelę trwać tylko przez czas trwania aktualizacji zmiennej tabeli. W związku z tym tabeli zmienne wymagają mniejszej liczby zasobów blokowania i rejestrowania.

Ograniczenia i ograniczenia

tabeli zmiennych nie mają statystyk dystrybucji. Nie wyzwalają ponownej kompilacji. W wielu przypadkach optymalizator tworzy plan zapytania przy założeniu, że zmienna tabeli nie ma wierszy. Z tego powodu należy zachować ostrożność przy użyciu zmiennej tabeli, jeśli spodziewasz się większej liczby wierszy (więcej niż 100). W tym przypadku tabele tymczasowe mogą być lepszym rozwiązaniem. W przypadku zapytań, które łączą zmienną tabeli z innymi tabelami, użyj wskazówki RECOMPILE, co powoduje, że optymalizator używa poprawnej kardynalności dla zmiennej tabeli.

tabeli zmiennych nie są obsługiwane w modelu rozumowania opartym na kosztach optymalizatora programu SQL Server. W związku z tym nie należy ich używać, gdy opcje oparte na kosztach są wymagane do osiągnięcia wydajnego planu zapytań. Tabele tymczasowe są preferowane, gdy są wymagane opcje oparte na kosztach. Ten plan zwykle obejmuje zapytania ze sprzężeniami, decyzjami równoległości i wyborami wyboru indeksu.

Zapytania modyfikujące tabelę zmiennych nie generują równoległych planów wykonywania zapytań. Wydajność może mieć wpływ na duże zmienne tabeli lub zmienne tabeli w złożonych zapytaniach są modyfikowane. Rozważ użycie tabel tymczasowych zamiast tego w sytuacjach, w których tabeli zmienne są modyfikowane. Aby uzyskać więcej informacji, zobacz CREATE TABLE (Transact-SQL). Zapytania odczytujące tabelę zmiennych bez ich modyfikowania nadal mogą być zrównane.

Ważny

Poziom zgodności bazy danych 150 zwiększa wydajność zmiennych tabeli z wprowadzeniem zmiennej tabelikompilacji odroczonej. Aby uzyskać więcej informacji, zobacz kompilacji zmiennej tabeli odroczonej.

Nie można jawnie tworzyć indeksów w zmiennych tabeli , a żadne statystyki nie są przechowywane w zmiennych tabeli . Począwszy od programu SQL Server 2014 (12.x), wprowadzono nową składnię, która umożliwia tworzenie określonych typów indeksów wbudowanych przy użyciu definicji tabeli. Korzystając z tej nowej składni, można utworzyć indeksy w tabeli zmiennych w ramach definicji tabeli. W niektórych przypadkach wydajność może poprawić się przy użyciu tabel tymczasowych, które zapewniają pełną obsługę indeksów i statystyki. Aby uzyskać więcej informacji na temat tabel tymczasowych i tworzenia indeksu wbudowanego, zobacz CREATE TABLE (Transact-SQL).

Ograniczenia CHECK, wartości DOMYŚLNE i obliczone kolumny w tabeli deklaracji typu nie mogą wywoływać funkcji zdefiniowanych przez użytkownika. Operacja przypisania między tabelą zmiennych nie jest obsługiwana. Ponieważ tabeli zmiennych mają ograniczony zakres i nie są częścią trwałej bazy danych, wycofywanie transakcji nie ma na nie wpływu. Nie można zmienić zmiennych tabeli po utworzeniu.

Zmienne tabel nie mogą być używane jako element docelowy klauzuli INTO w instrukcji SELECT ... INTO.

Nie można użyć instrukcji EXEC ani procedury składowanej sp_executesql do uruchomienia dynamicznego zapytania programu SQL Server odwołującego się do zmiennej tabeli, jeśli zmienna tabeli została utworzona poza instrukcją EXEC lub procedurą składowaną sp_executesql. Ponieważ zmienne tabeli można odwoływać się tylko w ich zakresie lokalnym, instrukcja EXEC i sp_executesql procedura składowana byłaby poza zakresem zmiennej tabeli. Można jednak utworzyć zmienną tabeli i wykonać wszystkie operacje przetwarzania wewnątrz instrukcji EXEC lub procedury składowanej sp_executesql, ponieważ wówczas zakres lokalny zmiennych tabeli znajduje się w instrukcji EXEC lub sp_executesql procedurze składowanej.

Zmienna tabeli nie jest strukturą tylko do pamięci. Ponieważ zmienna tabeli może przechowywać więcej danych niż w pamięci, musi mieć miejsce na dysku do przechowywania danych. Zmienne tabeli są tworzone w bazie danych tempdb podobnej do tabel tymczasowych. Jeśli pamięć jest dostępna, zarówno zmienne tabeli, jak i tabele tymczasowe są tworzone i przetwarzane w pamięci (pamięć podręczna danych).

Zmienne tabeli a tabele tymczasowe

Wybór między zmiennymi tabeli a tabelami tymczasowymi zależy od następujących czynników:

• Liczba wierszy wstawionych do tabeli.
• Liczba ponownych kompilacji, z których jest zapisywane zapytanie.
• Typ zapytań i ich zależność od indeksów i statystyk dotyczących wydajności.

W niektórych sytuacjach pomocne jest podzielenie procedury składowanej z tabelami tymczasowymi na mniejsze procedury składowane w celu ponownego skompilowania mniejszych jednostek.

Ogólnie rzecz biorąc, zmienne tabeli są używane zawsze, gdy jest to możliwe, z wyjątkiem sytuacji, gdy istnieje znaczna ilość danych i istnieje powtarzające się użycie tabeli. W takim przypadku można utworzyć indeksy w tabeli tymczasowej, aby zwiększyć wydajność zapytań. Jednak każdy scenariusz może być inny. Firma Microsoft zaleca przetestowanie, czy zmienne tabeli są bardziej przydatne niż tabele tymczasowe dla określonego zapytania lub procedury składowanej.

Przykłady

A. Deklarowanie zmiennej typu tabeli

Poniższy przykład tworzy tabelę zmienną, która przechowuje wartości określone w klauzuli OUTPUT instrukcji UPDATE. Następuje dwie instrukcje SELECT, które zwracają wartości w @MyTableVar i wyniki operacji aktualizacji w tabeli Employee. Wyniki w kolumnie INSERTED.ModifiedDate różnią się od wartości w kolumnie ModifiedDate w tabeli Employee. Ta różnica polega na tym, że wyzwalacz AFTER UPDATE, który aktualizuje wartość ModifiedDate do bieżącej daty, jest zdefiniowany w tabeli Employee. Jednak kolumny zwrócone z OUTPUT odzwierciedlają dane przed wyzwoleniem wyzwalaczy. Aby uzyskać więcej informacji, zobacz OUTPUT Clause (Transact-SQL).

USE AdventureWorks2022;
GO
DECLARE @MyTableVar TABLE (
    EmpID INT NOT NULL,
    OldVacationHours INT,
    NewVacationHours INT,
    ModifiedDate DATETIME
);

UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25
OUTPUT INSERTED.BusinessEntityID,
    DELETED.VacationHours,
    INSERTED.VacationHours,
    INSERTED.ModifiedDate
INTO @MyTableVar;

--Display the result set of the table variable.
SELECT EmpID,
    OldVacationHours,
    NewVacationHours,
    ModifiedDate
FROM @MyTableVar;
GO

--Display the result set of the table.
--Note that ModifiedDate reflects the value generated by an
--AFTER UPDATE trigger.
SELECT TOP (10) BusinessEntityID,
    VacationHours,
    ModifiedDate
FROM HumanResources.Employee;
GO

B. Tworzenie wbudowanej funkcji zwracanej przez tabelę

Poniższy przykład zwraca funkcję w tabeli wbudowanej. Zwraca trzy kolumny ProductID, Namei agregację sum od roku do daty według sklepu jako YTD Total dla każdego produktu sprzedawanego w sklepie.

USE AdventureWorks2022;
GO
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN
(
    SELECT P.ProductID,
        P.Name,
        SUM(SD.LineTotal) AS 'Total'
    FROM Production.Product AS P
    INNER JOIN Sales.SalesOrderDetail AS SD
        ON SD.ProductID = P.ProductID
    INNER JOIN Sales.SalesOrderHeader AS SH
        ON SH.SalesOrderID = SD.SalesOrderID
    INNER JOIN Sales.Customer AS C
        ON SH.CustomerID = C.CustomerID
    WHERE C.StoreID = @storeid
    GROUP BY P.ProductID,
        P.Name
);
GO

Aby wywołać funkcję, uruchom to zapytanie.

SELECT * FROM Sales.ufn_SalesByStore (602);

Zobacz też

• COLLATE (Transact-SQL)
• CREATE FUNCTION (Transact-SQL)
• User-Defined Functions
• CREATE TABLE (Transact-SQL)
• DECLARE @local_variable (Transact-SQL)
• użyj parametrów Table-Valued (aparatu bazy danych)
• wskazówki dotyczące zapytań (Transact-SQL)