table (Transact-SQL)

2025-01-15

Gäller för:SQL Server Azure SQL Database Azure SQL Managed Instance SQL-databas i Förhandsversion av Microsoft Fabric

tabell är en särskild datatyp som används för att lagra en resultatuppsättning för bearbetning vid ett senare tillfälle. tabell används främst för att tillfälligt lagra en uppsättning rader som returneras som resultatuppsättningen för tabellvärdesfunktionen. Funktioner och variabler kan deklareras som av typen tabell. tabell variabler kan användas i funktioner, lagrade procedurer och batchar. Om du vill deklarera variabler av typen tabellanvänder du DECLARE @local_variable.

Transact-SQL syntaxkonventioner

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

Samma delmängd av information som används för att definiera en tabell i CREATE TABLE. Tabelldeklarationen innehåller kolumndefinitioner, namn, datatyper och begränsningar. De enda villkorstyper som tillåts är PRIMÄRNYCKEL, UNIK NYCKEL och NULL.

Mer information om syntaxen finns i CREATE TABLE (Transact-SQL), CREATE FUNCTION (Transact-SQL)och DECLARE @local_variable (Transact-SQL).

collation_definition

Sortering av kolumnen som består av ett Microsoft Windows-språk och ett jämförelseformat, ett Windows-språk och binär notation eller en Microsoft SQL Server-sortering. Om collation_definition inte anges ärver kolumnen sorteringen av den aktuella databasen. Eller om kolumnen definieras som en användardefinierad typ av common language runtime (CLR) ärver kolumnen sorteringen av den användardefinierade typen.

Remarks

tabell Referensvariabler efter namn i en batchs FROM-sats, som du ser i följande exempel:

SELECT Employee_ID, Department_ID FROM @MyTableVar;

Utanför en FROM-sats måste tabell variabler refereras med hjälp av ett alias, enligt följande exempel:

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

tabell variabler ger följande fördelar jämfört med tillfälliga tabeller för småskaliga frågor som har frågeplaner som inte ändras och när omkompileringsproblem är dominerande:

En tabell variabel fungerar som en lokal variabel. Den har ett väldefinierat omfång. Den här variabeln kan användas i funktionen, den lagrade proceduren eller batchen där den deklareras.

Inom dess omfång kan en tabell variabel användas som en vanlig tabell. Det kan användas var som helst där ett tabell- eller tabelluttryck används i SELECT-, INSERT-, UPDATE- och DELETE-instruktioner. Men tabell inte kan användas i följande instruktion:

SELECT select_list INTO table_variable;

tabell variabler rensas automatiskt i slutet av funktionen, den lagrade proceduren eller batchen där de definieras.

tabell variabler som används i lagrade procedurer orsakar färre omkompileringar av lagrade procedurer än när temporära tabeller används när det inte finns några kostnadsbaserade alternativ som påverkar prestanda.

Tabellvariabler är helt isolerade till batchen som skapar dem så att ingen rematchning måste ske när en CREATE- eller ALTER-instruktion äger rum, vilket kan inträffa med en tillfällig tabell. Tillfälliga tabeller behöver den här upplösningen så att tabellen kan refereras från en kapslad lagrad procedur. Tabellvariabler undviker det här steget helt, så lagrade procedurer kan använda en plan som redan har kompilerats, vilket sparar resurser för att bearbeta den lagrade proceduren.
Transaktioner som involverar tabell variabler varar endast under en uppdatering i tabell variabel. Därför kräver tabell variabler färre låsnings- och loggningsresurser.

Begränsningar och begränsningar

tabell variabler inte har distributionsstatistik. De utlöser inte omkompileringar. I många fall skapar optimeraren en frågeplan utifrån antagandet att tabellvariabeln inte har några rader. Därför bör du vara försiktig med att använda en tabellvariabel om du förväntar dig ett större antal rader (större än 100). Temp-tabeller kan vara en bättre lösning i det här fallet. För frågor som ansluter tabellvariabeln till andra tabeller använder du tipset RECOMPILE, vilket gör att optimeraren använder rätt kardinalitet för tabellvariabeln.

tabell variabler stöds inte i SQL Server-optimerarens kostnadsbaserade resonemangsmodell. Därför bör de inte användas när kostnadsbaserade val krävs för att uppnå en effektiv frågeplan. Tillfälliga tabeller föredras när kostnadsbaserade alternativ krävs. Den här planen innehåller vanligtvis frågor med kopplingar, parallellitetsbeslut och alternativ för indexval.

