By: Atif Shehzad | Uppdaterad: 2011-08-29 | Kommentarer (8) | Relaterad: Mer > T-SQL
Gratis MSSQLTips Webinar: Delta i det här webbseminariet för att lära dig mer om bästa praxis för utveckling av SQL Server
. Andy Warren kommer att dela med sig av sin mångåriga erfarenhet för att ge några tips om vad som har fungerat bäst för honom och hur du kan utnyttja en del av denna kunskap.
Problem
Jag har använt beräknade kolumner i enkla scenarier och dessa fungerar bra. Men i vissa fall möter vi begränsningar när vi implementerar affärslogik genom beräknade kolumner. Vi måste till exempel ha olika värden baserade på ett separat uttryck för att bestämma de beräknade värdena. Dessutom finns det kanske en risk för ett fel som dividerar med noll, vilket måste förhindras. Vi måste också få tillgång till kolumner utanför den beräknade kolumntabellen för att använda dem i uttrycket för den beräknade kolumnen. I det här tipset tittar vi på hur vi kan göra beräknade kolumner mer flexibla.
Lösning
Beräknade kolumner kan ge stor flexibilitet i databasdesignen. Det är möjligt att villkorligt tillämpa det beräknade uttrycket, hantera felet dividerat med noll och få tillgång till vilken kolumn som helst utanför tabellen för beräknad kolumn. Detta skulle göra den beräknade kolumnen mer flexibel och praktisk för din databasdesign. Nedan följer en lista över några scenarier som vi kommer att hantera i det här tipset.
- Konditionell beräkning av värden
- Dividerat med noll-fel
- Access till en kolumn utanför den beräknade kolumntabellen
Vi kommer att använda enkla T-SQL-tekniker för att lösa de ovan nämnda problemen. Detta kommer att visa flexibiliteten och förmågan hos beräknade kolumner att hantera alla sådana problem.
Konditionell beräkning av värden
Vi har en enkel tabell med några kolumner relaterade till anställda. Det finns också en beräknad kolumn som beräknar pensionering vid 60 års ålder. Vi har ett nytt krav på att sätta åldersgränsen till 65 år för chefer, men behålla den på 60 år för alla andra anställda.
Vi kan åstadkomma detta genom att använda en villkorlig formel med hjälp av ett CASE-statement som visas i följande kod. För kolumnen DORetirement om beteckningen är ”Manager” kommer pensioneringen att vara 65 annars kommer den att vara 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
Om du kör ovanstående kod bör du få ett resultat som ser ut på följande sätt. Genom att använda en CASE-angivelse kan vi definiera separata uttryck beroende på de angivna kriterierna.
Dividerat med nollfel
I beräkningsuttrycket kan det, om vi dividerar, finnas en chans att få en nolla i nämnaren. I sådana fall riskerar vi att få ett fel på grund av att vi dividerar med noll.
Tänk på ett scenario där vi har en beräknad kolumn med formeln AS (täljare/nämnare) där täljare och nämnare används för den beräknade kolumnen . I det här fallet skulle ett fel ”dividera med noll” uppstå när uttrycket i den beräknade kolumnen beräknas med en nolla i nämnarkolumnen.
Vi kan undvika detta fel genom att ersätta nollan i nämnaren med ett NULL-värde med hjälp av funktionen NULLIF enligt nedan. Den fjärde posten som vi lägger in i tabellen skulle orsaka ett problem med att dividera med noll, men NULLIF-funktionen omvandlar den till ett NULL-värde.
-- 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
Då det inte går att dividera med NULL är det returnerade värdet NULL för den här kolumnen för den här posten.
Som standard tillåter en beräknad kolumn NULL-värden. Du kan dock uttryckligen ange NOT NULL endast med persisterade beräknade kolumner.
Access till en kolumn utanför den beräknade kolumntabellen
En beräknad kolumn kan inte direkt komma åt någon kolumn utanför sin tabell. Denna begränsning kan övervinnas genom att använda en användardefinierad funktion. En UDF kan användas i uttrycket för att få tillgång till en kolumn utanför den beräknade kolumntabellen.
I skriptet nedan skapas en UDF för att beräkna den anställdes ledighetssaldo för att visa hur många återstående ledighetsdagar en anställd har utöver de maximala 20 dagarna. Uppgifterna kommer från en sekundär tabell som heter 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å nedan kan vi se hur många återstående dagar som finns tillgängliga för varje anställd.
Det finns vissa överväganden som rör UDF:er när en beräknad kolumn ska användas i ett index. Du kan läsa mer om detta i det här tipset: Hur man skapar index på beräknade kolumner i SQL Server.
Nästa steg
De ovan nämnda problemen och deras lösningar fungerar på samma sätt för persisted eller non persisted beräknade kolumner. Om du utnyttjar den tillgängliga flexibiliteten hos beräknade kolumner kan du förbättra din databasdesign. Beräknade kolumner är ett bra alternativ att använda för att implementera affärslogik genom uttryck.
- Klicka här för att läsa tipset om grunderna för att arbeta med beräknade kolumner
- Klicka här för att läsa tipset om att skapa index på beräknade kolumner
Sist uppdaterad: 2011-08-29
Om författaren
Atif Shehzad är en passionerad SQL Server DBA, teknisk granskare och artikelförfattare.Se alla mina tips
- Mer tips för databasutvecklare…