Par : Atif Shehzad | Mis à jour : 2011-08-29 | Commentaires (8) | Connexe : Plus de > T-SQL
Webinaire gratuit MSSQLTips : Meilleures pratiques de développement pour SQL Server
Assistez à ce webinaire pour découvrir les meilleures pratiques de développement pour SQL Server. Andy Warren partagera ses nombreuses années d’expérience pour donner quelques conseils sur ce qui a le mieux fonctionné pour lui et comment vous pouvez utiliser certaines de ces connaissances.
Problème
J’ai utilisé des colonnes calculées dans des scénarios simples et celles-ci fonctionnent bien. Cependant, dans certains cas, nous sommes confrontés à des limitations lors de la mise en œuvre de la logique d’entreprise à travers les colonnes calculées. Par exemple, nous sommes tenus d’avoir différentes valeurs basées sur une expression distincte pour déterminer les valeurs calculées. De plus, il est possible qu’une erreur de division par zéro se produise, ce qui doit être évité. Nous devons également accéder à des colonnes en dehors du tableau des colonnes calculées pour les utiliser dans l’expression de la colonne calculée. Dans cette astuce, nous examinons comment rendre les colonnes calculées plus flexibles.
Solution
Les colonnes calculées peuvent ajouter une grande flexibilité dans la conception des bases de données. Il est possible d’appliquer conditionnellement l’expression calculée, en gérant l’erreur divisée par zéro et en accédant à n’importe quelle colonne en dehors de la table de la colonne calculée. Cela rend la colonne calculée plus flexible et plus pratique pour la conception de votre base de données. Voici la liste de quelques scénarios que nous allons traiter dans cette astuce.
- Calcul conditionnel de valeurs
- Erreur de division par zéro
- Accès à une colonne en dehors de la table de la colonne calculée
Nous utiliserons des techniques T-SQL simples pour résoudre les problèmes mentionnés ci-dessus. Cela démontrera la flexibilité et la capacité des colonnes calculées à gérer tous ces problèmes.
Computation conditionnelle des valeurs
Nous avons une table simple avec quelques colonnes liées aux employés. Il y a aussi une colonne calculée qui calcule la retraite à 60 ans. Nous avons une nouvelle exigence pour fixer la limite d’âge à 65 ans pour les gestionnaires, mais la maintenir à 60 ans pour tous les autres employés.
Nous pouvons accomplir cela en utilisant une formule conditionnelle à l’aide d’une instruction CASE comme indiqué dans le code suivant. Pour la colonne DORetirement si la désignation est « Manager » alors la retraite sera de 65 sinon elle sera de 60.
-- Script# 1: Computed column with conditional formula-- Use sample databaseUSE GO -- Create Table with computed columnIF OBJECT_ID('CCtest', 'U') IS NOT NULL DROP TABLE .GO CREATE TABLE . ( INT NOT NULL, VARCHAR(50) NOT NULL, DATETIME NOT NULL, AS CASE WHEN designation = 'Manager' THEN (DATEADD(YEAR,(65),)) ELSE (DATEADD(YEAR,(60),)) END)GO --Insert sample data INSERT INTO CCTest (empNumb, Designation, DOBirth) SELECT 84, 'DBA', '1985-12-13' UNION ALLSELECT 85, 'DBA', '1980-11-18' UNION ALLSELECT 86, 'Manager', '1978-01-19' UNION ALLSELECT 88, 'Manager', '1985-12-13' UNION ALLSELECT 90, 'Developer', '1975-07-23' GO -- Check the required functionality in resultSELECT Designation, datediff(yy,dobirth,doretirement ) AgeLimit, DOBirth, DORetirement FROM CCTestGO
Si vous exécutez le code ci-dessus vous devriez obtenir une sortie comme la suivante. En utilisant une instruction CASE, nous pouvons définir des expressions distinctes en fonction des critères fournis.
Erreur de division par zéro
Dans une expression de calcul, si nous divisons alors il peut y avoir une chance d’avoir un zéro dans le dénominateur. Dans de tels cas, nous courons le risque d’avoir une erreur à cause d’une division par zéro.
Envisageons un scénario où nous avons une colonne calculée avec la formule AS (numérateur/dénominateur) où les colonnes numérateur et dénominateur sont utilisées pour la colonne calculée . Dans ce cas, une erreur de division par zéro serait rencontrée chaque fois que l’expression de la colonne calculée est calculée avec un zéro dans la colonne du dénominateur.
Nous pouvons éviter cette erreur en remplaçant le zéro du dénominateur par une valeur NULL en utilisant la fonction NULLIF comme indiqué ci-dessous. Le quatrième enregistrement que nous insérons dans la table causerait un problème de division par zéro, mais la fonction NULLIF le convertit en un NULL.
-- Script# 2: Avoiding divided by zero error-- Use sample databaseUSE GO -- Create Table with computed columnIF OBJECT_ID('CCtest', 'U') IS NOT NULL DROP TABLE CCtestGOCREATE TABLE . ( int NOT NULL, int NOT NULL, AS (Numerator/NULLIF(Denominator,0)) )GO--Insert sample dataINSERT INTO CCTest (Numerator, Denominator) SELECT 840, 12 UNION ALLSELECT 805, 6 UNION ALLSELECT 846, 3 UNION ALLSELECT 88, 0 UNION ALLSELECT 90, 15GO-- Check the resultSELECT * from CCTestGO
Puisque vous ne pouvez pas diviser par NULL la valeur retournée est NULL pour cette colonne pour ce seul enregistrement.
Par défaut une colonne calculée autorisera les valeurs NULL. Cependant, vous pouvez spécifier explicitement NOT NULL avec les colonnes calculées persistées uniquement.
Accéder à une colonne en dehors de la table de la colonne calculée
Une colonne calculée ne peut pas accéder directement à une colonne en dehors de sa table. Cette limitation peut être surmontée en utilisant une fonction définie par l’utilisateur. Une UDF peut être utilisée dans l’expression pour accéder à toute colonne en dehors du tableau de la colonne calculée.
Dans le script ci-dessous, une UDF est créée pour calculer le solde de congés de l’employé afin de montrer combien de jours de congés restants un employé a au-delà du maximum de 20 jours. Ces données proviennent d’une table secondaire nommée LeaveBalance.
--Script # 3: Use UDF to access column in other table-- Use sample databaseUSE GO -- Create Table to reference in UDFIF OBJECT_ID('LeaveBalance', 'U') IS NOT NULL DROP TABLE LeaveBalanceGOCREATE TABLE . ( INT NOT NULL, TINYINT NOT NULL, )GO--Insert sample dataINSERT INTO LeaveBalanceSELECT 840, 12 UNION ALLSELECT 805, 6 UNION ALLSELECT 846, 13 UNION ALLSELECT 88, 7 UNION ALLSELECT 90, 15GO-- Create UDF to get leave balanceIF OBJECT_ID('UDF_GetLeaveBalance', 'FN') IS NOT NULL DROP FUNCTION UDF_GetLeaveBalanceGO-- Create UDF to use in computed columnCREATE FUNCTION UDF_GetLeaveBalance (@EmpNumb int)RETURNS TINYINTASBEGIN DECLARE @LeaveBalance TINYINT SELECT @LeaveBalance = (20 - LeavesAvailed) FROM LeaveBalance WHERE EmpNumb = @empnumb RETURN @LeaveBalanceENDGO-- Create Table to use computed columnIF OBJECT_ID('CCTest', 'U') IS NOT NULL DROP TABLE CCtestGOCREATE TABLE . ( INT NOT NULL, VARCHAR(50) NOT NULL, AS (.UDF_GetLeaveBalance(EmpNumb)) )GO--Insert sample dataINSERT INTO CCTest (EmpNumb, Designation) SELECT 840, 'DBA' UNION ALLSELECT 805, 'DBA' UNION ALLSELECT 846, 'Manager' UNION ALLSELECT 88, 'Manager' UNION ALLSELECT 90, 'Developer' GO-- Check the resultSELECT * from CCTestGO
Donc, ci-dessous, nous pouvons voir le nombre de jours restants disponibles pour chaque employé.
Il y a quelques considérations liées aux UDF lorsqu’une colonne calculée doit être utilisée dans un index. Vous pouvez en savoir plus à ce sujet dans cette astuce : Comment créer des index sur des colonnes calculées dans SQL Server.
Prochaines étapes
Les problèmes mentionnés ci-dessus et leurs solutions fonctionnent de la même manière pour les colonnes calculées persistantes ou non persistantes. L’utilisation de la flexibilité disponible des colonnes calculées peut améliorer la conception de votre base de données. Les colonnes calculées sont une bonne option à utiliser pour mettre en œuvre la logique métier par le biais d’expressions.
- Cliquez ici pour lire une astuce sur les bases du travail avec les colonnes calculées
- Cliquez ici pour lire une astuce sur la création d’index sur les colonnes calculées
Dernière mise à jour : 2011-08-29
A propos de l’auteur
Atif Shehzad est un DBA SQL Server passionné, un réviseur technique et un auteur d’articles.Voir tous mes conseils
- Plus de conseils pour les développeurs de bases de données…
.