De: Atif Shehzad | Actualizat: 2011-08-29 | Comentarii (8) | Related: Mai multe > T-SQL
Free MSSQLTips Webinar: Cele mai bune practici de dezvoltare pentru SQL Server
Asistați la acest webinar pentru a afla despre cele mai bune practici de dezvoltare pentru SQL Server. Andy Warren va împărtăși experiența sa de mulți ani pentru a oferi câteva indicii despre ceea ce a funcționat cel mai bine pentru el și cum puteți utiliza unele dintre aceste cunoștințe.
Problemă
Am folosit coloane calculate în scenarii simple și acestea funcționează bine. Cu toate acestea, în unele cazuri, ne confruntăm cu limitări în timpul implementării logicii de afaceri prin coloane calculate. De exemplu, ni se cere să avem valori diferite bazate pe o expresie separată pentru a determina valorile calculate. De asemenea, este posibil să apară o eroare de împărțire la zero, care trebuie prevenită. De asemenea, trebuie să accesăm coloane din afara tabelului de coloane calculate pentru a le utiliza în expresia coloanei calculate. În acest sfat analizăm cum să facem coloanele calculate mai flexibile.
Soluție
Colonii calculați pot adăuga o mare flexibilitate în proiectarea bazelor de date. Este posibilă aplicarea condiționată a expresiei de calcul, manipularea erorii împărțit la zero și accesarea oricărei coloane din afara tabelului de coloană calculată. Acest lucru ar face coloana calculată mai flexibilă și mai utilă pentru proiectarea bazei de date. Urmează lista unor scenarii pe care le vom trata în acest sfat.
- Calcularea condiționată a valorilor
- Eroare împărțită la zero
- Accesarea unei coloane în afara tabelului coloanei calculate
Vom utiliza tehnici T-SQL simple pentru a rezolva problemele menționate mai sus. Acest lucru va demonstra flexibilitatea și capacitatea coloanelor calculate de a rezolva orice astfel de probleme.
Calcularea condiționată a valorilor
Avem un tabel simplu cu câteva coloane legate de angajați. Există, de asemenea, o coloană calculată care calculează pensionarea la vârsta de 60 de ani. Avem o nouă cerință de a stabili limita de vârstă la 65 de ani pentru manageri, dar de a o menține la 60 de ani pentru toți ceilalți angajați.
Potem realiza acest lucru utilizând o formulă condiționată folosind o instrucțiune CASE, așa cum se arată în următorul cod. Pentru coloana DORetirement, dacă desemnarea este „Manager”, atunci pensionarea va fi de 65, altfel va fi de 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
Dacă rulați codul de mai sus, ar trebui să obțineți o ieșire ca cea de mai jos. Prin utilizarea unei instrucțiuni CASE, putem defini expresii separate în funcție de criteriile furnizate.
Eroare de împărțit la zero
În expresia de calcul, dacă împărțim, atunci poate exista o șansă de a avea un zero la numitor. În astfel de cazuri, riscăm să avem o eroare din cauza unei împărțiri la zero.
Considerăm un scenariu în care avem o coloană calculată cu formula AS (numărător/dominator) în care coloanele numărător și numitor sunt folosite pentru coloana calculată . În acest caz, o eroare de împărțire la zero ar fi întâlnită ori de câte ori expresia coloanei calculate este calculată cu un zero în coloana numitor.
Pot fi evitată această eroare prin înlocuirea zero-ului din numitor cu o valoare NULL folosind funcția NULLIF, așa cum se arată mai jos. Cea de-a patra înregistrare pe care o introducem în tabel ar cauza o problemă de împărțire la zero, dar funcția NULLIF o convertește la un 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
Din moment ce nu se poate împărți la NULL, valoarea returnată este NULL pentru această coloană pentru această înregistrare.
În mod implicit, o coloană calculată va permite valori NULL. Cu toate acestea, puteți specifica în mod explicit NOT NULL numai în cazul coloanelor calculate persistate.
Accesarea unei coloane din afara tabelului coloanei calculate
O coloană calculată nu poate accesa direct nicio coloană din afara tabelului său. Această limitare poate fi depășită prin utilizarea unei funcții definite de utilizator. O UDF poate fi utilizată în expresie pentru a accesa orice coloană din afara tabelului de coloane calculate.
În scriptul de mai jos, se creează o UDF pentru a calcula soldul de concediu al angajatului pentru a arăta câte zile de concediu mai are un angajat dincolo de maximul de 20 de zile. Aceste date provin dintr-un tabel secundar numit 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
Așa că mai jos putem vedea numărul de zile rămase disponibile pentru fiecare angajat.
Există câteva considerații legate de UDF-uri atunci când o coloană calculată urmează să fie utilizată într-un index. Puteți citi mai multe despre acest aspect în acest sfat: Cum se creează indici pe coloane calculate în SQL Server.
Pași următori
Problemele menționate mai sus și soluțiile lor funcționează în același mod pentru coloanele calculate persistate sau neperistate. Utilizarea flexibilității disponibile a coloanelor calculate poate îmbunătăți proiectarea bazei dumneavoastră de date. Coloanele calculate sunt o opțiune bună de utilizat pentru a implementa logica de afaceri prin expresii.
- Click aici pentru a citi un sfat despre elementele de bază ale lucrului cu coloane calculate
- Click aici pentru a citi un sfat despre crearea de indici pe coloane calculate
Ultima actualizare: 2011-08-29
Despre autor
Atif Shehzad este un DBA SQL Server pasionat, revizor tehnic și autor de articole.Vezi toate sfaturile mele
- Mai multe sfaturi pentru dezvoltatorii de baze de date…
.