table (Transact-SQL)

Article
04/13/2023

S’applique à :SQL Server Azure SQL Database Azure SQL Managed Instance

table est un type de données spécial utilisé pour stocker un jeu de résultats afin de le traiter ultérieurement. table est principalement utilisé pour stocker temporairement un ensemble de lignes retournées comme un jeu de résultats d’une fonction table. Les fonctions et les variables peuvent être déclarées comme étant de type table. Les variables de table peuvent être utilisées dans les fonctions, les procédures stockées et les lots. Pour déclarer des variables de type table, utilisez DECLARE @local_variable.

Conventions de la syntaxe Transact-SQL

Syntaxe

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

Remarque

Pour afficher la syntaxe Transact-SQL pour SQL Server 2014 (12.x) et versions antérieures, consultez la Documentation sur les versions antérieures.

Arguments

table_type_definition

Même sous-ensemble d’informations que celui utilisé pour définir une table dans CREATE TABLE. La déclaration de table inclut des définitions de colonnes, des noms, des types de données et des contraintes. Les seuls types de contraintes autorisés sont PRIMARY KEY, UNIQUE KEY et NULL.

Pour plus d’informations sur la syntaxe, consultez CREATE TABLE (Transact-SQL), CREATE FUNCTION (Transact-SQL) et DECLARE @local_variable (Transact-SQL).

collation_definition

Classement de la colonne composée de paramètres régionaux Windows et d’un style de comparaison, de paramètres régionaux Windows et d’une notation binaire ou d’un classement Microsoft SQL Server. Si l’argument collation_definition n’est pas spécifié, la colonne hérite du classement de la base de données active. Ou, si la colonne est définie comme un type CLR (Common Language Runtime) défini par l'utilisateur, elle hérite du classement du type défini par l'utilisateur.

Notes

Variables de référence table par nom dans la clause FROM d’un traitement, comme dans l’exemple suivant :

SELECT Employee_ID, Department_ID FROM @MyTableVar;

En dehors d’une clause FROM, les variables de table doivent être référencées en utilisant un alias, comme illustré dans l’exemple suivant :

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

Par rapport aux requêtes temporaires, les variables de table offrent les avantages suivants pour les requêtes à petite échelle dont les plans ne changent pas et qui rencontrent principalement des problèmes de recompilation :

Une variable de table se comporte comme une variable locale. Elle possède une étendue bien définie. Cette variable peut être utilisée dans la fonction, la procédure stockée ou le lot dans lequel elle est déclarée.

Dans les limites de son étendue, une variable de table peut être utilisée comme une table normale. Elle peut s'appliquer partout où une table, ou expression de table, est utilisée dans les instructions SELECT, INSERT, UPDATE et DELETE. Cependant, table ne peut pas être utilisée dans l’instruction suivante :

SELECT select_list INTO table_variable;

Les variables de table sont automatiquement nettoyées à la fin de la fonction, de la procédure stockée ou du traitement dans lequel elles sont définies.

Les variables de table utilisées dans les procédures stockées provoquent moins de recompilations de procédure stockée que si des tables temporaires sont utilisées quand aucun choix basé sur les coûts n’affecte les performances.

Les variables de table sont complètement isolées du lot qui les crée. Cela leur évite d’avoir à passer par une nouvelle résolution quand une instruction CREATE ou ALTER s’exécute, ce qui peut se produire avec une table temporaire. Les tables temporaires doivent passer par cette nouvelle résolution pour pouvoir être référencées à partir d’une procédure stockée imbriquée. Les variables de table ignorent cette étape complètement. Cela permet aux procédures stockées d’utiliser un plan déjà compilé, et ainsi d’économiser les ressources nécessaires à leur traitement.
La durée de vie d’une transaction impliquant une variable de table est simplement égale à celle d’une mise à jour effectuée sur cette variable de table. De ce fait, les variables de table nécessitent moins de ressources de verrouillage et de consignation.

Limitations et restrictions

Les variables de table n’ont pas de statistiques de distribution. Elles ne déclenchent aucune recompilation. Dans de nombreux cas, l’optimiseur crée un plan de requête en supposant que la variable de table n’a aucune ligne. Pour cette raison, soyez prudent lorsque vous utilisez une variable de table si vous attendez un nombre de lignes supérieur à 100. Les tables Temp peuvent être une meilleure solution dans ce cas. Pour les requêtes qui joignent la variable de table à d’autres tables, utilisez l’indicateur RECOMPILE afin que l’optimiseur utilise la cardinalité correcte pour la variable de table.

Les variables de table ne sont pas prises en charge dans le modèle de raisonnement basé sur les coûts de l’optimiseur SQL Server. Elles ne doivent donc pas être utilisées lorsque des choix basés sur les coûts sont requis pour obtenir un plan de requête efficace. Les tables temporaires sont préférables lorsque des tableaux basés sur les coûts sont obligatoires. Ce plan inclut en général les requêtes avec jointures, les décisions de parallélisme et les options de sélection d’index.

