tabel (Transact-SQL)
van toepassing op:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL-database in Microsoft Fabric
tabel is een speciaal gegevenstype dat wordt gebruikt voor het opslaan van een resultatenset voor verwerking op een later tijdstip. tabel wordt voornamelijk gebruikt voor het tijdelijk opslaan van een set rijen die worden geretourneerd als resultatenset met tabelwaarden. Functies en variabelen kunnen worden gedeclareerd als type tabel. tabel variabelen kunnen worden gebruikt in functies, opgeslagen procedures en batches. Als u variabelen van het type tabelwilt declareren, gebruikt u DECLARE @local_variable.
Transact-SQL syntaxisconventies
Syntaxis
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 )
}
Argumenten
table_type_definition
Dezelfde subset met informatie die wordt gebruikt voor het definiëren van een tabel in CREATE TABLE. De tabeldeclaratie bevat kolomdefinities, namen, gegevenstypen en beperkingen. De enige toegestane beperkingstypen zijn PRIMAIRE SLEUTEL, UNIEKE SLEUTEL en NULL.
Zie CREATE TABLE (Transact-SQL), CREATE FUNCTION (Transact-SQL) en DECLARE @local_variable (Transact-SQL)voor meer informatie over de syntaxis.
collation_definition
De sortering van de kolom die bestaat uit een Microsoft Windows-landinstelling en een vergelijkingsstijl, een Windows-landinstelling en de binaire notatie, of een Microsoft SQL Server-sortering. Als collation_definition niet is opgegeven, neemt de kolom de sortering van de huidige database over. Of als de kolom is gedefinieerd als een door de gebruiker gedefinieerd type common language runtime (CLR), neemt de kolom de sortering van het door de gebruiker gedefinieerde type over.
Opmerkingen
tabel Verwijzingsvariabelen op naam in de FROM-component van een batch, zoals in het volgende voorbeeld wordt weergegeven:
SELECT Employee_ID, Department_ID FROM @MyTableVar;
Buiten een FROM-component moet tabel variabelen worden verwezen met behulp van een alias, zoals wordt weergegeven in het volgende voorbeeld:
SELECT EmployeeID,
DepartmentID
FROM @MyTableVar m
INNER JOIN Employee
ON m.EmployeeID = Employee.EmployeeID
AND m.DepartmentID = Employee.DepartmentID;
tabel variabelen bieden de volgende voordelen ten opzichte van tijdelijke tabellen voor kleinschalige query's die queryplannen hebben die niet veranderen en wanneer problemen met opnieuw compileren dominant zijn:
Een tabel variabele gedraagt zich als een lokale variabele. Het heeft een goed gedefinieerd bereik. Deze variabele kan worden gebruikt in de functie, opgeslagen procedure of batch waarin deze wordt gedeclareerd.
Binnen het bereik kan een tabel variabele worden gebruikt als een gewone tabel. Het kan overal worden toegepast waar een tabel of tabelexpressie wordt gebruikt in de instructies SELECT, INSERT, UPDATE en DELETE. tabel kan echter niet worden gebruikt in de volgende instructie:
SELECT select_list INTO table_variable;
tabel variabelen automatisch worden opgeschoond aan het einde van de functie, opgeslagen procedure of batch waarin ze zijn gedefinieerd.
tabel variabelen die worden gebruikt in opgeslagen procedures, leiden tot minder opgeslagen procedurecompilaties dan wanneer tijdelijke tabellen worden gebruikt wanneer er geen kostengebaseerde keuzes zijn die van invloed zijn op de prestaties.
Tabelvariabelen worden volledig geïsoleerd in de batch waarmee ze worden gemaakt, zodat er geen opnieuw moet worden opgelost wanneer er een CREATE- of ALTER-instructie plaatsvindt, die kan optreden met een tijdelijke tabel. Tijdelijke tabellen hebben deze heroplossing nodig, zodat naar de tabel kan worden verwezen vanuit een geneste opgeslagen procedure. Tabelvariabelen vermijden deze stap volledig, zodat opgeslagen procedures een plan kunnen gebruiken dat al is gecompileerd, waardoor resources worden opgeslagen om de opgeslagen procedure te verwerken.
Transacties met betrekking tot tabel variabelen duren alleen voor de duur van een update op de tabel variabele. Als zodanig vereisen tabel variabelen minder vergrendelings- en logboekregistratiebronnen.
Beperkingen en beperkingen
tabel variabelen geen distributiestatistieken hebben. Ze activeren geen hercompilielen. In veel gevallen bouwt de optimizer een queryplan op basis van de aanname dat de tabelvariabele geen rijen heeft. Daarom moet u voorzichtig zijn met het gebruik van een tabelvariabele als u een groter aantal rijen verwacht (groter dan 100). Tijdelijke tabellen kunnen in dit geval een betere oplossing zijn. Voor query's die de tabelvariabele samenvoegen met andere tabellen, gebruikt u de hint RECOMPILE, waardoor de optimizer de juiste kardinaliteit voor de tabelvariabele gebruikt.
tabel variabelen worden niet ondersteund in het op kosten gebaseerde redeneringsmodel van de SQL Server-optimalisatie. Ze moeten daarom niet worden gebruikt wanneer op kosten gebaseerde keuzes nodig zijn om een efficiënt queryplan te bereiken. Tijdelijke tabellen hebben de voorkeur wanneer opties op basis van kosten vereist zijn. Dit plan omvat doorgaans query's met joins, beslissingen voor parallelle uitvoering en opties voor indexselectie.
Query's die tabel wijzigen variabelen genereren geen parallelle uitvoeringsplannen voor query's. De prestaties kunnen worden beïnvloed wanneer grote tabel variabelen of tabel variabelen in complexe query's worden gewijzigd. U kunt in plaats daarvan tijdelijke tabellen gebruiken in situaties waarin tabel variabelen worden gewijzigd. Zie CREATE TABLE (Transact-SQL)voor meer informatie. Query's die tabel lezen variabelen zonder deze te wijzigen, kunnen nog steeds worden geparallelliseerd.
Belangrijk
Databasecompatibiliteitsniveau 150 verbetert de prestaties van tabelvariabelen met de introductie van tabelvariabele. Zie Tabelvariabelevoor meer informatie.
Indexen kunnen niet expliciet worden gemaakt voor tabel variabelen en er worden geen statistieken bewaard op tabel variabelen. Vanaf SQL Server 2014 (12.x) is er een nieuwe syntaxis geïntroduceerd waarmee u bepaalde indextypen inline kunt maken met de tabeldefinitie. Met deze nieuwe syntaxis kunt u indexen maken voor tabel variabelen als onderdeel van de tabeldefinitie. In sommige gevallen kunnen de prestaties worden verbeterd met behulp van tijdelijke tabellen, die volledige indexondersteuning en statistieken bieden. Zie CREATE TABLE (Transact-SQL)voor meer informatie over het maken van tijdelijke tabellen en het maken van inline-indexen.
CHECK-beperkingen, STANDAARDwaarden en berekende kolommen in de tabel typedeclaratie kan geen door de gebruiker gedefinieerde functies aanroepen. Toewijzingsbewerking tussen tabel variabelen wordt niet ondersteund. Omdat tabel variabelen een beperkt bereik hebben en geen deel uitmaken van de permanente database, hebben transactiebacks geen invloed op deze variabelen. Tabelvariabelen kunnen niet worden gewijzigd na het maken.
Tabellenvariabelen kunnen niet worden gebruikt als het doel van de INTO-component in een SELECT ... INTO-instructie.
U kunt de EXEC-instructie of de sp_executesql opgeslagen procedure niet gebruiken om een dynamische SQL Server-query uit te voeren die verwijst naar een tabelvariabele, als de tabelvariabele buiten de EXEC-instructie of de sp_executesql opgeslagen procedure is gemaakt. Omdat alleen in het lokale bereik naar tabelvariabelen kan worden verwezen, vallen een EXEC-instructie en een sp_executesql opgeslagen procedure buiten het bereik van de tabelvariabele. U kunt echter de tabelvariabele maken en alle verwerking uitvoeren in de EXEC-instructie of de sp_executesql opgeslagen procedure, omdat de lokale bereik van de tabelvariabelen zich in de EXEC-instructie of de sp_executesql opgeslagen procedure bevindt.
Een tabelvariabele is geen structuur met alleen geheugen. Omdat een tabelvariabele meer gegevens kan bevatten dan in het geheugen past, moet deze een plaats op de schijf hebben om gegevens op te slaan. Tabelvariabelen worden gemaakt in de tempdb database die vergelijkbaar is met tijdelijke tabellen. Als er geheugen beschikbaar is, worden zowel tabelvariabelen als tijdelijke tabellen gemaakt en verwerkt in het geheugen (gegevenscache).
Tabelvariabelen versus tijdelijke tabellen
Het kiezen tussen tabelvariabelen en tijdelijke tabellen is afhankelijk van deze factoren:
- Het aantal rijen dat wordt ingevoegd in de tabel.
- Het aantal hercompilaties van de query wordt opgeslagen.
- Het type query's en hun afhankelijkheid van indexen en statistieken voor prestaties.
In sommige situaties is het handig om een opgeslagen procedure met tijdelijke tabellen in kleinere opgeslagen procedures op te splitsen, zodat hercompilatie plaatsvindt op kleinere eenheden.
Over het algemeen gebruikt u waar mogelijk tabelvariabelen, behalve wanneer er een aanzienlijk aantal gegevens is en er een herhaald gebruik van de tabel plaatsvindt. In dat geval kunt u indexen maken in de tijdelijke tabel om de queryprestaties te verbeteren. Elk scenario kan echter verschillen. Microsoft raadt u aan om te testen of tabelvariabelen nuttiger zijn dan tijdelijke tabellen voor een bepaalde query of opgeslagen procedure.
Voorbeelden
Een. Een variabele van het type tabel declareren
In het volgende voorbeeld wordt een tabel gemaakt variabele waarin de waarden worden opgeslagen die zijn opgegeven in de OUTPUT-component van de UPDATE-instructie. Twee SELECT instructies volgen, die de waarden in @MyTableVar en de resultaten van de updatebewerking in de Employee tabel retourneren. De resultaten in de kolom INSERTED.ModifiedDate verschillen van de waarden in de kolom ModifiedDate in de Employee tabel. Dit verschil komt doordat de AFTER UPDATE trigger, die de waarde van ModifiedDate bijwerken naar de huidige datum, is gedefinieerd in de Employee tabel. De kolommen die worden geretourneerd uit OUTPUT weerspiegelen echter de gegevens voordat triggers worden geactiveerd. Zie OUTPUT Component (Transact-SQL)voor meer informatie.
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. Een inline-tabelwaardefunctie maken
In het volgende voorbeeld wordt een inline-tabelwaardefunctie geretourneerd. Het retourneert drie kolommen ProductID, Nameen het totaal van totalen van het jaar tot heden per winkel als YTD Total voor elk product dat aan de winkel wordt verkocht.
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
Voer deze query uit om de functie aan te roepen.
SELECT * FROM Sales.ufn_SalesByStore (602);