Probleem

Ik heb berekende kolommen gebruikt in eenvoudige scenario’s en deze werken prima. In sommige gevallen worden we echter geconfronteerd met beperkingen bij het implementeren van bedrijfslogica via berekende kolommen. We moeten bijvoorbeeld verschillende waarden hebben op basis van een aparte expressie om de berekende waarden te bepalen. Ook bestaat de kans op een “deel door nul” fout die voorkomen moet worden. We moeten ook toegang hebben tot kolommen buiten de berekende kolomtabel voor gebruik in de berekende kolomuitdrukking. In deze tip bekijken we hoe we gecalculeerde kolommen flexibeler kunnen maken.

Oplossing

Gecalculeerde kolommen kunnen een grote flexibiliteit toevoegen aan het database ontwerp. Het is mogelijk om de berekende uitdrukking conditioneel toe te passen, de fout gedeeld door nul af te handelen en toegang te krijgen tot elke kolom buiten de tabel van de berekende kolom. Dit zou de berekende kolom flexibeler en handiger maken voor uw database ontwerp. Hieronder volgt een lijst van enkele scenario’s die we in deze tip gaan behandelen.

• Conditional computation of values
• Divided by zero error
• Accessing a column outside the computed column table

We zullen eenvoudige T-SQL technieken gebruiken om de hierboven genoemde problemen op te lossen. Dit zal de flexibiliteit en het vermogen van berekende kolommen demonstreren om met dergelijke problemen om te gaan.

Conditional Computation of Values

We hebben een eenvoudige tabel met enkele kolommen die betrekking hebben op werknemers. Er is ook een berekende kolom die pensionering op 60-jarige leeftijd berekent. We hebben een nieuwe eis om de leeftijdsgrens op 65 te stellen voor managers, maar deze op 60 te houden voor alle andere werknemers.

We kunnen dit bereiken door een voorwaardelijke formule te gebruiken met behulp van een CASE-statement, zoals in de volgende code wordt getoond. Als de kolom DORetirement de naam “Manager” heeft, is het pensioen 65, anders 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

Als u de bovenstaande code uitvoert, krijgt u uitvoer zoals hieronder. Door een CASE-instructie te gebruiken, kunnen we afzonderlijke expressies definiëren, afhankelijk van de opgegeven criteria.

Divided by Zero Error

In een rekenkundige uitdrukking kan het voorkomen dat er een nul in de noemer staat als we delen. In dergelijke gevallen lopen we het risico een fout te krijgen als gevolg van een deling door nul.

Bedenk een scenario waarin we een berekende kolom hebben met formule AS (teller/noemer) waarbij teller- en noemerkolommen worden gebruikt voor de berekende kolom . In dit geval zou een fout bij delen door nul worden aangetroffen telkens wanneer de berekende kolomuitdrukking wordt berekend met een nul in de noemerkolom.

We kunnen deze fout vermijden door de nul in de noemer te vervangen door een NULL-waarde met behulp van de NULLIF-functie, zoals hieronder wordt getoond. Het vierde record dat we in de tabel invoegen, zou een deler door nul-probleem veroorzaken, maar de NULLIF-functie converteert dit naar een 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

Omdat u niet door NULL kunt delen, is de geretourneerde waarde voor deze kolom NULL voor deze ene record.

Bestandaard laat een berekende kolom NULL-waarden toe. U kunt echter expliciet NOT NULL opgeven voor uitsluitend persisted computed kolommen.

Toegang tot een kolom buiten de tabel met de berekende kolom

Een berekende kolom kan niet rechtstreeks toegang krijgen tot een kolom buiten zijn tabel. Deze beperking kan worden opgeheven door een door de gebruiker gedefinieerde functie te gebruiken. Een UDF kan in de expressie worden gebruikt om toegang te krijgen tot een kolom buiten de berekende kolomtabel.

In het onderstaande script wordt een UDF gemaakt om het verlofsaldo van een werknemer te berekenen om te laten zien hoeveel resterende verlofdagen een werknemer heeft na het maximum van 20 dagen. Deze gegevens zijn afkomstig uit een secundaire tabel met de naam 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

Dus hieronder zien we het aantal resterende dagen dat voor elke werknemer beschikbaar is.

Er zijn enkele overwegingen met betrekking tot UDF’s wanneer een berekende kolom moet worden gebruikt in een index. U kunt hier meer over lezen in deze tip: Indexen maken op berekende kolommen in SQL Server.

Volgende stappen

De hierboven genoemde problemen en hun oplossingen werken op dezelfde manier voor persisted of niet persisted computed kolommen. Gebruik maken van de beschikbare flexibiliteit van berekende kolommen kan uw database ontwerp verbeteren. Berekende kolommen zijn een goede optie om bedrijfslogica te implementeren door middel van expressies.

• Klik hier om een tip te lezen over de basisprincipes van het werken met berekende kolommen
• Klik hier om een tip te lezen over het maken van indexen op berekende kolommen

Laatst bijgewerkt: 2011-08-29

Over de auteur

Atif Shehzad is een gepassioneerd SQL Server DBA, technisch reviewer en auteur van artikelen.
Bekijk al mijn tips

Gerelateerde bronnen

• Meer tips voor database-ontwikkelaars…