By: Atif Shehzad | Frissítve: Atif Shehzad Hozzászólások (8) | Kapcsolódó: 2011-08-29 | Hozzászólások (8) | Kapcsolódó: 2011-08-29 | Hozzászólások (8) | Kapcsolódó: 2011-08-29 | Hozzászólások (8) | Kapcsolódó: 2011-08-29 T-SQL
Ingyenes MSSQLTips webinárium: SQL Server fejlesztésének legjobb gyakorlatai
Menjen el erre a webináriumra, és ismerje meg az SQL Server fejlesztésének legjobb gyakorlatait. Andy Warren megosztja sokéves tapasztalatát, hogy néhány támpontot adjon arról, mi vált be neki a legjobban, és hogyan hasznosíthatja Ön is e tudás egy részét.
Probléma
Egyszerű forgatókönyvekben használtam kiszámított oszlopokat, és ezek jól működnek. Néhány esetben azonban korlátozásokkal szembesülünk az üzleti logika kiszámított oszlopokon keresztül történő megvalósítása során. Például a számított értékek meghatározásához külön kifejezés alapján különböző értékekre van szükségünk. Emellett előfordulhat a nullával való osztás hibája, amelyet meg kell akadályozni. A számított oszlop táblázaton kívüli oszlopokhoz is hozzá kell férnünk a számított oszlop kifejezésben való felhasználáshoz. Ebben a tippben azt nézzük meg, hogyan lehet a számított oszlopokat rugalmasabbá tenni.
Megoldás
A számított oszlopok nagy rugalmasságot adhatnak az adatbázis-tervezésben. Lehetőség van a számítási kifejezés feltételes alkalmazására, a nullával osztott hiba kezelésére és a számított oszlop táblán kívüli bármely oszlop elérésére. Ez rugalmasabbá és praktikusabbá tenné a számított oszlopot az adatbázis-tervezésben. Az alábbiakban felsorolunk néhány olyan forgatókönyvet, amelyet ebben a tippben fogunk kezelni.
- Feltételes értékszámítás
- Nullával osztott hiba
- A kiszámított oszlop táblán kívüli oszlop elérése
A fenti problémák megoldásához egyszerű T-SQL technikákat fogunk használni. Ezzel demonstráljuk a kiszámított oszlopok rugalmasságát és képességét az ilyen problémák kezelésére.
Az értékek feltételes kiszámítása
Van egy egyszerű táblázatunk néhány, az alkalmazottakkal kapcsolatos oszloppal. Van egy számított oszlop is, amely kiszámítja a 60 éves korban történő nyugdíjba vonulást. Van egy új követelményünk, hogy a vezetők esetében a korhatárt 65 évre állítsuk be, de az összes többi alkalmazott esetében 60 év maradjon.
Ezt egy CASE utasítást használó feltételes képlet segítségével érhetjük el, ahogy az alábbi kódban látható. A DORetirement oszlop esetében, ha a megnevezés “Manager”, akkor a nyugdíjkorhatár 65 év, egyébként 60 év lesz.
-- 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
Ha a fenti kódot futtatja, akkor a következő kimenetet kell kapnia. A CASE utasítás használatával külön kifejezéseket definiálhatunk a megadott kritériumoktól függően.
Nullával osztott hiba
A számítási kifejezésben, ha osztunk, akkor előfordulhat, hogy a nevezőben nulla van. Ilyen esetekben fennáll annak a veszélye, hogy hibát kapunk a nullával való osztás miatt.
Megfontolunk egy olyan forgatókönyvet, ahol van egy AS (számláló/nevező) képletű számított oszlopunk, ahol a számláló és a nevező oszlopokat használjuk a számított oszlophoz . Ebben az esetben a nullával való osztás hibája lépne fel, amikor a számított oszlop kifejezése úgy kerül kiszámításra, hogy a nevező oszlopban nulla van.
Ezt a hibát elkerülhetjük, ha a nevezőben lévő nullát NULL értékkel helyettesítjük a NULLIF függvény segítségével, az alábbiak szerint. A negyedik rekord, amelyet beszúrunk a táblázatba, nullával való osztás problémáját okozná, de a NULLIF függvény ezt NULL értékké alakítja.
-- 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
Mivel nem lehet NULL-lal osztani, a visszaadott érték NULL erre az oszlopra, erre az egy rekordra.
Egy számított oszlop alapértelmezés szerint NULL értékeket enged meg. Kifejezetten NOT NULL értéket azonban csak a perszisztens számított oszlopok esetében adhat meg.
Elérés a számított oszlop táblázatán kívüli oszlophoz
A számított oszlop nem érhet el közvetlenül a táblázatán kívüli oszlopot. Ezt a korlátozást egy felhasználó által definiált függvény használatával lehet áthidalni. Egy UDF használható a kifejezésben a számított oszlop táblán kívüli bármely oszlop eléréséhez.
A lenti szkriptben egy UDF-et hozunk létre a munkavállaló szabadságegyenlegének kiszámításához, hogy megmutassuk, hány nap szabadsága van még hátra a munkavállalónak a maximális 20 napon túl. Ez az adat a LeaveBalance nevű másodlagos táblából származik.
--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
Az alábbiakban tehát láthatjuk, hogy az egyes alkalmazottaknak hány maradék napjuk van még.
Az UDF-ekkel kapcsolatban van néhány megfontolás, amikor egy kiszámított oszlopot indexben kell használni. Erről bővebben ebben a tippben olvashat: Hogyan hozzunk létre indexeket számított oszlopokra az SQL Serverben.
Következő lépések
A fent említett problémák és azok megoldásai ugyanúgy működnek a perszisztens vagy nem perszisztens számított oszlopok esetében. A kiszámított oszlopok rendelkezésre álló rugalmasságának kihasználása javíthatja az adatbázis-tervezést. A számított oszlopok jó lehetőség az üzleti logika kifejezéseken keresztül történő megvalósítására.
- A számított oszlopokkal való munka alapjairól szóló tipp elolvasásához kattintson ide
- A számított oszlopokkal való munka alapjairól szóló tipp elolvasásához kattintson ide
A legutóbbi frissítés:
A szerzőről
Atif Shehzad szenvedélyes SQL Server DBA, műszaki lektor és cikkíró.Minden tippem megtekintése
- Még több adatbázis-fejlesztői tipp…