Partager via

Spécifier les colonnes calculées dans une table

  • Article

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

Une colonne calculée est une colonne virtuelle qui n’est pas stockée physiquement dans la table, à moins que la colonne ne soit indiquée comme 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 à l'aide de SQL Server Management Studio (SSMS) ou de Transact-SQL (T-SQL).

Limites

  • Une colonne calculée ne peut pas être utilisée en tant que définition de contrainte DEFAULT ou FOREIGN KEY ou avec une définition de contrainte NOT NULL. Toutefois, si sa valeur 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, elle peut être utilisée en tant que colonne clé dans un index ou composante d'une contrainte PRIMARY KEY ou UNIQUE quelconque.

    Par exemple, si la table possède les colonnes de type entier a et b, une colonne calculée définie comme a + b peut être indexée, contrairement à la colonne calculée définie comme a + DATEPART(dd, GETDATE()) dont la valeur est susceptible d’évoluer au fil des appels.

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

  • SET QUOTED_IDENTIFIER doit avoir la valeur ON lors de la création ou de la modification d’index dans des colonnes calculées ou des vues indexées. Pour plus d’informations, consultez SET QUOTED_IDENTIFIER (Transact-SQL).

Autorisations

Requiert une autorisation ALTER sur la table.

Utiliser SQL Server Management Studio

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 la 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 prise en charge en tant que conversion implicite, 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 associée à une colonne de type int, le type entier doit être converti en 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 doivent être enregistrées en choisissant Oui ou Non dans la liste déroulante de la propriété enfant Is Persisted.

  6. Dans le menu Fichier, sélectionnez Enregistrer nom_table.

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 dans laquelle vous voulez spécifier une formule de colonne calculée, puis sélectionnez 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.

Utiliser Transact-SQL

Ajouter une colonne calculée lors de la création d'une table

L'exemple suivant crée une table avec une colonne calculée qui multiplie la valeur de la colonne QtyAvailable par la valeur de la colonne UnitPrice.

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;

-- Update values in the table.
UPDATE dbo.Products 
SET UnitPrice = 2.5
WHERE ProductID = 1;

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

Ajouter une nouvelle colonne calculée dans une table existante

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.5);

Ajoutez éventuellement l’argument PERSISTED pour stocker physiquement les valeurs calculées dans la table :

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

Modifier une colonne existante en une colonne calculée

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);
GO

Contenu connexe