Frågor som ändrar tabell variabler genererar inte parallella frågekörningsplaner. Prestanda kan påverkas när stora tabell variabler eller tabell variabler i komplexa frågor ändras. Överväg att använda tillfälliga tabeller i stället i situationer där tabell variabler ändras. Mer information finns i CREATE TABLE (Transact-SQL). Frågor som läser tabell variabler utan att ändra dem kan fortfarande parallelliseras.

Important

Databaskompatibilitetsnivå 150 förbättrar prestandan för tabellvariabler med introduktionen av tabellvariabeln uppskjuten kompilering. Mer information finns i Tabellvariabeln uppskjuten kompilering.

Index kan inte skapas explicit i tabell variabler och ingen statistik sparas på tabell variabler. Från och med SQL Server 2014 (12.x) introducerades ny syntax som gör att du kan skapa vissa indextyper infogat med tabelldefinitionen. Med den här nya syntaxen kan du skapa index i tabell variabler som en del av tabelldefinitionen. I vissa fall kan prestandan förbättras genom att använda tillfälliga tabeller i stället, vilket ger fullständigt indexstöd och statistik. Mer information om tillfälliga tabeller och skapande av infogade index finns i CREATE TABLE (Transact-SQL).

CHECK-begränsningar, STANDARDvärden och beräknade kolumner i tabellen typdeklaration kan inte anropa användardefinierade funktioner. Tilldelningsåtgärd mellan tabell variabler stöds inte. Eftersom tabell variabler har begränsat omfång och inte ingår i den beständiga databasen påverkar inte transaktionsåterställningen dem. Tabellvariabler kan inte ändras när de har skapats.

Tabellvariabler kan inte användas som mål för INTO-satsen i en SELECT ... INTO-instruktion.

Du kan inte använda EXEC-instruktionen eller den sp_executesql lagrade proceduren för att köra en dynamisk SQL Server-fråga som refererar till en tabellvariabel, om tabellvariabeln skapades utanför EXEC-instruktionen eller sp_executesql lagrad procedur. Eftersom tabellvariabler endast kan refereras till i deras lokala omfång ligger en EXEC-instruktion och en sp_executesql lagrad procedur utanför tabellvariabelns omfång. Du kan dock skapa tabellvariabeln och utföra all bearbetning i EXEC-instruktionen eller den sp_executesql lagrade proceduren eftersom det lokala omfånget för tabellvariabler finns i EXEC-instruktionen eller den sp_executesql lagrade proceduren.

En tabellvariabel är inte en endast minnesstruktur. Eftersom en tabellvariabel kan innehålla mer data än vad som får plats i minnet måste den ha en plats på disken för att lagra data. Tabellvariabler skapas i tempdb databas som liknar temporära tabeller. Om minne är tillgängligt skapas och bearbetas både tabellvariabler och temporära tabeller i minnet (datacachen).

Tabellvariabler jämfört med temporära tabeller

Om du väljer mellan tabellvariabler och temporära tabeller beror det på följande faktorer:

Antalet rader som infogas i tabellen.
Antalet omkompileringar som frågan sparas från.
Typen av frågor och deras beroende av index och statistik för prestanda.

I vissa situationer är det bra att dela upp en lagrad procedur med tillfälliga tabeller i mindre lagrade procedurer så att omkompileringen sker på mindre enheter.

I allmänhet använder du tabellvariabler när det är möjligt, förutom när det finns en betydande mängd data och tabellen används upprepade gånger. I så fall kan du skapa index i den tillfälliga tabellen för att öka frågeprestandan. Varje scenario kan dock vara olika. Microsoft rekommenderar att du testar om tabellvariabler är mer användbara än temporära tabeller för en viss fråga eller lagrad procedur.

Examples

A. Deklarera en variabel av typen tabell

I följande exempel skapas en tabell variabel som lagrar de värden som anges i UTDATA-satsen i UPDATE-instruktionen. Två SELECT-instruktioner följer, som returnerar värdena i @MyTableVar och resultatet av uppdateringsåtgärden i tabellen Employee. Resultatet i kolumnen INSERTED.ModifiedDate skiljer sig från värdena i kolumnen ModifiedDate i tabellen Employee. Den här skillnaden beror på att utlösaren AFTER UPDATE, som uppdaterar värdet för ModifiedDate till det aktuella datumet, definieras i tabellen Employee. Kolumnerna som returneras från OUTPUT återspegla data innan utlösare utlöses. Mer information finns i OUTPUT-sats (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. Skapa en infogad tabellvärdesfunktion

I följande exempel returneras en infogad tabellvärdesfunktion. Den returnerar tre kolumner ProductID, Nameoch summan av hittills i år totalt per butik som YTD Total för varje produkt som säljs till butiken.

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

Kör den här frågan för att anropa funktionen.

SELECT * FROM Sales.ufn_SalesByStore (602);

Feedback

Var den här sidan till hjälp?