Partager via

table (Transact-SQL)

  • Article

Type de données spécial qui peut être utilisé pour stocker un jeu de résultats afin de le traiter ultérieurement. table est principalement utilisé pour le stockage temporaire d'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 pour être de type table. Les variables table peuvent être utilisées dans les fonctions, les procédures stockées et les traitements. Pour déclarer des variables de type table, utilisez DECLARE @local\_variable.

Icône Lien de rubrique Conventions de la syntaxe de 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 ) 
     }

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é de paramètres régionaux Microsoft 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 par défaut 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.

Remarques d'ordre général

Les variables table peuvent être référencées par leur 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 table peuvent être référencées en utilisant un alias, tel que le montre l'exemple suivant :

SELECT EmployeeID, DepartmentID 
FROM @MyTableVar m
JOIN Employee on (m.EmployeeID =Employee.EmployeeID AND
   m.DepartmentID = Employee.DepartmentID);

Les variables table offrent les avantages suivants pour les requêtes à petite échelle dont les plans ne changent pas et où les problèmes de recompilation sont dominants :

  • Une variable de type table se comporte comme une variable locale. Elle possède une étendue bien définie. Il s'agit de la fonction, procédure stockée ou traitement dans lequel elle est déclarée.

    Dans les limites de son étendue, une variable de type 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, une variable table ne peut pas être utilisée dans les instructions suivantes :

    SELECT select_list INTO table_variable;

    Les variables 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 table utilisées dans les procédures stockées provoquent moins de recompilations de procédures stockées par rapport aux tables temporaires utilisées lorsqu'il n'y a aucun choix basé sur les coûts qui affecte les performances.

  • La durée de vie d'une transaction impliquant une variable table est simplement égale à celle d'une mise à jour effectuée sur cette variable. De ce fait, les variables table requièrent moins de ressources de verrouillage et de consignation.

Limitations et restrictions

Les variables Table n'ont pas de statistiques de distribution et ne déclencheront pas de recompilations. Par conséquent, dans de nombreux cas, l'optimiseur va créer un plan de requête en supposant que la variable de table n'a pas de lignes. 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. Sinon, pour les requêtes qui joignent la variable de table à d'autres tables, utilisez l'indicateur RECOMPILE, qui contraint l'optimiseur à utiliser la cardinalité correcte pour la variable de table.

Les variables table ne sont pas prises en charge dans le modèle de raisonnement basé sur les coûts de l'optimiseur SQL Server. Par conséquent, elles ne doivent pas être utilisées lorsque des tableaux 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. Cela 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èles. Les performances peuvent être affectées lorsque des variables de table de grande taille ou des variables de table figurant dans des requêtes complexes sont modifiées. Dans ces situations, envisagez d'utiliser des tables temporaires à la place. 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.

Il est impossible de créer explicitement des index sur des variables de table, et aucune statistique concernant ces variables n'est calculée. Dans ces situations, les performances peuvent s'améliorer en utilisant les tables temporaires à la place, car elles prennent en charge les index et les statistiques. Pour plus d'informations sur les tables temporaires, consultez CREATE TABLE (Transact-SQL).

Les contraintes CHECK, les valeurs DEFAULT et les colonnes calculées dans la déclaration de type table ne peuvent pas appeler des fonctions définies par l'utilisateur.

L'opération d'affectation entre les variables table n'est pas prise en charge.

Étant donné que les variables table ont une portée limitée et qu'elles ne font pas partie de la base de données persistante, elles ne sont pas affectées par les annulations de transaction.

Les variables de table ne peuvent pas être modifiées après la création.

Exemples

A.Déclaration d'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 ; elles retournent les valeurs dans @MyTableVar, ainsi que les résultats de la mise à jour dans la table Employee. Notez que 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 retournées par OUTPUT reflètent les données avant l'activation des déclencheurs. Pour plus d'informations, consultez Clause OUTPUT (Transact-SQL).

USE AdventureWorks2012;
GO
DECLARE @MyTableVar table(
    EmpID int NOT NULL,
    OldVacationHours int,
    NewVacationHours int,
    ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
    ModifiedDate = GETDATE() 
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.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

B.Création d'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 l'agrégation des totaux annuels par magasin sous YTD Total .

USE AdventureWorks2012;
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 
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    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);

Voir aussi

Référence

COLLATE (Transact-SQL)

CREATE FUNCTION (Transact-SQL)

CREATE TABLE (Transact-SQL)

DECLARE @local\_variable (Transact-SQL)

Indicateurs de requête (Transact-SQL)

Concepts

Fonctions définies par l'utilisateur

Utiliser les paramètres table (Moteur de base de données)