Les requêtes qui modifient des variables de table ne génèrent pas de plans d’exécution parallèle. Les performances peuvent être affectées quand des variables de table volumineuses ou des variables de table intégrées dans des requêtes complexes sont modifiées. À la place, envisagez d’utiliser des tables temporaires dans les cas où les variables de table sont modifiées. Pour plus d’informations, consultez CREATE TABLE (Transact-SQL). Il est toujours possible d’effectuer une mise en parallèle des requêtes qui lisent des variables de table sans les modifier.

Important

Le niveau de compatibilité de la base de données 150 améliore les performances des variables de table avec l’introduction de la compilation différée de variable de table. Pour plus d'informations, consultez Compilation différée de variable de table.

Il est impossible de créer explicitement des index sur des variables de table et aucune statistique n’est conservée sur les variables de table. À compter de SQL Server 2014 (12.x), une nouvelle syntaxe a été introduite, qui permet de créer certains types d’index inline avec la définition de table. Il est ainsi possible de créer des index sur des variables de table dans le cadre de la définition de la table. Dans certains cas, les performances peuvent s’améliorer en utilisant plutôt des tables temporaires, car elles assurent une prise en charge totale des index et fournissent des statistiques. Pour plus d’informations sur les tables temporaires et la création d’index inline, consultez CREATE TABLE (Transact-SQL).

Les contraintes CHECK, les valeurs DEFAULT et les colonnes calculées dans la déclaration de type de table ne peuvent pas appeler des fonctions définies par l’utilisateur. L’opération d’affectation entre des variables de table n’est pas prise en charge. Comme les variables de table ont une étendue limitée et ne font pas partie de la base de données persistante, les restaurations de transaction ne les affectent pas. Une fois créées, les variables de table ne sont plus modifiables.

Les variables de table ne peuvent pas être utilisées dans les instructions INSERT EXEC ou SELECT INTO.

Vous ne pouvez pas utiliser l’instruction EXEC ni la procédure stockée sp_executesql pour exécuter une requête SQL Server dynamique qui référence une variable de table si la variable de table a été créée en dehors de l’instruction EXEC ou de la procédure stockée sp_executesql. Étant donné que les variables de table peuvent être référencées uniquement dans leur étendue locale, une instruction EXEC et une procédure sp_executesql se trouveraient en dehors de l’étendue de la variable de table. En revanche, vous pouvez créer la variable de table et effectuer tous les traitements à l’intérieur de l’instruction EXEC ou de la procédure stockée sp_executesql, car l’étendue locale de la variable de table se trouve alors dans l’instruction EXEC ou la procédure stockée sp_executesql.

Une variable de table n’est pas une structure mémoire uniquement. Une variable de table peut stocker plus de données que ce qui peut être mis en mémoire. Elle doit donc disposer d’un emplacement sur le disque pour y stocker les données. Les variables de table sont créées dans la base de données tempdb comme les tables temporaires. Si la mémoire disponible est suffisante, les variables de table et les tables temporaires sont créées et traitées directement en mémoire (dans le cache de données).

Différences entre les variables de table et les tables temporaires

Le choix entre des variables de table et des tables temporaires dépend de ces facteurs :

Nombre de lignes insérées dans la table.
Nombre de recompilations à partir desquelles la requête est enregistrée.
Type des requêtes et leur dépendance vis-à-vis des index et des statistiques pour les performances.

Dans certains cas, il est préférable de diviser une procédure stockée avec des tables temporaires en plusieurs procédures stockées plus petites afin que la recompilation se fasse sur des unités plus petites.

En général, vous utilisez toujours des variables de table, sauf quand le volume de données est important et que la table est utilisée de manière répétée. Dans ce cas, vous pouvez créer des index sur la table temporaire pour améliorer les performances des requêtes. Toutefois, chaque scénario est unique. Microsoft vous recommande de tester si des variables de table sont ou non préférables à des tables temporaires pour une requête ou une procédure stockée particulière.

Exemples

R. Déclarer une variable de type table

L’exemple suivant crée une variable table qui stocke les valeurs définies dans la clause OUTPUT de l’instruction UPDATE. Deux instructions SELECT suivent qui retournent les valeurs dans @MyTableVar, ainsi que les résultats de la mise à jour dans la table Employee. Les résultats dans la colonne INSERTED.ModifiedDate sont différents des valeurs de la colonne ModifiedDate dans la table Employee. Ceci s’explique par le fait que le déclencheur AFTER UPDATE, qui met à jour la valeur de ModifiedDate en fonction de la date actuelle, est défini sur la table Employee. Toutefois, les colonnes renvoyées par OUTPUT reflètent les données avant l'activation des déclencheurs. Pour plus d’informations, consultez Clause OUTPUT (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. Créer une fonction table incluse

L'exemple suivant retourne une fonction table incluse. Pour chaque produit vendu au magasin, il retourne trois colonnes : ProductID, Name et la somme des totaux annuels par magasin sous YTD Total.

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

Pour appeler la fonction, exécutez la requête suivante :

SELECT * FROM Sales.ufn_SalesByStore (602);