Introduction
Computed columns zijn gewoon virtuele kolommen waarvan de inhoud het resultaat is van een expressie. Gewoonlijk worden zij gebruikt om gegevens te bevatten die gebaseerd zijn op de rest van de kolommen van de tabel. De uitdrukking kan andere niet berekende kolommen van de tabel, constanten, operatoren en functies bevatten, maar u kunt geen query specificeren als uitdrukking voor een berekende kolom.
Omdat het “virtuele” kolommen zijn, worden zij niet op schijf opgeslagen zoals de rest van de tabel. In feite worden ze niet opgeslagen, maar berekend telkens wanneer de kolom in een query wordt benaderd. Zoals u zult zien, kunt u SQL Server dwingen de kolom in de tabel op te slaan (“persist”) met enkele beperkingen.
De beste manier om te begrijpen hoe berekende kolommen werken is door gebruik te maken van voorbeelden. Aan het eind vind je een bestand met alle scripts die in dit artikel zijn gebruikt, en we zullen er een paar in de tekst laten zien om de uitleg te illustreren. Om te beginnen maken we twee tabellen: de eerste met factuurgegevens en de andere met de detailregels van die facturen. In het script-bestand vindt u ook enkele inserts om voorbeeldgegevens te creëren.
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));
De manier om een berekende kolom te maken is dezelfde manier waarop u andere kolommen in een tabel zou maken, met een CREATE TABLE of ALTER TABLE statement. Voor een berekende kolom vervangen we het datatype van de kolom door de expressie die zal worden gebruikt om de inhoud van de kolom te verkrijgen. De syntaxis is de naam van de kolom, gevolgd door het sleutelwoord “als”, en dan de expressie. Laten we een berekende kolom in de detail_line tabel maken om het totaalbedrag van de lijn op te slaan, dat we berekenen door unit_price en quantity te vermenigvuldigen.
ALTER TABLE detail_lines ADD total_amount AS unit_price * quantity;
Controle op een berekende kolom
Er zijn verschillende manieren om te bevestigen dat een kolom werkelijk een berekende kolom is. Een daarvan is met behulp van de functie columnproperty() waarbij de eigenschap “IsComputed” wordt gespecificeerd.
SELECT COLUMNPROPERTY(OBJECT_ID('dbo.detail_lines'),'total_amount','IsComputed')
Een andere manier om informatie over berekende kolommen te verkrijgen is via de systeemview, sys.computed_columns. Deze view is een uitbreiding van de sys.columns view. Dit betekent dat sys.computed_columns alle kolommen erft van de sys.columns view en ook andere kolommen toevoegt die specifiek zijn voor dit type kolom. In de loop van het artikel zullen we enkele van de kolommen in deze view zien, omdat we verschillende kenmerken van de berekende kolommen zien. Voor nu is het voldoende om te weten dat deze view alleen berekende kolommen toont en een kolom heeft, genaamd is_computed, die vertelt of de kolom berekend is of niet. Vanzelfsprekend zullen alle records van deze view een één in deze kolom hebben.
SELECT name, is_computed FROM sys.computed_columns;
Aangezien de inhoud van de kolom wordt berekend telkens wanneer in een query naar de kolom wordt verwezen, wordt de inhoud altijd bijgewerkt. Elke verandering in de kolommen die in de expressie zijn opgenomen, wordt automatisch weerspiegeld in de waarde van de kolom. We kunnen dit zien door de hoeveelheid in een register in de voorbeeld detail_lines tabel te wijzigen en het resultaat te controleren.
UPDATE detail_lines SET quantity = 4 WHERE product = 'Cup'SELECT product, unit_price, quantity, total_amount FROM detail_lines WHERE product = 'Cup'
Een stap vooruit
Wat we tot nu toe hebben gezien met berekende kolommen is erg basaal omdat het alleen gaat om het uitvoeren van berekeningen met andere kolommen. De uitdrukking van de berekende kolommen kan echter functies bevatten, zowel T-SQL standaardfuncties als door de gebruiker gedefinieerde functies (UDF). Op deze manier is het mogelijk om de functionaliteit van deze kolommen veel verder uit te breiden.
Laten we hier eens een voorbeeld van zien. We gaan een berekende kolom aan de facturen-tabel toevoegen die het totaalbedrag van de factuur berekent. Om dit te doen, moeten we het factuurnummer ophalen en de detail_lines tabel bevragen om het totaal_bedrag van elk record met dat factuurnummer op te tellen. De beste manier om dit te doen is met behulp van een functie die het factuurnummer als parameter ontvangt en de som teruggeeft. Daarna moeten we de kolom maken die deze functie gebruikt.
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)
We kunnen controleren of deze kolom correct werkt door een nieuw record toe te voegen in de tabel, detail_lines, zodat het totaal_bedrag zou moeten veranderen.
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
De kolom wijzigen
Er kunnen zich situaties voordoen dat u een berekende kolom moet wijzigen. Helaas is dat niet mogelijk. Om die wijziging aan te brengen, moet de kolom worden verwijderd en met de nieuwe uitdrukking opnieuw worden aangemaakt.
In het geval dat de berekende kolom gebruik maakt van een externe functie, zullen wij niet in staat zijn deze functie te wijzigen. Als wij het proberen, krijgen wij een foutmelding dat deze functie aan de tabel is gekoppeld. Om de functie te wijzigen, moeten we de kolom verwijderen, de wijziging van de functie uitvoeren, en tenslotte de kolom opnieuw aanmaken met de nieuwe versie van de functie.
We kunnen de definitie van de kolom opvragen bij “definition” column from the sys.computed_columns view.
SEECT name, definition FROM sys.computed_columns
Het opslaan van een Computed Column
Zoals we al eerder zeiden, zijn deze kolommen “virtueel”, dus ze worden niet fysiek in de tabel opgeslagen. Er is echter de mogelijkheid om de berekening te forceren fysiek in de tabel te worden opgeslagen, wat “persist “en van de kolom wordt genoemd. Dit kan de prestaties van SELECT statements verbeteren, omdat de berekening van de kolom dan niet telkens hoeft te worden uitgevoerd wanneer ernaar wordt verwezen.
Bovendien moet, om de kolom te persisteren, de expressie die wordt gebruikt om de kolom te creëren, een “deterministische” zijn. Zoals we kunnen zien op de Microsoft website, “deterministische functies geven altijd hetzelfde resultaat terug elke keer dat ze worden aangeroepen met een specifieke set van invoerwaarden en gegeven dezelfde toestand van de database.” (https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions?view=sql-server-2017). Als we willen weten of SQL Server de expressie van een berekende kolom als deterministisch beschouwt of niet, kunnen we de columnproperty() functie gebruiken met de “IsDeterministic” eigenschap.
SELECT COLUMNPROPERTY(OBJECT_ID('dbo.detail_lines'),'total_amount','IsDeterministic')SELECT COLUMNPROPERTY(OBJECT_ID('dbo.invoices'),'total_invoice','IsDeterministic')
Als de definitie van de kolom een door de gebruiker gedefinieerde functie is, kunt u ook controleren of die functie zelf deterministisch is of niet. Daartoe moet u de objectproperty()-functie gebruiken met de eigenschap IsDeterministic.
SELECT OBJCETPROPERTY(OBJECT_ID('dbo.fn_total_invoice'),'IsDeterministic')
Zoals u in de query’s kunt zien, wordt de kolom van het eerste voorbeeld, waarin we de totale prijs van het detail berekenen, als deterministisch beschouwd. De functie die de totale prijs van de rekening berekent, wordt echter als niet-deterministisch beschouwd. Op deze manier kan alleen de kolom totaal_prijs van de detail_tabel in de tabel worden opgeslagen.
ALTER TABLE detail_lines DROP COLUMN total_amount;ALTER TABLE detail_lines ADD total_amount AS unit_price * quantity PERSISTED;
Opnieuw ziet u in de sys.computed_columns view het veld is_persisted, dat aangeeft of de kolom al dan niet in de tabel is persisted.
Indexen met berekende kolommen
Het is mogelijk om berekende kolommen in indexen te gebruiken, hoewel ze aan verschillende eisen moeten voldoen:
- Eigendom: Alle functies die in de definitie van de berekende kolom worden gebruikt, moeten eigendom zijn van dezelfde gebruiker als de tabel.
- Determinisme: De berekende kolom moet deterministisch zijn. Als de kolom CLR-expressies bevat, moet de kolom niet alleen deterministisch zijn, maar ook persistent.
- Nauwkeurigheid: De uitdrukking van de berekende kolom moet nauwkeurig zijn. Dit houdt in dat deze niet van het gegevenstype “float” of “real” mag zijn. Evenmin kunt u dit gegevenstype in uw definitie gebruiken. Deze eigenschap kan worden geverifieerd met de functie columnproperty() door de eigenschap IsPrecise op te geven.
- Gegevenstype: De berekende kolom kan niet van het type tekst, ntext of image zijn. Als de expressie de gegevenstypen image, ntext, text, varchar (max), nvarchar (max), varbinary (max), of xml bevat, kan deze alleen worden gebruikt als het gegevenstype dat uit de expressie resulteert, is toegestaan in een index.
Naast deze overwegingen moeten de verbindingen die worden gebruikt om de kolom te creëren en de verbinding die wordt gebruikt om de index te creëren, bepaalde configuraties hebben om deze acties te kunnen uitvoeren.
De verbinding om de berekende kolom te creëren, moet de optie ANSI_NULLS actief hebben. Dit kan worden geverifieerd met de functie columnproperty(), door de eigenschap IsAnsiNullsOn op te geven.
De verbinding om de index te creëren is, evenals verbindingen om insert, update en delete uit te voeren van records die de index beïnvloeden, moeten de opties ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER actief hebben. Bovendien moet de NUMERIC_ROUNDABORT optie zijn uitgeschakeld.
Laatste overwegingen
Tot slot gaan we nog enkele aspecten bekijken die men moet weten voor het juiste gebruik van de berekende kolommen.
Uiteraard kunnen berekende kolommen niet worden bijgewerkt, noch worden opgenomen in de waardenlijst van een INSERT actie. Hoewel de berekende kolommen deel kunnen uitmaken van de lijst van resultaten van een select statement, kunnen zij ook worden gebruikt in de clausules WHERE, ORDER BY, of in al die waarin een uitdrukking kan worden gezet.
SELECT product, unit_price, quantity, total_amount FROM detail_lines WHERE total_amount > 10 ORDER BY total_amount
Ondanks het bovenstaande kan een berekende kolom niet worden gebruikt in DEFAULT of FOREIGN KEY constraint definitie. Hetzelfde geldt voor een NOT NULL constraint-definitie.
Daarentegen kunnen berekende kolommen wel worden gebruikt als onderdeel van PRIMARY KEY- of UNIQUE-constraints. Hiervoor moet de definitie van de berekende kolom een deterministische uitdrukking zijn.
Conclusie
Het gebruik van berekende kolommen kan in sommige situaties zeer nuttig zijn. Je moet zorgvuldig bestuderen waar ze te gebruiken, vanwege de beperkingen die ze hebben, speciaal om indexen te maken en ze te persisteren. Dit is nog maar het begin. Voel je vrij om nieuwe dingen te proberen en experimenteer met berekende kolommen om nieuwe mogelijkheden te vinden.