Tabulka (Transact-SQL)

platí pro:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL databáze v Microsoft Fabric

tabulce je speciální datový typ, který slouží k uložení sady výsledků pro pozdější zpracování. tabulky se primárně používá k dočasnému ukládání sady řádků, které se vrátí jako sada výsledků funkce s hodnotou tabulky. Funkce a proměnné lze deklarovat jako typ tabulky. tabulce proměnných lze použít ve funkcích, uložených procedurách a dávkách. Chcete-li deklarovat proměnné typu tabulky, použijte DECLARE @local_variable.

Transact-SQL konvence syntaxe

Syntax

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 )
     }

Arguments

table_type_definition

Stejná podmnožina informací, která slouží k definování tabulky v CREATE TABLE. Deklarace tabulky obsahuje definice sloupců, názvy, datové typy a omezení. Jediné povolené typy omezení jsou PRIMÁRNÍ KLÍČ, JEDINEČNÝ KLÍČ a NULL.

Další informace o syntaxi naleznete v tématu CREATE TABLE (Transact-SQL), CREATE FUNCTION (Transact-SQL)a DECLARE @local_variable (Transact-SQL).

collation_definition

Kolace sloupce, který se skládá z národního prostředí Systému Microsoft Windows a stylu porovnání, národního prostředí systému Windows a binární notace nebo kolace Microsoft SQL Serveru. Pokud není zadaný collation_definition, sloupec zdědí kolaci aktuální databáze. Nebo pokud je sloupec definovaný uživatelem definovaným typem modulu CLR (Common Language Runtime), sloupec dědí kolaci uživatelem definovaného typu.

Remarks

tabulka Odkazovat proměnné podle názvu v klauzuli FROM dávky, jak je znázorněno v následujícím příkladu:

SELECT Employee_ID, Department_ID FROM @MyTableVar;

Mimo klauzuli FROM musí být tabulka proměnných odkazována pomocí aliasu, jak je znázorněno v následujícím příkladu:

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

tabulek proměnných poskytují následující výhody oproti dočasným tabulkám pro dotazy v malém měřítku, které mají plány dotazů, které se nemění a kdy jsou obavy z rekompilace dominantní:

• Tabulka proměnné se chová jako místní proměnná. Má dobře definovaný obor. Tuto proměnnou lze použít ve funkci, uložené proceduře nebo dávce, ve které je deklarována.

  V rámci oboru lze tabulku proměnnou použít jako běžnou tabulku. Může se použít kdekoli, kde se v příkazech SELECT, INSERT, UPDATE a DELETE používá výraz tabulky nebo tabulky. Tabulku ale nejde použít v následujícím příkazu:

SELECT select_list INTO table_variable;

tabulce proměnné se automaticky vyčistí na konci funkce, uložené procedury nebo dávky, ve které jsou definované.

• tabulky proměnných, které se používají v uložených procedurách, způsobují menší počet rekompilace uložených procedur, než kdy se používají dočasné tabulky, pokud neexistují žádné volby založené na nákladech, které mají vliv na výkon.

  Proměnné tabulky jsou zcela izolované od dávky, která je vytvoří, takže opětovného řešení nemusí nastat, když dojde k příkazu CREATE nebo ALTER, což může nastat s dočasnou tabulkou. Dočasné tabulky potřebují toto opětovné rozlišení, aby na tabulku bylo možné odkazovat z vnořené uložené procedury. Proměnné tabulky se tomuto kroku zcela vyhýbají, takže uložené procedury mohou používat plán, který je již zkompilován, a tím ukládat prostředky ke zpracování uložené procedury.

• Transakce zahrnující tabulky proměnné trvají pouze po dobu trvání aktualizace tabulce proměnné. Například tabulky proměnných vyžaduje méně prostředků uzamčení a protokolování.

Omezení a omezení

tabulce proměnné nemají distribuční statistiky. Neaktivují rekompily. V mnoha případech optimalizátor sestaví plán dotazu na předpokladu, že proměnná tabulky nemá žádné řádky. Z tohoto důvodu byste měli být opatrní při použití proměnné tabulky, pokud očekáváte větší počet řádků (větší než 100). Dočasné tabulky mohou být v tomto případě lepším řešením. Pro dotazy, které spojují proměnnou tabulky s jinými tabulkami, použijte nápovědu RECOMPILE, která způsobí, že optimalizátor použije správnou kardinalitu pro proměnnou tabulky.

tabulky proměnných nejsou podporovány v modelu optimalizace SQL Serveru založeném na nákladech. Proto by se neměly používat, pokud jsou k dosažení efektivního plánu dotazů potřeba volby založené na nákladech. Dočasné tabulky se preferují v případě, že jsou vyžadovány volby založené na nákladech. Tento plán obvykle zahrnuje dotazy se spojeními, rozhodnutími o paralelismu a volbami výběru indexu.

Dotazy, které upravují tabulky proměnných, negenerují plány paralelního provádění dotazů. Výkon může být ovlivněn, když se změní velké tabulky proměnných nebo tabulky proměnných v složitých dotazech. Místo toho zvažte použití dočasných tabulek v situacích, kdy se tabulky proměnných změní. Další informace naleznete v tématuCREATE TABLE (Transact-SQL). Dotazy, které čtou tabulky proměnných, aniž by je upravovaly, je stále možné paralelizovat.

