Introduktion
Beräknade kolumner är bara virtuella kolumner vars innehåll är resultatet av ett uttryck. Vanligtvis används de för att hålla data baserade på resten av kolumnerna i tabellen. Uttrycket kan innehålla andra icke beräknade kolumner i tabellen, konstanter, operatörer och funktioner, men du kan inte ange en fråga som ett uttryck för en beräknad kolumn.
Eftersom de är ”virtuella” kolumner lagras de inte på disk som resten av tabellen. Faktum är att de inte lagras utan beräknas varje gång kolumnen nås i en fråga. Som du kommer att se kan du tvinga SQL Server att lagra (”persist”) kolumnen i tabellen med vissa begränsningar.
Det bästa sättet att förstå hur beräknade kolumner fungerar är att använda exempel. I slutet hittar du en fil som innehåller alla skript som används i artikeln, och vi kommer att visa några av dem i texten för att illustrera förklaringarna. Till att börja med ska vi skapa två tabeller: den första för att hålla fakturainformation och den andra med detaljraderna för dessa fakturor. Du kan också hitta några insättningar i skriptfilen för att skapa exempeldata.
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));
Sättet att skapa en beräknad kolumn är detsamma som att skapa andra kolumner i en tabell, med ett CREATE TABLE- eller ALTER TABLE-meddelande. För en beräknad kolumn ersätter vi kolonnens datatyp med det uttryck som kommer att användas för att få fram kolumnens innehåll. Syntaxen är kolumnnamnet, följt av nyckelordet ”as” och sedan uttrycket. Låt oss skapa en beräknad kolumn i tabellen detail_line för att lagra det totala beloppet för linjen, som vi beräknar genom att multiplicera unit_price och quantity.
ALTER TABLE detail_lines ADD total_amount AS unit_price * quantity;
Kontrollera om det rör sig om en beräknad kolumn
Det finns flera sätt att bekräfta att en kolumn verkligen är en beräknad kolumn. Ett av dem är att använda funktionen columnproperty() och ange egenskapen ”IsComputed”.
SELECT COLUMNPROPERTY(OBJECT_ID('dbo.detail_lines'),'total_amount','IsComputed')
Ett annat sätt att få information om beräknade kolumner är genom systemvyn sys.computed_columns. Denna vy är en utvidgning av vyn sys.columns. Det innebär att sys.computed_columns ärver alla kolumner från vyn sys.columns och lägger även till andra som är specifika för den här typen av kolumner. Under hela artikeln kommer vi att se några av kolumnerna i den här vyn, eftersom vi ser olika egenskaper hos de beräknade kolumnerna. För tillfället räcker det med att veta att den här vyn endast visar beräknade kolumner och har en kolumn, som heter is_computed, som talar om om kolumnen är beräknad eller inte. Självklart kommer alla poster i den här vyn att ha en etta i den här kolumnen.
SELECT name, is_computed FROM sys.computed_columns;
Eftersom innehållet i kolumnen beräknas varje gång kolumnen refereras i en fråga, uppdateras innehållet alltid. Alla ändringar i de kolumner som ingår i uttrycket återspeglas automatiskt i kolumnens värde. Vi kan se detta genom att ändra kvantiteten i ett register i exempeltabellen detail_lines och kontrollera resultatet.
UPDATE detail_lines SET quantity = 4 WHERE product = 'Cup'SELECT product, unit_price, quantity, total_amount FROM detail_lines WHERE product = 'Cup'
Ett steg framåt
Det vi hittills har sett med beräknade kolumner är mycket grundläggande eftersom det bara handlar om att utföra beräkningar med andra kolumner. Uttrycket för de beräknade kolumnerna kan dock innehålla funktioner, både T-SQL-standardfunktioner och användardefinierade funktioner (UDF). På så sätt är det möjligt att utöka funktionaliteten hos dessa kolumner mycket mer.
Låt oss se ett exempel på detta. Vi ska lägga till en beräknad kolumn i tabellen fakturor som beräknar det totala beloppet för fakturan. För att göra detta måste vi hämta fakturanumret och fråga tabellen detail_lines för att summera totalbeloppet från varje post med detta faktura-ID. Det bästa sättet att göra detta är att använda en funktion som tar emot fakturanummer som en parameter och returnerar summan. Därefter måste vi skapa kolumnen som använder den här funktionen.
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 kontrollera att kolumnen fungerar korrekt genom att lägga till en ny post i tabellen, detail_lines, så att summa_faktura borde ändras.
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
Ändra kolumnen
Det kan förekomma situationer där du måste ändra en beräknad kolumn. Tyvärr är detta inte möjligt. För att göra den ändringen är det nödvändigt att ta bort kolumnen och återskapa den med det nya uttrycket.
Om den beräknade kolumnen använder en extern funktion får vi inte ändra denna funktion. Om vi försöker får vi ett fel som visar att funktionen är kopplad till tabellen. För att ändra funktionen är det nödvändigt att ta bort kolumnen, utföra ändringen av funktionen och slutligen återskapa kolumnen med den nya versionen av funktionen.
Vi kan få definitionen av kolumnen i kolumnen ”definition” från vyn sys.computed_columns.
SEECT name, definition FROM sys.computed_columns
Lagring av en beräknad kolumn
Som vi nämnde tidigare är dessa kolumner ”virtuella” så de lagras inte fysiskt i tabellen. Det finns dock en möjlighet att tvinga beräkningen att lagras fysiskt i tabellen, vilket kallas ”persist ”ing av kolumnen. Detta kan förbättra prestandan med SELECT-anvisningar eftersom man då slipper utföra beräkningen av kolumnen varje gång den refereras.
Dessutom måste uttrycket som används för att skapa kolumnen vara ”deterministiskt” för att kolumnen ska kunna bevaras. Som vi kan se på Microsofts webbplats, ”returnerar deterministiska funktioner alltid samma resultat varje gång de anropas med en specifik uppsättning ingångsvärden och med samma tillstånd i databasen”. (https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions?view=sql-server-2017). Om vi vill veta om SQL Server betraktar uttrycket för en beräknad kolumn som deterministisk eller inte kan vi använda funktionen columnproperty() med egenskapen ”IsDeterministic”.
SELECT COLUMNPROPERTY(OBJECT_ID('dbo.detail_lines'),'total_amount','IsDeterministic')SELECT COLUMNPROPERTY(OBJECT_ID('dbo.invoices'),'total_invoice','IsDeterministic')
Om definitionen av kolumnen är en användardefinierad funktion kan du också kontrollera om själva funktionen är deterministisk eller inte. För att göra detta måste du använda funktionen objectproperty() med egenskapen IsDeterministic.
SELECT OBJCETPROPERTY(OBJECT_ID('dbo.fn_total_invoice'),'IsDeterministic')
Som du kan se i frågorna anses kolumnen i det första exemplet, där vi beräknar detaljens totalpris, vara deterministisk. Funktionen som beräknar totalpriset för fakturan anses dock vara icke-deterministisk. På så sätt kan endast kolumnen total_price i tabellen detail_table lagras i tabellen.
ALTER TABLE detail_lines DROP COLUMN total_amount;ALTER TABLE detail_lines ADD total_amount AS unit_price * quantity PERSISTED;
Återigen i vyn sys.computed_columns kan du se fältet is_persisted, som anger om kolumnen är persisterad eller inte i tabellen.
Index med beräknade kolumner
Det är möjligt att använda beräknade kolumner i index, även om de måste uppfylla flera krav:
- Ägarskap: Alla funktioner som används i definitionen av den beräknade kolumnen måste ägas av samma användare som tabellen.
- Determinism: Alla funktioner som används i definitionen av den beräknade kolumnen måste ägas av samma användare som tabellen: Den beräknade kolumnen måste vara deterministisk. Om kolumnen innehåller CLR-uttryck måste kolumnen dessutom, förutom att vara deterministisk, vara persisterad.
- Noggrannhet: Uttrycket i den beräknade kolumnen måste vara exakt. Detta innebär att det inte kan vara av datatypen ”float” eller ”real”. Du kan inte heller använda denna typ av data i din definition. Denna egenskap kan verifieras med funktionen columnproperty() genom att ange egenskapen IsPrecise.
- Datatyp: Den beräknade kolumnen kan inte vara av typen text, ntext eller image. Om uttrycket innehåller datatyperna image, ntext, text, varchar (max), nvarchar (max), varbinary (max) eller xml kan det endast användas om den datatyp som uttrycket resulterar i är tillåten i ett index.
Utöver dessa överväganden måste anslutningarna som används för att skapa kolumnen och den som används för att skapa indexet ha vissa konfigurationer för att kunna utföra dessa åtgärder.
Anslutningen för att skapa den beräknade kolumnen måste ha alternativet ANSI_NULLS aktivt. Detta kan kontrolleras med funktionen columnproperty() genom att ange egenskapen IsAnsiNullsOn.
Anslutningen för att skapa indexet är, liksom anslutningar för att utföra insättning, uppdatering och radering av poster som påverkar indexet, måste ha alternativen ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER aktiva. Dessutom måste alternativet NUMERIC_ROUNDABORT vara inaktiverat.
Sista överväganden
Avslutningsvis ska vi gå igenom några ytterligare aspekter som det är nödvändigt att känna till för att kunna använda de beräknade kolumnerna på rätt sätt.
Beräknade kolumner kan naturligtvis inte uppdateras, och de kan inte heller inkluderas i värdelistan för en INSERT-åtgärd. Även om de beräknade kolumnerna kan vara en del av resultatlistan i ett select-statement kan de också användas i klausulerna WHERE, ORDER BY eller i alla de klausuler där ett uttryck kan sättas in.
SELECT product, unit_price, quantity, total_amount FROM detail_lines WHERE total_amount > 10 ORDER BY total_amount
Trots ovanstående kan en beräknad kolumn inte användas i definitionen av DEFAULT- eller FOREIGN KEY-begränsningar. Inte heller med en NOT NULL-begränsningsdefinition.
Å andra sidan kan beräknade kolumner användas som en del av PRIMARY KEY- eller UNIQUE-begränsningar. För att göra detta bör definitionen av beräknad kolumn vara ett deterministiskt uttryck.
Slutsats
Användningen av beräknade kolumner kan vara mycket användbar i vissa situationer. Du måste studera noggrant var du kan använda dem, på grund av de begränsningar de har, särskilt när det gäller att skapa index och bevara dem. Detta är bara början. Pröva gärna nya saker och experimentera med beräknade kolumner för att hitta nya möjligheter.