Partager via

Spécifier des colonnes calculées dans une table

Une colonne calculée est une colonne virtuelle qui n’est pas physiquement stockée dans la table, sauf si la colonne est marquée PERSISTED. Une expression de colonne calculée peut utiliser des données d'autres colonnes afin de calculer une valeur pour la colonne à laquelle elle appartient. Vous pouvez spécifier une expression pour une colonne calculée dans SQL Server 2014 à l’aide de SQL Server Management Studio ou de Transact-SQL.

Dans cette rubrique

Avant de commencer

Limitations et restrictions

  • Une colonne calculée ne peut pas être utilisée comme définition de contrainte DEFAULT ou FOREIGN KEY ou avec une définition de contrainte NOT NULL. Toutefois, si la valeur de colonne calculée est définie par une expression déterministe et que le type de données du résultat est autorisé dans les colonnes d’index, une colonne calculée peut être utilisée comme colonne clé dans un index ou dans le cadre d’une contrainte PRIMARY KEY ou UNIQUE. Par exemple, si la table a des colonnes entières a et b, la colonne calculée a + b peut être indexée, mais que la colonne calculée a + DATEPART(dd, GETDATE()) ne peut pas être indexée, car la valeur peut changer dans les appels suivants.

  • Une colonne calculée ne peut pas être la cible d’une instruction INSERT ou UPDATE.

Sécurité

Autorisations

Nécessite l’autorisation ALTER sur la table.

Utilisation de SQL Server Management Studio

Pour ajouter une nouvelle colonne calculée

  1. Dans l' Explorateur d'objets, développez la table à laquelle vous voulez ajouter une nouvelle colonne calculée. Cliquez avec le bouton droit sur Colonnes et sélectionnez Nouvelle colonne.

  2. Entrez le nom de colonne et acceptez le type de données par défaut (nchar(10)). Le moteur de base de données détermine le type de données de la colonne calculée en appliquant les règles de priorité des types de données aux expressions spécifiées dans la formule. Par exemple, si la formule fait référence à une colonne de type money et à une colonne de type int, la colonne calculée est de type money , car ce type de données a la priorité la plus élevée. Pour plus d’informations, consultez Priorités des types de données (Transact-SQL).

  3. Dans l'onglet Propriétés des colonnes , développez la propriété Spécification de la colonne calculée .

  4. Dans la propriété enfant (Formule) , entrez l’expression pour cette colonne dans la cellule de grille située à droite. Par exemple, dans une colonne SalesTotal , la formule que vous écrivez peut être SubTotal+TaxAmt+Freight, qui ajoute la valeur dans ces colonnes pour chaque ligne de la table.

    Important

    Lorsqu'une formule combine deux expressions de type de données différents, les règles de priorité des types de données spécifient que le type ayant une priorité plus faible est converti dans un type ayant une priorité plus élevée. Si la conversion n’est pas une conversion implicite prise en charge, l’erreur «Error validating the formula for column column_name. » est retournée. Utilisez la fonction CAST ou CONVERT pour résoudre le conflit de type de données. Par exemple, si une colonne de type nvarchar est combinée à une colonne de type, le type intentier doit être converti nvarchar comme indiqué dans cette formule ('Prod'+CONVERT(nvarchar(23),ProductID)). Pour plus d’informations, consultez CAST et CONVERT (Transact-SQL).

  5. Indiquez si les données sont conservées en choisissant Oui ou Non dans la liste déroulante de la propriété Enfant Persistante .

  6. Dans le menu Fichier, cliquez sur Enregistrernom de la table.

Pour ajouter une définition de colonne calculée à une colonne existante

  1. Dans l’ Explorateur d’objets, cliquez avec le bouton droit sur la table contenant la colonne à modifier et développez Colonnes .

  2. Cliquez avec le bouton droit sur la colonne pour laquelle vous souhaitez spécifier une formule de colonne calculée, puis cliquez sur Supprimer. Cliquez sur OK.

  3. Ajoutez une nouvelle colonne et spécifiez la formule de colonne calculée en suivant la procédure précédente pour ajouter une nouvelle colonne calculée.

Utilisation de Transact-SQL

Pour ajouter une colonne calculée lors de la création d’une table

  1. Connectez-vous au moteur de base de données.

  2. Dans la barre d'outils standard, cliquez sur Nouvelle requête.

  3. Copiez et collez l'exemple suivant dans la fenêtre de requête, puis cliquez sur Exécuter. L’exemple crée une table avec une colonne calculée qui multiplie la valeur dans la QtyAvailable colonne fois la valeur dans la UnitPrice colonne.

    CREATE TABLE dbo.Products   
(  
    ProductID int IDENTITY (1,1) NOT NULL  
  , QtyAvailable smallint  
  , UnitPrice money  
  , InventoryValue AS QtyAvailable * UnitPrice  
);  

-- Insert values into the table.  
INSERT INTO dbo.Products (QtyAvailable, UnitPrice)  
VALUES (25, 2.00), (10, 1.5);  

-- Display the rows in the table.  
SELECT ProductID, QtyAvailable, UnitPrice, InventoryValue  
FROM dbo.Products;

Pour ajouter une nouvelle colonne calculée à une table existante

  1. Connectez-vous au moteur de base de données.

  2. Dans la barre d'outils standard, cliquez sur Nouvelle requête.

  3. Copiez et collez l'exemple suivant dans la fenêtre de requête, puis cliquez sur Exécuter. L'exemple suivant ajoute une nouvelle colonne à la table créée dans l'exemple précédent.

    ALTER TABLE dbo.Products ADD RetailValue AS (QtyAvailable * UnitPrice * 1.35);

Pour modifier une colonne existante en colonne calculée

  1. Connectez-vous au moteur de base de données.

  2. Dans la barre d'outils standard, cliquez sur Nouvelle requête.

  3. Pour modifier une colonne existante en colonne calculée, vous devez supprimer et recréer la colonne calculée. Copiez et collez l'exemple suivant dans la fenêtre de requête, puis cliquez sur Exécuter. L'exemple suivant modifie la colonne ajoutée dans l'exemple précédent.

    ALTER TABLE dbo.Products DROP COLUMN RetailValue;  
GO  
ALTER TABLE dbo.Products ADD RetailValue AS (QtyAvailable * UnitPrice * 1.5);

    Pour plus d’informations, consultez ALTER TABLE (Transact-SQL).

  • Last updated on