Important

Úroveň kompatibility databáze 150 zlepšuje výkon proměnných tabulek zavedením odložené kompilace. Další informace najdete v tématu odložené kompilace proměnné tabulky.

Indexy nelze vytvářet explicitně u tabulek proměnných a proměnných tabulce se neuchovávají žádné statistiky. Počínaje SQL Serverem 2014 (12.x) byla zavedena nová syntaxe, která umožňuje vytvořit určité typy indexů vložené s definicí tabulky. Pomocí této nové syntaxe můžete v rámci definice tabulky vytvořit indexy tabulce proměnných. V některýchpřípadechch Další informace o dočasných tabulkách a vytváření vložených indexů najdete v tématuCREATE TABLE (Transact-SQL).

Check omezení, výchozí hodnoty a počítané sloupce v tabulce deklarace typu nemůžou volat uživatelem definované funkce. Operace přiřazení mezi tabulkami proměnnými není podporovaná. Vzhledem k tomu, že tabulky proměnné mají omezený rozsah a nejsou součástí trvalé databáze, vrácení transakcí na ně nemá vliv. Proměnné tabulky nelze po vytvoření změnit.

Proměnné tabulek nelze použít jako cíl klauzule INTO v příkazu SELECT ... INTO.

Příkaz EXEC ani uloženou proceduru sp_executesql nemůžete použít ke spuštění dynamického dotazu SQL Serveru, který odkazuje na proměnnou tabulky, pokud byla proměnná tabulky vytvořena mimo příkaz EXEC nebo sp_executesql uložená procedura. Vzhledem k tomu, že proměnné tabulky lze odkazovat pouze v místním oboru, příkaz EXEC a sp_executesql uložená procedura by byly mimo rozsah proměnné tabulky. Můžete však vytvořit proměnnou tabulky a provést veškeré zpracování uvnitř příkazu EXEC nebo sp_executesql uložená procedura, protože pak jsou proměnné tabulky místní obor v příkazu EXEC nebo sp_executesql uložená procedura.

Proměnná tabulky není struktura jen pro paměť. Protože proměnná tabulky může obsahovat více dat, než se vejde do paměti, musí mít místo na disku pro ukládání dat. Proměnné tabulky se vytvářejí v databázi tempdb podobně jako dočasné tabulky. Pokud je k dispozici paměť, vytvoří se proměnné tabulek i dočasné tabulky a zpracovávají se v paměti (mezipaměť dat).

Proměnné tabulky vs. dočasné tabulky

Volba mezi proměnnými tabulky a dočasnými tabulkami závisí na těchto faktorech:

• Počet řádků, které jsou vloženy do tabulky.
• Počet rekompilace, ze kterých je dotaz uložen.
• Typ dotazů a jejich závislost na indexech a statistikách pro výkon.

V některých situacích je užitečné rozdělit uloženou proceduru s dočasnými tabulkami na menší uložené procedury, aby se rekompilace uskutečnila na menších jednotkách.

Obecně platí, že proměnné tabulky se používají vždy, když je to možné, s výjimkou případů, kdy existuje velký objem dat a opakované použití tabulky. V takovém případě můžete v dočasné tabulce vytvořit indexy, které zvýší výkon dotazů. Každý scénář se ale může lišit. Microsoft doporučuje otestovat, jestli jsou proměnné tabulky užitečnější než dočasné tabulky pro konkrétní dotaz nebo uloženou proceduru.

Examples

A. Deklarace proměnné tabulky typů

Následující příklad vytvoří tabulku proměnnou, která ukládá hodnoty zadané v klauzuli OUTPUT příkazu UPDATE. Následují dva příkazy SELECT, které vrátí hodnoty v @MyTableVar a výsledky operace aktualizace v tabulce Employee. Výsledkem je, že sloupec INSERTED.ModifiedDate se liší od hodnot ve sloupci ModifiedDate v tabulce Employee. Tento rozdíl je ten, že trigger AFTER UPDATE, který aktualizuje hodnotu ModifiedDate na aktuální datum, je definován v tabulce Employee. Sloupce vrácené z OUTPUT však odrážejí data před aktivací triggerů. Další informace naleznete v tématu VÝSTUPNÍ klauzule (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. Vytvoření vložené funkce s hodnotou tabulky

Následující příklad vrátí vloženou funkci s hodnotou tabulky. Vrátí tři sloupce ProductID, Namea agregaci součtů od roku do data podle prodejen jako YTD Total pro každý produkt prodaných do obchodu.

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

Pokud chcete funkci vyvolat, spusťte tento dotaz.

SELECT * FROM Sales.ufn_SalesByStore (602);

Viz také

• COLLATE (Transact-SQL)
• CREATE FUNCTION (Transact-SQL)
• User-Defined Funkce
• VYTVOŘIT TABULKU (Transact-SQL)
• DECLARE @local_variable (Transact-SQL)
• Použít parametry Table-Valued (databázový stroj)
• nápovědy k dotazům (Transact-SQL)