táblázat (Transact-SQL)

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseFelügyelt Azure SQL-példánySQL-adatbázis a Microsoft Fabricben

tábla egy speciális adattípus, amely egy későbbi feldolgozásra szánt eredménykészlet tárolására szolgál. tábla elsősorban olyan sorok ideiglenes tárolására szolgál, amelyeket a függvény eredményhalmazaként ad vissza a rendszer. A függvények és változók deklarálhatók tábla. tábla változók függvényekben, tárolt eljárásokban és kötegekben használhatók. A táblatípusú változók deklarálásához használja a .

Transact-SQL szintaxis konvenciói

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

Ugyanaz az információhalmaz, amely a CREATE TABLE tábláinak definiálásához használatos. A tábladeklaráció oszlopdefiníciókat, neveket, adattípusokat és korlátozásokat tartalmaz. Az egyetlen engedélyezett kényszertípus az ELSŐDLEGES KULCS, AZ EGYEDI KULCS és a NULL.

A szintaxissal kapcsolatos további információkért lásd: CREATE TABLE (Transact-SQL), CREATE FÜGGVÉNY (Transact-SQL)és DECLARE @local_variable (Transact-SQL).

collation_definition

A Microsoft Windows területi beállításból és összehasonlítási stílusból, Windows területi beállításból és bináris jelölésből vagy Microsoft SQL Server-rendezésből álló oszlop rendezése. Ha nincs megadva collation_definition, az oszlop örökli az aktuális adatbázis rendezést. Vagy ha az oszlop általános nyelvi futtatókörnyezet (CLR) felhasználó által definiált típusként van definiálva, az oszlop örökli a felhasználó által definiált típus rendezést.

Remarks

tábla neve alapján referenciaváltozókat a köteg FROM záradékában, ahogy az alábbi példa is mutatja:

SELECT Employee_ID, Department_ID FROM @MyTableVar;

A FROM záradékon kívül tábla változókra alias használatával kell hivatkozni, ahogyan az a következő példában látható:

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

tábla változói a következő előnyöket biztosítják az ideiglenes táblákhoz olyan kis méretű lekérdezésekhez, amelyek nem változnak, és ha az újrafordítási problémák dominálnak:

• A tábla változó helyi változóként viselkedik. Jól definiált hatókörrel rendelkezik. Ez a változó abban a függvényben, tárolt eljárásban vagy kötegben használható, amelyben deklarálva van.

  A hatókörön belül egy tábla változó használható normál táblához hasonlóan. Bárhol alkalmazható, ahol a SELECT, INSERT, UPDATE és DELETE utasításban tábla- vagy táblakifejezést használnak. tábla azonban nem használható az alábbi utasításban:

SELECT select_list INTO table_variable;

tábla változók automatikusan törlődnek annak a függvénynek, tárolt eljárásnak vagy kötegnek a végén, amelyben definiálva vannak.

• tábla tárolt eljárásokban használt változók kevesebb tárolt eljárás újrafordítását okozzák, mint ha ideiglenes táblákat használnak, ha nincsenek olyan költségalapú választási lehetőségek, amelyek befolyásolnák a teljesítményt.

  A táblaváltozók teljesen el vannak különítve az őket létrehozó kötegtől, így nem kell újrafeloldási a CREATE vagy ALTER utasítás végrehajtásakor, ami ideiglenes táblával történhet. Az ideiglenes tábláknak szüksége van erre az újrafeloldásra, hogy a táblázat beágyazott tárolt eljárásból hivatkozható legyen. A táblaváltozók teljesen elkerülik ezt a lépést, így a tárolt eljárások használhatják a már lefordított tervet, így erőforrásokat takaríthatnak meg a tárolt eljárás feldolgozásához.

• A tábla változókat tartalmazó tranzakciók csak a tábla változó frissítésének időtartamára tartanak. Ezért tábla változók kevesebb zárolási és naplózási erőforrást igényelnek.

Korlátozások és korlátozások

tábla változók nem rendelkeznek eloszlási statisztikával. Nem váltanak ki újrafordításokat. Az optimalizáló sok esetben abból a feltételezésből épít ki lekérdezési tervet, hogy a táblaváltozónak nincsenek sorai. Ezért óvatosnak kell lennie a táblaváltozó használata során, ha nagyobb számú sort vár (100-nál nagyobb). Ebben az esetben a temp táblák jobb megoldást jelenthetnek. A táblaváltozót más táblákkal összekapcsoló lekérdezésekhez használja a RECOMPILE tippet, amely miatt az optimalizáló a táblázatváltozó megfelelő számosságát használja.

tábla változók nem támogatottak az SQL Server-optimalizáló költségalapú érvelési modelljében. Ezért ezeket nem szabad használni, ha költségalapú választási lehetőségekre van szükség a hatékony lekérdezési terv eléréséhez. Az ideiglenes táblákat akkor célszerű használni, ha költségalapú választásra van szükség. Ez a terv általában összekapcsolt lekérdezéseket, párhuzamossági döntéseket és indexválasztási lehetőségeket tartalmaz.

A tábla változókat módosító lekérdezések nem hoznak létre párhuzamos lekérdezés-végrehajtási terveket. A teljesítmény akkor lehet hatással, ha a nagy tábla változói, vagy tábla változói összetett lekérdezésekben módosulnak. Fontolja meg ideiglenes táblák használatát olyan helyzetekben, amikor tábla változók módosulnak. További információ: CREATE TABLE (Transact-SQL). A táblázatot változókat módosító lekérdezések továbbra is párhuzamosak lehetnek.

