By: Atif Shehzad | Päivitetty: Lisää > T-SQL
Free MSSQLTips Webinar: SQL Serverin parhaat kehityskäytännöt
Osallistu tähän webinaariin ja tutustu SQL Serverin parhaisiin kehityskäytäntöihin. Andy Warren jakaa monivuotisen kokemuksensa ja antaa vinkkejä siitä, mikä on toiminut hänen kohdallaan parhaiten ja miten sinä voit hyödyntää osaa tästä tiedosta.
Obgelma
Olen käyttänyt laskennallisia sarakkeita yksinkertaisissa skenaarioissa ja nämä toimivat hyvin. Joissakin tapauksissa kohtaamme kuitenkin rajoituksia, kun toteutamme liiketoimintalogiikkaa laskennallisten sarakkeiden avulla. Meiltä vaaditaan esimerkiksi eri arvoja, jotka perustuvat erilliseen lausekkeeseen laskettujen arvojen määrittämiseksi. Lisäksi saattaa esiintyä nollalla jakamista koskeva virhe, joka on estettävä. Meidän on myös käytettävä lasketun saraketaulukon ulkopuolisia sarakkeita lasketun sarakkeen lausekkeessa käytettäväksi. Tässä vinkissä tarkastelemme, miten laskennallisista sarakkeista saadaan joustavampia.
Ratkaisu
Laskennalliset sarakkeet voivat lisätä suurta joustavuutta tietokannan suunnitteluun. On mahdollista soveltaa ehdollisesti laskennallista lauseketta, käsitellä nollalla jaettua virhettä ja käyttää mitä tahansa laskennallisen sarakkeen taulukon ulkopuolista saraketta. Tämä tekisi laskennallisesta sarakkeesta joustavamman ja kätevämmän tietokantasuunnittelussa. Seuraavassa on luettelo joistakin skenaarioista, joita aiomme käsitellä tässä vinkissä.
- Arvojen ehdollinen laskenta
- Jaksotettu nollalla -virhe
- Lasketun sarakkeen taulukon ulkopuolisen sarakkeen käyttäminen
Käytämme yksinkertaisia T-SQL-tekniikoita edellä mainittujen ongelmien ratkaisemiseen. Tämä osoittaa laskennallisten sarakkeiden joustavuuden ja kyvyn käsitellä tällaisia ongelmia.
Varojen ehdollinen laskenta
Meillä on yksinkertainen taulukko, jossa on joitakin työntekijöihin liittyviä sarakkeita. Siinä on myös laskettu sarake, joka laskee eläkkeelle jäämisen 60-vuotiaana. Meillä on uusi vaatimus asettaa ikärajaksi 65 vuotta johtajille, mutta pitää se 60 vuotta kaikille muille työntekijöille.
Voimme toteuttaa tämän käyttämällä ehdollista kaavaa CASE-lauseen avulla, kuten seuraavassa koodissa näkyy. DORetirement-sarakkeessa, jos nimitys on ”Manager”, eläkeikä on 65. Muuten se on 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
Jos suoritat yllä olevan koodin, sinun pitäisi saada seuraavan kaltainen tuloste. CASE-lauseen avulla voimme määritellä erillisiä lausekkeita annettujen kriteerien mukaan.
Divided by Zero Error
Laskennallisessa lausekkeessa, jos jaamme, niin silloin voi olla mahdollisuus, että nimittäjässä on nolla. Tällaisissa tapauksissa meillä on riski saada virhe nollalla jakamisen takia.
Harkitaan skenaariota, jossa meillä on laskennallinen sarake kaavalla AS (osoittaja/nimittäjä), jossa laskennalliseen sarakkeeseen käytetään osoittaja- ja nimittäjäsarakkeita . Tässä tapauksessa jaa nollalla -virhe ilmenee aina, kun lasketun sarakkeen lausekkeen laskennassa nimittäjäsarakkeessa on nolla.
Voidaan välttää tämä virhe korvaamalla nimittäjässä oleva nolla NULL-arvolla käyttämällä NULLIF-funktiota, kuten alla on esitetty. Neljäs taulukkoon lisäämämme tietue aiheuttaisi nollalla jakamisen ongelman, mutta NULLIF-funktio muuntaa tämän NULL-arvoksi.
-- 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
Koska NULLilla ei voi jakaa, palautettu arvo on NULL tälle sarakkeelle tämän yhden tietueen osalta.
Oletusarvoisesti laskettu sarake sallii NULL-arvot. Voit kuitenkin määrittää nimenomaisesti NOT NULL -arvon vain persistoitujen laskennallisten sarakkeiden kanssa.
Sarakkeen käyttäminen lasketun sarakkeen taulun ulkopuolella
Laskettu sarake ei voi käyttää suoraan mitään saraketta taulunsa ulkopuolella. Tämä rajoitus voidaan kiertää käyttämällä käyttäjän määrittelemää funktiota. UDF:ää voidaan käyttää lausekkeessa minkä tahansa lasketun saraketaulukon ulkopuolisen sarakkeen käyttämiseen.
Alhaalla olevassa komentosarjassa luodaan UDF, jolla lasketaan työntekijän lomasaldo ja näytetään, kuinka monta lomapäivää työntekijällä on jäljellä 20 päivän enimmäismäärän lisäksi. Nämä tiedot tulevat toissijaisesta taulusta nimeltä 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
Alhaalla näkyy siis kunkin työntekijän jäljellä olevien vapaapäivien määrä.
UDF:iin liittyy joitakin näkökohtia, kun laskettua saraketta on tarkoitus käyttää indeksissä. Voit lukea tästä lisää tästä vinkistä: Indeksien luominen laskennallisille sarakkeille SQL Serverissä.
Seuraavat vaiheet
Yllä mainitut ongelmat ja niiden ratkaisut toimivat samalla tavalla persistoitujen tai persistoimattomien laskennallisten sarakkeiden osalta. Laskettujen sarakkeiden käytettävissä olevan joustavuuden hyödyntäminen voi parantaa tietokannan suunnittelua. Lasketut sarakkeet ovat hyvä vaihtoehto liiketoimintalogiikan toteuttamiseen lausekkeiden avulla.
- Lue tästä vinkki laskettujen sarakkeiden kanssa työskentelyn perusteista
- Lue tästä vinkki indeksien luomisesta laskettuihin sarakkeisiin
Viimeisin päivitetty:
Tietoa kirjoittajasta
Atif Shehzad on intohimoinen SQL Serverin tietokantapäällikkö (SQL Server DBA), tekninen arvioija ja artikkelien kirjoittaja.Katsele kaikki vinkkini
- Muut tietokantakehittäjän vinkit…