Problem

Jeg har brugt beregnede kolonner i enkle scenarier, og disse fungerer fint. Men i nogle tilfælde står vi over for begrænsninger, mens vi implementerer forretningslogik via beregnede kolonner. Vi skal f.eks. have forskellige værdier baseret på et separat udtryk for at bestemme de beregnede værdier. Der kan også være risiko for en fejl ved at dividere med nul, som skal forhindres. Vi skal også have adgang til kolonner uden for den beregnede kolonnetabel til brug i det beregnede kolonneudtryk. I dette tip ser vi på, hvordan vi kan gøre beregnede kolonner mere fleksible.

Løsning

Beregnede kolonner kan tilføje stor fleksibilitet i databasedesignet. Det er muligt at anvende det beregnede udtryk betinget, håndtere divideret med nul-fejl og få adgang til enhver kolonne uden for tabellen med den beregnede kolonne. Dette vil gøre den beregnede kolonne mere fleksibel og praktisk for dit databasedesign. Følgende er en liste over nogle scenarier, som vi vil håndtere i dette tip.

• Betinget beregning af værdier
• Divideret med nul-fejl
• Accessing a column outside the computed column table

Vi vil bruge enkle T-SQL-teknikker til at løse de ovennævnte problemer. Dette vil demonstrere fleksibiliteten og evnen af beregnede kolonner til at håndtere sådanne problemer.

Konditionel beregning af værdier

Vi har en simpel tabel med nogle kolonner relateret til medarbejdere. Der er også en beregnet kolonne, som beregner pensionering ved 60 års alderen. Vi har et nyt krav om at sætte aldersgrænsen til 65 år for ledere, men beholde den på 60 år for alle andre medarbejdere.

Vi kan opnå dette ved at bruge en betinget formel ved hjælp af en CASE-anvisning, som vist i følgende kode. For kolonnen DORetirement, hvis betegnelsen er “Manager”, vil pensionen være 65, ellers vil den være 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

Hvis du kører ovenstående kode, bør du få output som det følgende. Ved at bruge en CASE-anvisning kan vi definere separate udtryk afhængigt af de angivne kriterier.

Divideret med nul-fejl

I beregningsudtryk, hvis vi dividerer, kan der være en chance for at have et nul i nævneren. I sådanne tilfælde risikerer vi at få en fejl på grund af en divideret med nul.

Tænk på et scenarie, hvor vi har en beregnet kolonne med formlen AS (tæller/nævner), hvor tæller- og nævnerkolonnerne bruges til den beregnede kolonne . I dette tilfælde vil der opstå en fejl ved at dividere med nul, når udtrykket i den beregnede kolonne beregnes med et nul i nævnerkolonnen.

Vi kan undgå denne fejl ved at erstatte nullet i nævneren med en NULL-værdi ved hjælp af NULLIF-funktionen som vist nedenfor. Den fjerde post, som vi indsætter i tabellen, ville forårsage et problem med at dividere med nul, men NULLIF-funktionen konverterer denne til en NULL-værdi.

-- 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

Da du ikke kan dividere med NULL, er den returnerede værdi NULL for denne kolonne for denne ene post.

Som standard vil en beregnet kolonne tillade NULL-værdier. Du kan dog kun eksplicit angive NOT NULL med persisterede beregnede kolonner.

Accessing a column outside of the computed column table

En beregnet kolonne kan ikke få direkte adgang til en kolonne uden for dens tabel. Denne begrænsning kan overvindes ved at bruge en brugerdefineret funktion. En UDF kan bruges i udtrykket til at få adgang til enhver kolonne uden for den beregnede kolonnetabel.

I nedenstående script oprettes en UDF til at beregne medarbejderens feriesaldo for at vise, hvor mange resterende feriedage en medarbejder har ud over de maksimale 20 dage. Disse data kommer fra en sekundær tabel ved navn 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

Så nedenfor kan vi se antallet af resterende dage til rådighed for hver medarbejder.

Der er nogle overvejelser i forbindelse med UDF’er, når en beregnet kolonne skal bruges i et indeks. Du kan læse mere om dette i dette tip: Sådan oprettes indekser på beregnede kolonner i SQL Server.

Næste trin

De ovennævnte problemer og deres løsninger fungerer på samme måde for persisterede eller ikke persisterede beregnede kolonner. Udnyttelse af den tilgængelige fleksibilitet i beregnede kolonner kan forbedre dit databasedesign. Beregnede kolonner er en god mulighed for at bruge til at implementere forretningslogik gennem udtryk.

• Klik her for at læse tip om det grundlæggende i at arbejde med beregnede kolonner
• Klik her for at læse tip om at oprette indekser på beregnede kolonner

Sidst opdateret: 2011-08-29

Om forfatteren

Atif Shehzad er en passioneret SQL Server DBA, teknisk reviewer og artikelforfatter.
Se alle mine tips

Relaterede ressourcer

• Mere tips til databaseudviklere…