Podle: Atif Shehzad | Aktualizováno: 2011-08-29 | Komentáře (8) | Související: Více > T-SQL
Webinář MSSQLTips zdarma:
Zúčastněte se tohoto webináře, kde se dozvíte o nejlepších postupech pro vývoj SQL Serveru. Andy Warren se s vámi podělí o své dlouholeté zkušenosti a poradí vám, co se mu nejlépe osvědčilo a jak můžete některé z těchto znalostí využít.
Problém
Používám vypočtené sloupce v jednoduchých scénářích a ty fungují dobře. V některých případech však narážíme na omezení při implementaci obchodní logiky prostřednictvím vypočtených sloupců. Například požadujeme, aby na základě samostatného výrazu byly určeny různé hodnoty vypočtených hodnot. Také se může vyskytnout chyba dělení nulou, které je třeba zabránit. Požadujeme také přístup ke sloupcům mimo tabulku vypočtených sloupců pro použití ve výrazu vypočteného sloupce. V tomto tipu se podíváme na to, jak učinit vypočtené sloupce flexibilnějšími.
Řešení
Vypočtené sloupce mohou dodat velkou flexibilitu při návrhu databáze. Je možné podmíněně použít výpočtový výraz, ošetřit chybu děleno nulou a přistupovat k libovolnému sloupci mimo tabulku vypočteného sloupce. Díky tomu by byl vypočtený sloupec flexibilnější a praktičtější pro návrh databáze. Následuje seznam některých scénářů, které budeme v tomto tipu řešit.
- Podmíněný výpočet hodnot
- Chybu děleno nulou
- Přístup ke sloupci mimo tabulku vypočteného sloupce
K řešení výše uvedených problémů použijeme jednoduché techniky jazyka T-SQL. Tím si ukážeme flexibilitu a schopnost vypočtených sloupců řešit všechny podobné problémy.
Podmíněný výpočet hodnot
Máme jednoduchou tabulku s několika sloupci týkajícími se zaměstnanců. Je zde také vypočtený sloupec, který počítá odchod do důchodu v 60 letech. Máme nový požadavek nastavit věkovou hranici na 65 let pro vedoucí pracovníky, ale pro všechny ostatní zaměstnance ji ponechat na 60 letech.
Toho můžeme dosáhnout pomocí podmíněného vzorce s použitím příkazu CASE, jak ukazuje následující kód. Pro sloupec DORůchod, pokud je označení „Manažer“, pak bude odchod do důchodu 65, jinak bude 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
Pokud spustíte výše uvedený kód, měli byste získat následující výstup. Pomocí příkazu CASE můžeme definovat samostatné výrazy v závislosti na zadaných kritériích.
Chybné dělení nulou
Ve výpočetním výrazu, pokud dělíme, pak může existovat možnost, že ve jmenovateli bude nula. V takových případech se vystavujeme riziku chyby z důvodu dělení nulou.
Uvažujme scénář, kdy máme výpočtový sloupec se vzorcem AS (čitatel/jmenovatel), kde jsou pro výpočtový sloupec použity sloupce čitatele a jmenovatele . V tomto případě by se vyskytla chyba dělení nulou, kdykoli by byl vypočtený výraz sloupce vypočítán s nulou ve sloupci jmenovatele.
Této chybě se můžeme vyhnout nahrazením nuly ve jmenovateli hodnotou NULL pomocí funkce NULLIF, jak je uvedeno níže. Čtvrtý záznam, který vložíme do tabulky, by způsobil problém s dělením nulou, ale funkce NULLIF jej převede na 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
Protože nelze dělit NULL, je vrácená hodnota pro tento sloupec pro tento jeden záznam NULL.
Výpočtový sloupec standardně povoluje hodnoty NULL. Hodnotu NOT NULL však můžete explicitně zadat pouze u persistujících vypočtených sloupců.
Přístup ke sloupci mimo tabulku vypočtených sloupců
Vypočtený sloupec nemůže přímo přistupovat k žádnému sloupci mimo svou tabulku. Toto omezení lze překonat použitím funkce definované uživatelem. UDF lze použít ve výrazu pro přístup k libovolnému sloupci mimo tabulku vypočtených sloupců.
V níže uvedeném skriptu je vytvořena UDF pro výpočet zůstatku dovolené zaměstnance, která ukazuje, kolik dní dovolené zbývá zaměstnanci nad rámec maximálního počtu 20 dní. Tato data pocházejí ze sekundární tabulky s názvem 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
Níže tedy vidíme počet zbývajících dnů, které má každý zaměstnanec k dispozici.
Pokud má být vypočtený sloupec použit v indexu, existují určité úvahy týkající se UDF. Více se o tom dočtete v tomto tipu: Jak vytvářet indexy na vypočtené sloupce v SQL Serveru.
Další kroky
Výše uvedené problémy a jejich řešení fungují stejně pro persistované i nepersistované vypočtené sloupce. Využití dostupné flexibility vypočtených sloupců může zlepšit návrh databáze. Vypočtené sloupce jsou dobrou možností, jak implementovat obchodní logiku pomocí výrazů.
- Klikněte sem pro přečtení tipu o základech práce s vypočtenými sloupci
- Klikněte sem pro přečtení tipu o vytváření indexů na vypočtených sloupcích
Posledně aktualizováno: O autorovi
O autorovi
Atif Shehzad je vášnivý SQL Server DBA, technický recenzent a autor článků.Zobrazit všechny mé tipy
- Další tipy pro vývojáře databází…
.