table (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.

Meilleures pratiques

N'utilisez pas de variables de table pour stocker de grandes quantités de données (plus de 100 lignes). Les choix de plan risquent de ne pas être optimaux ou stables lorsqu'une variable de table contient une grande quantité de données. Envisagez de réécrire de telles requêtes de façon à ce qu'elles utilisent des tables temporaires ou utilisez l'indicateur de requête USE PLAN pour veiller à ce l'optimiseur utilise un plan de requête existant qui fonctionne bien pour votre scénario.

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 présentent les avantages suivants pour les requêtes à petite échelle qui ont des plans de requête qui ne changent pas et lorsque 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 des procédures stockées, provoquent moins de recompilations de procédures stockées que lorsque des tables temporaires sont utilisées s'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 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 choix basés sur les coûts sont nécessaires pour parvenir à un plan de requête efficace. Il est préférable d'utiliser des tables temporaires lorsque des choix basés sur les coûts sont nécessaires. Cela inclut en général les requêtes avec jointures, les décisions en matière de parallélisme et les choix de sélection de l'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 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.

Exemples

A. Déclaration d'une variable de type table

USE AdventureWorks2008R2;
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

USE AdventureWorks2008R2;
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

SELECT * FROM Sales.ufn_SalesByStore (602);

Voir aussi

Référence

Concepts