Introducere
Coloanele calculate sunt doar coloane virtuale al căror conținut este rezultatul unei expresii. De obicei, ele sunt utilizate pentru a păstra date bazate pe restul coloanelor din tabel. Expresia poate conține alte coloane necomputate ale tabelului, constante, operatori și funcții, dar nu puteți specifica o interogare ca expresie pentru o coloană calculată.
Deoarece sunt coloane „virtuale”, ele nu sunt stocate pe disc ca și restul tabelului. De fapt, ele nu sunt stocate, ci calculate de fiecare dată când coloana este accesată într-o interogare. După cum veți vedea, puteți forța SQL Server să stocheze („persistă”) coloana în tabel cu anumite restricții.
Cel mai bun mod de a înțelege cum funcționează coloanele calculate este să folosiți exemple. La sfârșit veți găsi un fișier care conține toate scripturile utilizate în articol, iar noi vă vom prezenta câteva dintre ele în text pentru a ilustra explicațiile. Pentru început, vom crea două tabele: primul pentru a păstra informații despre facturi și celălalt cu liniile de detaliu ale acestor facturi. Puteți găsi, de asemenea, câteva inserții în fișierul script pentru a crea exemple de date.
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));
Modul de a crea o coloană calculată este același în care ați crea alte coloane într-un tabel, cu o instrucțiune CREATE TABLE sau ALTER TABLE. Pentru o coloană calculată, înlocuim tipul de date al coloanei cu expresia care va fi utilizată pentru a obține conținutul coloanei. Sintaxa va fi numele coloanei, urmat de cuvântul cheie „as” și apoi de expresie. Să creăm o coloană calculată în tabelul detail_line pentru a stoca suma totală a liniei, pe care o calculăm prin înmulțirea prețului_unitar și a cantității.
ALTER TABLE detail_lines ADD total_amount AS unit_price * quantity;
Verificarea unei coloane calculate
Există mai multe modalități de a confirma că o coloană este într-adevăr o coloană calculată. Unul dintre ele este utilizarea funcției columnproperty() specificând proprietatea „IsComputed”.
SELECT COLUMNPROPERTY(OBJECT_ID('dbo.detail_lines'),'total_amount','IsComputed')
O altă modalitate de a obține informații despre coloanele calculate este prin intermediul vizualizării sistemului, sys.computed_columns. Această vizualizare este o extensie a vizualizării sys.columns. Aceasta înseamnă că sys.computed_columns moștenește toate coloanele din vizualizarea sys.columns și adaugă și altele care sunt specifice acestui tip de coloană. Pe parcursul articolului vom vedea unele dintre coloanele din această vizualizare, deoarece vom vedea diferite caracteristici ale coloanelor calculate. Deocamdată, este suficient să știm că această vizualizare afișează numai coloane calculate și are o coloană, numită is_computed, care spune dacă coloana este calculată sau nu. Evident, toate înregistrările din această vizualizare vor avea un unu în această coloană.
SELECT name, is_computed FROM sys.computed_columns;
Deoarece conținutul coloanei este calculat de fiecare dată când coloana este menționată într-o interogare, conținutul este întotdeauna actualizat. Orice modificare a coloanelor care sunt incluse în expresie se reflectă automat în valoarea coloanei. Putem vedea acest lucru modificând cantitatea într-un registru din tabelul de exemplu detail_lines și verificând rezultatul.
UPDATE detail_lines SET quantity = 4 WHERE product = 'Cup'SELECT product, unit_price, quantity, total_amount FROM detail_lines WHERE product = 'Cup'
Un pas înainte
Ceea ce am văzut până acum cu coloanele calculate este foarte elementar, deoarece implică doar efectuarea de calcule cu alte coloane. Cu toate acestea, expresia coloanelor calculate poate conține funcții, atât funcții standard T-SQL, cât și funcții definite de utilizator (UDF). În acest fel, este posibil să se extindă mult mai mult funcționalitatea acestor coloane.
Să vedem un exemplu în acest sens. Vom adăuga o coloană calculată în tabelul de facturi care să calculeze suma totală a facturii. Pentru a face acest lucru, trebuie să obținem numărul facturii și să interogăm tabelul detail_lines pentru a însuma total_amount din fiecare înregistrare cu acel id de factură. Cel mai bun mod de a face acest lucru este să folosim o funcție care primește ID-ul facturii ca parametru și returnează suma. După aceea, trebuie să creăm coloana care utilizează această funcție.
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)
Putem verifica dacă această coloană funcționează corect adăugând o nouă înregistrare în tabelul detail_lines, astfel încât factura_totală ar trebui să se modifice.
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
Modificarea coloanei
Pot exista situații în care trebuie să modificați o coloană calculată. Din păcate, acest lucru nu este posibil. Pentru a face această modificare, este necesar să ștergem coloana și să o recreăm cu noua expresie.
În cazul în care coloana calculată utilizează o funcție externă, nu vom avea voie să modificăm această funcție. Dacă încercăm, vom primi o eroare care indică faptul că această funcție este legată de tabel. Pentru a modifica funcția, este necesar să ștergem coloana, să efectuăm modificarea funcției și, în final, să recreăm din nou coloana cu noua versiune a funcției.
Putem obține definiția coloanei la coloana „definition” din vizualizarea sys.computed_columns.
SEECT name, definition FROM sys.computed_columns
Stocarea unei coloane calculate
După cum am menționat anterior, aceste coloane sunt „virtuale”, deci nu sunt stocate fizic în tabel. Cu toate acestea, există posibilitatea de a forța calculul să fie stocat fizic în tabel, ceea ce se numește „persistența” coloanei. Acest lucru poate îmbunătăți performanța în cazul instrucțiunilor SELECT, deoarece astfel se evită necesitatea de a efectua calculul coloanei de fiecare dată când aceasta este menționată.
În plus, pentru a persista coloana, expresia utilizată pentru a crea coloana trebuie să fie una „deterministă”. După cum putem vedea pe site-ul Microsoft, „funcțiile deterministe returnează întotdeauna același rezultat de fiecare dată când sunt apelate cu un set specific de valori de intrare și având în vedere aceeași stare a bazei de date”. (https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions?view=sql-server-2017). Dacă dorim să știm dacă SQL Server consideră expresia unei coloane calculate ca fiind deterministă sau nu, putem utiliza funcția columnproperty() cu proprietatea „IsDeterministic”.
SELECT COLUMNPROPERTY(OBJECT_ID('dbo.detail_lines'),'total_amount','IsDeterministic')SELECT COLUMNPROPERTY(OBJECT_ID('dbo.invoices'),'total_invoice','IsDeterministic')
Dacă definiția coloanei este o funcție definită de utilizator, puteți verifica, de asemenea, dacă acea funcție este ea însăși deterministă sau nu. Pentru a face acest lucru, trebuie să utilizați funcția objectproperty() cu proprietatea IsDeterministic.
SELECT OBJCETPROPERTY(OBJECT_ID('dbo.fn_total_invoice'),'IsDeterministic')
După cum puteți vedea din interogări, coloana din primul exemplu, în care calculăm prețul total al detaliului, este considerată deterministă. Cu toate acestea, funcția care calculează prețul total al facturii este considerată nedeterministă. În acest fel, numai coloana preț_total din tabelul detaliu_table poate fi stocată în tabel.
ALTER TABLE detail_lines DROP COLUMN total_amount;ALTER TABLE detail_lines ADD total_amount AS unit_price * quantity PERSISTED;
Din nou, în vizualizarea sys.computed_columns puteți vedea câmpul is_persisted, care va indica dacă coloana este sau nu persistă în tabel.
Indexuri cu coloane calculate
Este posibil să se utilizeze coloane calculate în indexuri, deși acestea trebuie să îndeplinească mai multe cerințe:
- Proprietate: Toate funcțiile care sunt utilizate în definirea coloanei calculate trebuie să fie deținute de același utilizator ca și tabelul.
- Determinism: Coloana calculată trebuie să fie deterministă. De asemenea, dacă coloana conține expresii CLR, pe lângă faptul că trebuie să fie deterministă, coloana trebuie să fie persistă.
- Precizie: Expresia coloanei calculate trebuie să fie precisă. Aceasta implică faptul că nu poate fi de tipul de date „float” sau „real”. Nici nu puteți utiliza acest tip de date în definiție. Această caracteristică poate fi verificată cu ajutorul funcției columnproperty() prin specificarea proprietății IsPrecise.
- Tipul de date: Coloana calculată nu poate fi de tipul text, ntext sau imagine. De asemenea, în cazul în care expresia conține tipurile de date image, ntext, text, varchar (max), nvarchar (max), varbinary (max) sau xml, aceasta poate fi utilizată numai dacă tipul de date care rezultă din expresie este permis într-un index.
În plus față de aceste considerații, conexiunile utilizate pentru a crea coloana și cea utilizată pentru a crea indexul trebuie să aibă anumite configurații pentru a efectua aceste acțiuni.
Conexiunea pentru a crea coloana calculată trebuie să aibă opțiunea ANSI_NULLS activă. Acest lucru poate fi verificat cu ajutorul funcției columnproperty(), prin specificarea proprietății IsAnsiNullsOn.
Conexiunea pentru a crea indicele este, precum și conexiunile pentru a efectua inserarea, actualizarea și ștergerea înregistrărilor care influențează indicele trebuie să aibă opțiunile ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER active. În plus, opțiunea NUMERIC_ROUNDABORT trebuie să fie dezactivată.
Ultimele considerații
Pentru a încheia, vom trece în revistă câteva aspecte suplimentare pe care este necesar să le cunoaștem pentru utilizarea corectă a coloanelor calculate.
Evident, coloanele calculate nu pot fi actualizate și nici incluse în lista de valori a unei acțiuni INSERT. Deși, coloanele calculate pot face parte din lista de rezultate a unei instrucțiuni select, ele pot fi utilizate și în clauzele WHERE, ORDER BY, sau în toate cele în care se poate pune o expresie.
SELECT product, unit_price, quantity, total_amount FROM detail_lines WHERE total_amount > 10 ORDER BY total_amount
În ciuda celor de mai sus, o coloană calculată nu poate fi utilizată în definirea constrângerilor DEFAULT sau FOREIGN KEY. Nu poate fi nici cu o definiție a unei constrângeri NOT NULL.
Pe de altă parte, coloanele calculate pot fi utilizate ca parte a constrângerilor PRIMARY KEY sau UNIQUE. Pentru a face acest lucru, definiția coloanei calculate trebuie să fie o expresie deterministă.
Concluzie
Utilizarea coloanelor calculate poate fi foarte utilă în anumite situații. Trebuie să studiați cu atenție unde să le folosiți, din cauza restricțiilor pe care le au, în special pentru a crea indici și a le persista. Acesta este doar începutul. Simțiți-vă liberi să încercați lucruri noi și să experimentați cu coloanele calculate pentru a găsi noi posibilități.