Important

A 150-es adatbáziskompatibilitási szint javítja a táblaváltozók teljesítményét táblaváltozó késleltetett fordításibevezetésével. További információ: táblaváltozó késleltetett fordítási.

Az indexek nem hozhatók létre explicit módon tábla változókon, és tábla változói nem őriznek meg statisztikákat. Az SQL Server 2014 -től kezdve (12.x) új szintaxis jelent meg, amely lehetővé teszi bizonyos indextípusok beágyazott létrehozását a tábladefinícióval. Ezzel az új szintaxissal indexeket hozhat létre tábla változókon a tábladefiníció részeként. Bizonyos esetekben a teljesítmény javulhat ideiglenes táblák használatával, amelyek teljes körű indextámogatást és statisztikákat biztosítanak. További információ az ideiglenes táblákról és a beágyazott indexek létrehozásáról: CREATE TABLE (Transact-SQL).

A CHECK kényszerek, az ALAPÉRTELMEZETT értékek és a számított oszlopok a tábla típusdeklarációjában nem hívhatók meg felhasználó által definiált függvények. A tábla változók közötti hozzárendelési művelet nem támogatott. Mivel tábla változók hatóköre korlátozott, és nem részei az állandó adatbázisnak, a tranzakció-visszaállítások nincsenek hatással rájuk. A táblaváltozók nem módosíthatók a létrehozás után.

A táblaváltozók nem használhatók a INTO záradék célhelyeként egy SELECT ... INTO utasításban.

Az EXEC utasítás vagy a sp_executesql tárolt eljárás nem használható táblaváltozót hivatkozó dinamikus SQL Server-lekérdezés futtatására, ha a táblaváltozó az EXEC utasításon vagy a sp_executesql tárolt eljáráson kívül lett létrehozva. Mivel a táblaváltozók csak a helyi hatókörükben hivatkozhatók, egy EXEC-utasítás és egy sp_executesql tárolt eljárás kívül esik a táblaváltozó hatókörén. Létrehozhatja azonban a táblaváltozót, és az EXEC-utasításban vagy a sp_executesql tárolt eljárásban is elvégezheti a feldolgozást, mivel a helyi táblaváltozók hatóköre az EXEC utasításban vagy a sp_executesql tárolt eljárásban található.

A táblaváltozók nem csak memóriaszerkezetek. Mivel egy táblaváltozó több adatot tárolhat, mint amennyi a memóriában elfér, lemezen kell lennie az adatok tárolásához. A táblaváltozók az ideiglenes táblákhoz hasonló tempdb adatbázisban jönnek létre. Ha rendelkezésre áll memória, a rendszer a memória (adatgyorsítótár) során a táblaváltozókat és az ideiglenes táblákat is létrehozza és feldolgozja.

Táblaváltozók és ideiglenes táblák

A táblaváltozók és az ideiglenes táblák közötti választás az alábbi tényezőktől függ:

• A táblázatba beszúrt sorok száma.
• A lekérdezés által mentett újrafordítások száma.
• A lekérdezések típusa és a teljesítmény indexeihez és statisztikáihoz való függőségük.

Bizonyos esetekben hasznos lehet, ha egy tárolt eljárást ideiglenes táblákkal kisebb tárolt eljárásokra bont, így az újrafordítás kisebb egységeken történik.

Általában a táblaváltozókat akkor használja, amikor csak lehetséges, kivéve, ha jelentős mennyiségű adat áll rendelkezésre, és a tábla ismételt használata történik. Ebben az esetben indexeket hozhat létre az ideiglenes táblában a lekérdezési teljesítmény növelése érdekében. Az egyes forgatókönyvek azonban eltérőek lehetnek. A Microsoft azt javasolja, hogy tesztelje, hogy a táblaváltozók hasznosabbak-e, mint egy adott lekérdezés vagy tárolt eljárás ideiglenes táblái.

Examples

A. Típustáblázat változójának deklarálása

Az alábbi példa létrehoz egy tábla változót, amely az UPDATE utasítás OUTPUT záradékában megadott értékeket tárolja. Két SELECT utasítás következik, amelyek a @MyTableVar értékeit és a frissítési művelet eredményeit a Employee táblában adja vissza. A INSERTED.ModifiedDate oszlop eredményei eltérnek a ModifiedDate tábla Employee oszlopában lévő értékektől. Ez a különbség azért van, mert a AFTER UPDATE eseményindító, amely frissíti az ModifiedDate értékét az aktuális dátumra, a Employee táblában van definiálva. A OUTPUT visszaadott oszlopok azonban az eseményindítók indítása előtt tükrözik az adatokat. További információ: OUTPUT záradék (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. Beágyazott táblaértékű függvény létrehozása

Az alábbi példa egy beágyazott táblaértékű függvényt ad vissza. Három oszlopot ad vissza, ProductID, Name, valamint a tárolónkénti évenkénti összesítéseket YTD Total az áruháznak eladott termékekhez.

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

A függvény meghívásához futtassa ezt a lekérdezést.

SELECT * FROM Sales.ufn_SalesByStore (602);

Lásd még

• COLLATE (Transact-SQL)
• CREATE függvény (Transact-SQL)
• User-Defined Függvények
• CREATE TABLE (Transact-SQL)
• DECLARE @local_variable (Transact-SQL)
• Table-Valued paraméterek (adatbázismotor) használata
• lekérdezési tippek (Transact-SQL)