Introduction
Les colonnes calculées sont juste des colonnes virtuelles dont le contenu est le résultat d’une expression. Généralement, elles sont utilisées pour contenir des données basées sur le reste des colonnes de la table. L’expression peut contenir d’autres colonnes non calculées de la table, des constantes, des opérateurs et des fonctions, mais vous ne pouvez pas spécifier une requête comme expression pour une colonne calculée.
Comme ce sont des colonnes « virtuelles », elles ne sont pas stockées sur le disque comme le reste de la table. En fait, elles ne sont pas stockées mais calculées chaque fois que la colonne est accédée dans une requête. Comme vous le verrez, vous pouvez forcer SQL Server à stocker (« persister ») la colonne dans la table avec quelques restrictions.
La meilleure façon de comprendre le fonctionnement des colonnes calculées est d’utiliser des échantillons. Vous trouverez à la fin un fichier contenant tous les scripts utilisés dans l’article, et nous vous en montrerons quelques-uns dans le texte pour illustrer les explications. Pour commencer, nous allons créer deux tables : la première pour contenir les informations sur les factures et l’autre avec les lignes de détail de ces factures. Vous trouverez également quelques insertions dans le fichier script pour créer des données types.
CREATE TABLE invoices( id_invoice INT PRIMARY KEY IDENTITY , customer_name VARCHAR(25));CREATE TABLE detail_lines( id_detail INT PRIMARY KEY IDENTITY , id_invoice_detail INT , product VARCHAR(30) , unit_price MONEY , quantity INT , FOREIGN KEY (id_invoice_detail) REFERENCES invoices (id_invoice));
La façon de créer une colonne calculée est la même que celle utilisée pour créer d’autres colonnes dans une table, avec une instruction CREATE TABLE ou ALTER TABLE. Pour une colonne calculée, on remplace le type de données de la colonne par l’expression qui sera utilisée pour obtenir le contenu de la colonne. La syntaxe sera le nom de la colonne, suivi du mot clé « as », puis de l’expression. Créons une colonne calculée dans la table detail_line pour stocker le montant total de la ligne, que nous calculons en multipliant unit_price et quantity.
ALTER TABLE detail_lines ADD total_amount AS unit_price * quantity;
Vérifier une colonne calculée
Il existe plusieurs façons de confirmer qu’une colonne est réellement une colonne calculée. L’une d’entre elles consiste à utiliser la fonction columnproperty() en spécifiant la propriété « IsComputed ».
SELECT COLUMNPROPERTY(OBJECT_ID('dbo.detail_lines'),'total_amount','IsComputed')
Une autre façon d’obtenir des informations sur les colonnes calculées est à travers la vue système, sys.computed_columns. Cette vue est une extension de la vue sys.columns. Cela signifie que sys.computed_columns hérite de toutes les colonnes de la vue sys.columns et en ajoute également d’autres qui sont spécifiques à ce type de colonne. Tout au long de l’article, nous verrons certaines des colonnes de cette vue, au fur et à mesure que nous verrons différentes caractéristiques des colonnes calculées. Pour l’instant, il suffit de savoir que cette vue ne montre que les colonnes calculées et possède une colonne, nommée is_computed, qui indique si la colonne est calculée ou non. Évidemment, tous les enregistrements de cette vue auront un un dans cette colonne.
SELECT name, is_computed FROM sys.computed_columns;
Comme le contenu de la colonne est calculé chaque fois que la colonne est référencée dans une requête, le contenu est toujours mis à jour. Tout changement dans les colonnes qui sont incluses dans l’expression, est automatiquement reflété dans la valeur de la colonne. Nous pouvons le constater en modifiant la quantité dans un registre de la table detail_lines de l’échantillon et vérifier le résultat.
UPDATE detail_lines SET quantity = 4 WHERE product = 'Cup'SELECT product, unit_price, quantity, total_amount FROM detail_lines WHERE product = 'Cup'
Un pas en avant
Ce que nous avons vu jusqu’à présent avec les colonnes calculées est très basique puisqu’il s’agit seulement d’effectuer des calculs avec d’autres colonnes. Cependant, l’expression des colonnes calculées peut contenir des fonctions, aussi bien des fonctions standard T-SQL que des fonctions définies par l’utilisateur (UDF). De cette façon, il est possible d’étendre la fonctionnalité de ces colonnes beaucoup plus loin.
Voyons un exemple de ceci. Nous allons ajouter une colonne calculée à la table des factures qui calcule le montant total de la facture. Pour ce faire, nous devons obtenir le numéro de la facture et interroger la table detail_lines pour additionner le total_mount de chaque enregistrement avec cet id de facture. La meilleure façon de le faire est d’utiliser une fonction qui reçoit l’identifiant de la facture comme paramètre et renvoie la somme. Après cela, nous devons créer la colonne qui utilise cette fonction.
CREATE FUNCTION fn_total_invoice (@invoice_number INT)RETURNS MONEYASBEGIN DECLARE @total MONEY SELECT @total=SUM(total_amount) FROM detail_lines WHERE id_invoice_detail = @invoice_number RETURN @totalENDALTER TABLE invoices ADD total_invoice AS dbo.fn_total_invoice(id_invoice)
Nous pouvons vérifier que cette colonne fonctionne correctement en ajoutant un nouvel enregistrement dans la table, detail_lines, donc le total_facture devrait changer.
INSERT INTO detail_lines (id_invoice_detail,product,unit_price, quantity) VALUES (2,'Cup',9.90,1)SELECT id_invoice, customer_name, total_invoice FROM invoices WHERE id_invoice=2
Modifier la colonne
Il pourrait y avoir des situations où vous devez modifier une colonne calculée. Malheureusement, cela n’est pas possible. Pour effectuer cette modification, il faut supprimer la colonne et la recréer avec la nouvelle expression.
Dans le cas où la colonne calculée utilise une fonction externe, nous ne serons pas autorisés à modifier cette fonction. Si nous essayons, nous recevons une erreur indiquant que cette fonction est liée à la table. Pour modifier la fonction, il est nécessaire de supprimer la colonne, d’effectuer la modification de la fonction, et enfin, de recréer la colonne avec la nouvelle version de la fonction.
Nous pouvons obtenir la définition de la colonne à la colonne « définition » de la vue sys.computed_columns.
SEECT name, definition FROM sys.computed_columns
Stockage d’une colonne calculée
Comme nous l’avons mentionné précédemment, ces colonnes sont « virtuelles », elles ne sont donc pas stockées physiquement dans la table. Cependant, il existe la possibilité de forcer le calcul à être physiquement stocké dans la table, ce qui est appelé « persist « er la colonne. Cela peut améliorer les performances avec les instructions SELECT puisque cela évite d’avoir à effectuer le calcul de la colonne à chaque fois qu’elle est référencée.
De plus, afin de persister la colonne, l’expression utilisée pour créer la colonne doit être une expression « déterministe ». Comme nous pouvons le voir sur le site de Microsoft, « les fonctions déterministes renvoient toujours le même résultat chaque fois qu’elles sont appelées avec un ensemble spécifique de valeurs d’entrée et étant donné le même état de la base de données. » (https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions?view=sql-server-2017). Si nous voulons savoir si SQL Server considère l’expression d’une colonne calculée comme déterministe ou non, nous pouvons utiliser la fonction columnproperty() avec la propriété « IsDeterministic ».
SELECT COLUMNPROPERTY(OBJECT_ID('dbo.detail_lines'),'total_amount','IsDeterministic')SELECT COLUMNPROPERTY(OBJECT_ID('dbo.invoices'),'total_invoice','IsDeterministic')
Si la définition de la colonne est une fonction définie par l’utilisateur, vous pouvez également vérifier si cette fonction elle-même est déterministe ou non. Pour ce faire, vous devez utiliser la fonction objectproperty() avec la propriété IsDeterministic.
SELECT OBJCETPROPERTY(OBJECT_ID('dbo.fn_total_invoice'),'IsDeterministic')
Comme vous pouvez le voir dans les requêtes, la colonne du premier exemple, dans laquelle nous calculons le prix total du détail, est considérée comme déterministe. Cependant, la fonction qui calcule le prix total de la facture est considérée comme non déterministe. Ainsi, seule la colonne total_prix de la table detail_table peut être stockée dans la table.
ALTER TABLE detail_lines DROP COLUMN total_amount;ALTER TABLE detail_lines ADD total_amount AS unit_price * quantity PERSISTED;
De nouveau dans la vue sys.computed_columns, vous pouvez voir le champ is_persisted, qui indiquera si la colonne est persistée ou non dans la table.
Index avec des colonnes calculées
Il est possible d’utiliser des colonnes calculées dans des index, bien qu’elles doivent répondre à plusieurs exigences :
- Propriété : Toutes les fonctions qui sont utilisées dans la définition de la colonne calculée doivent être la propriété du même utilisateur que la table.
- Déterminisme : La colonne calculée doit être déterministe. De plus, si la colonne contient des expressions CLR, en plus d’être déterministe, la colonne doit être persistée.
- Précision : L’expression de la colonne calculée doit être précise. Cela implique qu’elle ne peut pas être du type de données « float » ou « real ». Vous ne pouvez pas non plus utiliser ce type de données dans votre définition. Cette caractéristique peut être vérifiée avec la fonction columnproperty() en spécifiant la propriété IsPrecise.
- Type de données : La colonne calculée ne peut pas être de type text, ntext ou image. De plus, si l’expression contient des types de données image, ntext, text, varchar (max), nvarchar (max), varbinary (max) ou xml, elle ne peut être utilisée que si le type de données résultant de l’expression est autorisé dans un index.
En plus de ces considérations, les connexions utilisées pour créer la colonne et celle utilisée pour créer l’index doivent avoir certaines configurations afin d’effectuer ces actions.
La connexion pour créer la colonne calculée doit avoir l’option ANSI_NULLS active. Ceci peut être vérifié avec la fonction columnproperty(), en spécifiant la propriété IsAnsiNullsOn.
La connexion pour créer l’index est, ainsi que les connexions pour effectuer l’insertion, la mise à jour et la suppression des enregistrements qui influencent l’index doivent avoir les options ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER actives. En outre, l’option NUMERIC_ROUNDABORT doit être désactivée.
Dernières considérations
Pour terminer, nous allons passer en revue quelques aspects supplémentaires qu’il est nécessaire de connaître pour l’utilisation correcte des colonnes calculées.
Évidemment, les colonnes calculées ne peuvent pas être mises à jour, ni incluses dans la liste des valeurs d’une action INSERT. Bien que, les colonnes calculées peuvent faire partie de la liste des résultats d’une instruction select, elles peuvent également être utilisées dans les clauses WHERE, ORDER BY, ou dans toutes celles dans lesquelles une expression peut être mise.
SELECT product, unit_price, quantity, total_amount FROM detail_lines WHERE total_amount > 10 ORDER BY total_amount
Malgré ce qui précède, une colonne calculée ne peut pas être utilisée dans la définition d’une contrainte DEFAULT ou FOREIGN KEY. Elle ne peut pas non plus l’être avec une définition de contrainte NOT NULL.
En revanche, les colonnes calculées peuvent être utilisées dans le cadre de contraintes PRIMARY KEY ou UNIQUE. Pour ce faire, la définition de la colonne calculée doit être une expression déterministe.
Conclusion
L’utilisation de colonnes calculées peut être très utile dans certaines situations. Vous devez étudier soigneusement où les utiliser, en raison des restrictions qu’elles ont, spécialement pour créer l’index et les persister. Ceci n’est que le début. N’hésitez pas à essayer de nouvelles choses et à expérimenter avec les colonnes calculées pour trouver de nouvelles possibilités.