Introduktion
Beregnede kolonner er blot virtuelle kolonner, hvis indhold er resultatet af et udtryk. Normalt bruges de til at indeholde data baseret på resten af kolonnerne i tabellen. Udtrykket kan indeholde andre ikke beregnede kolonner i tabellen, konstanter, operatorer og funktioner, men du kan ikke angive en forespørgsel som et udtryk for en beregnet kolonne.
Da de er “virtuelle” kolonner, gemmes de ikke på disken ligesom resten af tabellen. Faktisk gemmes de ikke, men beregnes, hver gang der er adgang til kolonnen i en forespørgsel. Som du vil se, kan du tvinge SQL Server til at gemme (“persistere”) kolonnen i tabellen med nogle begrænsninger.
Den bedste måde at forstå, hvordan beregnede kolonner fungerer, er ved at bruge eksempler. Til sidst finder du en fil med alle de scripts, der er brugt i artiklen, og vi vil vise dig nogle af dem i teksten for at illustrere forklaringerne. Til at begynde med vil vi oprette to tabeller: den første skal indeholde fakturaoplysninger og den anden med detaillinjerne for disse fakturaer. Du kan også finde nogle indsætninger i scriptfilen for at oprette eksempeldata.
CREATE TABLE invoices( id_invoice INT PRIMARY KEY IDENTITY , customer_name VARCHAR(25));CREATE TABLE detail_lines( id_detail INT PRIMARY KEY IDENTITY , id_invoice_detail INT , product VARCHAR(30) , unit_price MONEY , quantity INT , FOREIGN KEY (id_invoice_detail) REFERENCES invoices (id_invoice));
Måden at oprette en beregnet kolonne på er den samme måde, som du ville oprette andre kolonner i en tabel, nemlig med en CREATE TABLE- eller ALTER TABLE-anvisning. For en beregnet kolonne erstatter vi kolonnens datatype med det udtryk, der skal bruges til at få kolonnens indhold. Syntaksen vil være kolonnens navn, efterfulgt af nøgleordet “as” og derefter udtrykket. Lad os oprette en beregnet kolonne i tabellen detail_line til at gemme det samlede beløb for linjen, som vi beregner ved at gange unit_price og mængde.
ALTER TABLE detail_lines ADD total_amount AS unit_price * quantity;
Tjek for en beregnet kolonne
Der er flere måder at bekræfte, at en kolonne virkelig er en beregnet kolonne. En af dem er at bruge funktionen columnproperty() med angivelse af egenskaben “IsComputed”.
SELECT COLUMNPROPERTY(OBJECT_ID('dbo.detail_lines'),'total_amount','IsComputed')
En anden måde at få oplysninger om beregnede kolonner på er via systemvisningen sys.computed_columns. Denne visning er en udvidelse af visningen sys.columns. Det betyder, at sys.computed_columns arver alle kolonnerne fra sys.columns view og tilføjer også andre, der er specifikke for denne type kolonne. I løbet af artiklen vil vi se nogle af kolonnerne i denne visning, da vi ser forskellige egenskaber ved de beregnede kolonner. Indtil videre er det nok at vide, at denne visning kun viser beregnede kolonner og har en kolonne, der hedder is_computed, som fortæller, om kolonnen er beregnet eller ej. Det er klart, at alle registreringer i denne visning vil have et tal i denne kolonne.
SELECT name, is_computed FROM sys.computed_columns;
Da indholdet af kolonnen beregnes, hver gang kolonnen refereres i en forespørgsel, opdateres indholdet altid. Enhver ændring i de kolonner, der er medtaget i udtrykket, afspejles automatisk i kolonnens værdi. Vi kan se dette ved at ændre mængden i et register i eksemplet detail_lines-tabellen og kontrollere resultatet.
UPDATE detail_lines SET quantity = 4 WHERE product = 'Cup'SELECT product, unit_price, quantity, total_amount FROM detail_lines WHERE product = 'Cup'
Et skridt fremad
Det, vi har set indtil nu med beregnede kolonner, er meget grundlæggende, da det kun drejer sig om at udføre beregninger med andre kolonner. Udtrykket for de beregnede kolonner kan dog indeholde funktioner, både T-SQL-standardfunktioner og brugerdefinerede funktioner (UDF). På denne måde er det muligt at udvide funktionaliteten af disse kolonner meget mere.
Lad os se et eksempel på dette. Vi vil tilføje en beregnet kolonne til tabellen fakturaer, som beregner det samlede beløb for fakturaen. For at gøre dette skal vi hente fakturanummeret og forespørge i tabellen detail_lines for at summere totalbeløbet fra hver post med det pågældende faktura-id. Den bedste måde at gøre dette på er at bruge en funktion, der modtager faktura-id’et som en parameter og returnerer summen. Derefter skal vi oprette den kolonne, der bruger denne funktion.
CREATE FUNCTION fn_total_invoice (@invoice_number INT)RETURNS MONEYASBEGIN DECLARE @total MONEY SELECT @total=SUM(total_amount) FROM detail_lines WHERE id_invoice_detail = @invoice_number RETURN @totalENDALTER TABLE invoices ADD total_invoice AS dbo.fn_total_invoice(id_invoice)
Vi kan kontrollere, at denne kolonne fungerer korrekt, ved at tilføje en ny post i tabellen, detail_lines, så total_fakturaen skulle ændre sig.
INSERT INTO detail_lines (id_invoice_detail,product,unit_price, quantity) VALUES (2,'Cup',9.90,1)SELECT id_invoice, customer_name, total_invoice FROM invoices WHERE id_invoice=2
Ændring af kolonnen
Der kan være situationer, hvor du er nødt til at ændre en beregnet kolonne. Dette er desværre ikke muligt. For at foretage denne ændring er det nødvendigt at slette kolonnen og genskabe den med det nye udtryk.
I det tilfælde, hvor den beregnede kolonne bruger en ekstern funktion, vil vi ikke have lov til at ændre denne funktion. Hvis vi forsøger, får vi en fejl, der angiver, at denne funktion er knyttet til tabellen. For at ændre funktionen er det nødvendigt at slette kolonnen, udføre ændringen af funktionen og til sidst oprette kolonnen igen med den nye version af funktionen.
Vi kan få definitionen af kolonnen på kolonnen “definition” fra visningen sys.computed_columns.
SEECT name, definition FROM sys.computed_columns
Lagring af en beregnet kolonne
Som vi nævnte før, er disse kolonner “virtuelle”, så de er ikke fysisk gemt i tabellen. Der er dog mulighed for at tvinge beregningen til at blive fysisk gemt i tabellen, hvilket kaldes “persist “ing af kolonnen. Dette kan forbedre ydeevnen med SELECT-statements, da man derved undgår at skulle udføre beregningen af kolonnen, hver gang der henvises til den.
For at persistere kolonnen skal det udtryk, der bruges til at oprette kolonnen, desuden være et “deterministisk” udtryk. Som vi kan se på Microsofts websted, “returnerer deterministiske funktioner altid det samme resultat, hver gang de kaldes med et bestemt sæt inputværdier og med den samme tilstand i databasen.” (https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions?view=sql-server-2017). Hvis vi vil vide, om SQL Server betragter udtrykket for en beregnet kolonne som deterministisk eller ej, kan vi bruge funktionen columnproperty() med egenskaben “IsDeterministic”.
SELECT COLUMNPROPERTY(OBJECT_ID('dbo.detail_lines'),'total_amount','IsDeterministic')SELECT COLUMNPROPERTY(OBJECT_ID('dbo.invoices'),'total_invoice','IsDeterministic')
Hvis definitionen af kolonnen er en brugerdefineret funktion, kan du også kontrollere, om selve denne funktion er deterministisk eller ej. For at gøre dette skal du bruge funktionen objectproperty() med egenskaben IsDeterministic.
SELECT OBJCETPROPERTY(OBJECT_ID('dbo.fn_total_invoice'),'IsDeterministic')
Som du kan se af forespørgslerne, betragtes kolonnen i det første eksempel, hvor vi beregner den samlede pris for en detalje, som deterministisk. Men den funktion, der beregner den samlede pris for regningen, betragtes som ikke-deterministisk. På denne måde kan kun kolonnen total_price i tabellen detail_table gemmes i tabellen.
ALTER TABLE detail_lines DROP COLUMN total_amount;ALTER TABLE detail_lines ADD total_amount AS unit_price * quantity PERSISTED;
Igen i visningen sys.computed_columns kan du se feltet is_persisted, som angiver, om kolonnen er persisteret eller ej i tabellen.
Indekser med beregnede kolonner
Det er muligt at bruge beregnede kolonner i indekser, selv om de skal opfylde flere krav:
- Ejerskab: Alle de funktioner, der anvendes i definitionen af den beregnede kolonne, skal være ejet af den samme bruger som tabellen.
- Determinisme: Den beregnede kolonne skal være deterministisk. Hvis kolonnen indeholder CLR-udtryk, skal kolonnen ud over at være deterministisk også være persisteret.
- Nøjagtighed: Udtrykket i den beregnede kolonne skal være præcist. Dette indebærer, at det ikke kan være af datatypen “float” eller “real”. Du kan heller ikke bruge denne datatype i din definition. Denne egenskab kan verificeres med funktionen columnproperty() ved at angive egenskaben IsPrecise.
- Datatype: Den beregnede kolonne kan ikke være af typerne text, ntext eller image. Hvis udtrykket også indeholder datatyperne image, ntext, text, varchar (max), nvarchar (max), varbinary (max) eller xml, kan det kun anvendes, hvis den datatype, der fremkommer i udtrykket, er tilladt i et indeks.
Ud over disse overvejelser skal de forbindelser, der bruges til at oprette kolonnen, og den, der bruges til at oprette indekset, have visse konfigurationer for at kunne udføre disse handlinger.
Forbindelsen til at oprette den beregnede kolonne skal have indstillingen ANSI_NULLS aktiv. Dette kan kontrolleres med funktionen columnproperty() ved at angive egenskaben IsAnsiNullsOn.
Forbindelsen til oprettelse af indekset samt forbindelser til at udføre indsættelse, opdatering og sletning af poster, der påvirker indekset, skal have indstillingerne ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER aktiveret. Derudover skal indstillingen NUMERIC_ROUNDABORT være deaktiveret.
Sidste overvejelser
Til sidst vil vi gennemgå nogle yderligere aspekter, som det er nødvendigt at kende til for korrekt brug af de beregnede kolonner.
Det er klart, at beregnede kolonner ikke kan opdateres eller inkluderes i værdilisten for en INSERT-handling. Selv om de beregnede kolonner kan være en del af listen over resultater i en select-anvisning, kan de også bruges i klausulerne WHERE, ORDER BY eller i alle dem, hvor et udtryk kan sættes ind.
SELECT product, unit_price, quantity, total_amount FROM detail_lines WHERE total_amount > 10 ORDER BY total_amount
På trods af ovenstående kan en beregnet kolonne ikke bruges i definitionen af DEFAULT- eller FOREIGN KEY-begrænsninger. Det kan heller ikke være med en NOT NULL-begrænsningsdefinition.
På den anden side kan beregnede kolonner bruges som en del af PRIMARY KEY- eller UNIQUE-begrænsninger. For at gøre dette skal definitionen af den beregnede kolonne være et deterministisk udtryk.
Konklusion
Brugen af beregnede kolonner kan være meget nyttig i nogle situationer. Du skal undersøge nøje, hvor du skal bruge dem, på grund af de begrænsninger, de har, specielt for at oprette indeks og persistere dem. Dette er kun begyndelsen. Du er velkommen til at prøve nye ting og eksperimentere med beregnede kolonner for at finde nye muligheder.