Introduzione
Le colonne calcolate sono solo colonne virtuali il cui contenuto è il risultato di un’espressione. Di solito, sono usate per contenere dati basati sul resto delle colonne della tabella. L’espressione può contenere altre colonne non calcolate della tabella, costanti, operatori e funzioni, ma non è possibile specificare una query come espressione per una colonna calcolata.
Essendo colonne “virtuali”, non vengono memorizzate su disco come il resto della tabella. Infatti, non vengono memorizzate ma calcolate ogni volta che la colonna viene consultata in una query. Come vedrete, potete forzare SQL Server a memorizzare (“persistere”) la colonna nella tabella con alcune restrizioni.
Il modo migliore per capire come funzionano le colonne calcolate è usare degli esempi. Alla fine troverete un file contenente tutti gli script utilizzati nell’articolo, e ve ne mostreremo alcuni nel testo per illustrare le spiegazioni. Per iniziare, creeremo due tabelle: la prima per contenere le informazioni sulle fatture e l’altra con le righe di dettaglio di quelle fatture. Potete anche trovare alcuni inserimenti nel file di script per creare dati di esempio.
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));
Il modo di creare una colonna calcolata è lo stesso in cui creereste altre colonne in una tabella, con un’istruzione CREATE TABLE o ALTER TABLE. Per una colonna calcolata, sostituiamo il tipo di dati della colonna con l’espressione che sarà usata per ottenere il contenuto della colonna. La sintassi sarà il nome della colonna, seguito dalla parola chiave “as”, e poi l’espressione. Creiamo una colonna calcolata nella tabella detail_line per memorizzare l’importo totale della linea, che calcoliamo moltiplicando unit_price e quantity.
ALTER TABLE detail_lines ADD total_amount AS unit_price * quantity;
Controllo per una colonna calcolata
Ci sono diversi modi per confermare che una colonna è davvero una colonna calcolata. Uno di questi è usare la funzione columnproperty() specificando la proprietà “IsComputed”.
SELECT COLUMNPROPERTY(OBJECT_ID('dbo.detail_lines'),'total_amount','IsComputed')
Un altro modo per ottenere informazioni sulle colonne calcolate è attraverso la vista di sistema, sys.computed_columns. Questa vista è un’estensione della vista sys.columns. Ciò significa che sys.computed_columns eredita tutte le colonne dalla vista sys.columns e ne aggiunge altre che sono specifiche per questo tipo di colonna. Nel corso dell’articolo vedremo alcune delle colonne di questa vista, come vediamo diverse caratteristiche delle colonne calcolate. Per ora, è sufficiente sapere che questa vista mostra solo colonne calcolate e ha una colonna, chiamata is_computed, che dice se la colonna è calcolata o no. Ovviamente, tutti i record di questa vista avranno un uno in questa colonna.
SELECT name, is_computed FROM sys.computed_columns;
Poiché il contenuto della colonna è calcolato ogni volta che la colonna è referenziata in una query, il contenuto è sempre aggiornato. Qualsiasi cambiamento nelle colonne che sono incluse nell’espressione, si riflette automaticamente nel valore della colonna. Possiamo vedere questo cambiando la quantità in un registro nella tabella detail_lines di esempio e controllare il risultato.
UPDATE detail_lines SET quantity = 4 WHERE product = 'Cup'SELECT product, unit_price, quantity, total_amount FROM detail_lines WHERE product = 'Cup'
Un passo avanti
Quello che abbiamo visto finora con le colonne calcolate è molto elementare, poiché si tratta solo di eseguire calcoli con altre colonne. Tuttavia, l’espressione delle colonne calcolate può contenere funzioni, sia funzioni standard T-SQL che funzioni definite dall’utente (UDF). In questo modo, è possibile estendere la funzionalità di queste colonne molto di più.
Vediamo un esempio di questo. Stiamo per aggiungere una colonna calcolata alla tabella delle fatture che calcola l’importo totale della fattura. Per farlo, dobbiamo ottenere il numero della fattura e interrogare la tabella detail_lines per sommare il total_amount da ogni record con l’id della fattura. Il modo migliore per farlo è usare una funzione che riceve l’id della fattura come parametro e restituisce la somma. Dopo di che, dobbiamo creare la colonna che usa questa funzione.
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)
Possiamo verificare che questa colonna funziona correttamente aggiungendo un nuovo record nella tabella, detail_lines, così il total_bill dovrebbe cambiare.
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
Alterare la colonna
Ci potrebbero essere situazioni in cui è necessario modificare una colonna calcolata. Sfortunatamente, questo non è possibile. Per fare questa modifica, è necessario cancellare la colonna e ricrearla con la nuova espressione.
Nel caso in cui la colonna calcolata usi una funzione esterna, non ci sarà permesso di modificare questa funzione. Se ci proviamo, riceviamo un errore che indica che questa funzione è legata alla tabella. Per modificare la funzione, è necessario cancellare la colonna, eseguire la modifica della funzione e, infine, ricreare la colonna con la nuova versione della funzione.
Possiamo ottenere la definizione della colonna alla colonna “definition” dalla vista sys.computed_columns.
SEECT name, definition FROM sys.computed_columns
Memorizzare una colonna calcolata
Come abbiamo detto prima, queste colonne sono “virtuali” quindi non vengono fisicamente memorizzate nella tabella. Tuttavia, c’è la possibilità di forzare il calcolo ad essere fisicamente memorizzato nella tabella, il che è chiamato “persistere” della colonna. Questo può migliorare le prestazioni con le istruzioni SELECT poiché evita di dover eseguire il calcolo della colonna ogni volta che si fa riferimento ad essa.
Inoltre, per persistere la colonna, l’espressione usata per creare la colonna deve essere “deterministica”. Come possiamo vedere nel sito di Microsoft, “le funzioni deterministiche restituiscono sempre lo stesso risultato ogni volta che vengono chiamate con un insieme specifico di valori di input e dato lo stesso stato del database.” (https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions?view=sql-server-2017). Se vogliamo sapere se SQL Server considera l’espressione di una colonna calcolata come deterministica o no, possiamo usare la funzione columnproperty() con la proprietà “IsDeterministic”.
SELECT COLUMNPROPERTY(OBJECT_ID('dbo.detail_lines'),'total_amount','IsDeterministic')SELECT COLUMNPROPERTY(OBJECT_ID('dbo.invoices'),'total_invoice','IsDeterministic')
Se la definizione della colonna è una funzione definita dall’utente, potete anche verificare se la funzione stessa è deterministica o no. Per farlo, dovete usare la funzione objectproperty() con la proprietà IsDeterministic.
SELECT OBJCETPROPERTY(OBJECT_ID('dbo.fn_total_invoice'),'IsDeterministic')
Come potete vedere dalle query, la colonna del primo esempio, in cui calcoliamo il prezzo totale del dettaglio, è considerata deterministica. Tuttavia, la funzione che calcola il prezzo totale della fattura è considerata non deterministica. In questo modo, solo la colonna total_price della tabella detail_table può essere memorizzata nella tabella.
ALTER TABLE detail_lines DROP COLUMN total_amount;ALTER TABLE detail_lines ADD total_amount AS unit_price * quantity PERSISTED;
Sempre nella vista sys.computed_columns potete vedere il campo is_persisted, che indicherà se la colonna è persistita o meno nella tabella.
Indici con colonne calcolate
È possibile usare colonne calcolate negli indici, anche se devono soddisfare diversi requisiti:
- Proprietà: Tutte le funzioni utilizzate nella definizione della colonna calcolata devono essere di proprietà dello stesso utente della tabella.
- Determinismo: La colonna calcolata deve essere deterministica. Inoltre, se la colonna contiene espressioni CLR, oltre ad essere deterministica, la colonna deve essere persistente.
- Precisione: L’espressione della colonna calcolata deve essere precisa. Ciò implica che non può essere del tipo di dati “float” o “real”. Né potete usare questo tipo di dati nella vostra definizione. Questa caratteristica può essere verificata con la funzione columnproperty() specificando la proprietà IsPrecise.
- Tipo di dati: La colonna calcolata non può essere dei tipi text, ntext o image. Inoltre, se l’espressione contiene tipi di dati image, ntext, text, varchar (max), nvarchar (max), varbinary (max), o xml, può essere usata solo se il tipo di dati risultante dall’espressione è permesso in un indice.
Oltre a queste considerazioni, le connessioni usate per creare la colonna e quella usata per creare l’indice devono avere certe configurazioni per poter eseguire queste azioni.
La connessione per creare la colonna calcolata deve avere l’opzione ANSI_NULLS attiva. Questo può essere verificato con la funzione columnproperty(), specificando la proprietà IsAnsiNullsOn.
La connessione per creare l’indice è, così come le connessioni per eseguire insert, update e delete dei record che influenzano l’indice devono avere le opzioni ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER attive. Inoltre, l’opzione NUMERIC_ROUNDABORT deve essere disabilitata.
Ultime considerazioni
Per finire, passiamo in rassegna alcuni aspetti aggiuntivi che è necessario conoscere per il corretto utilizzo delle colonne calcolate.
Ovviamente, le colonne calcolate non possono essere aggiornate, né incluse nella lista dei valori di un’azione INSERT. Anche se le colonne calcolate possono far parte della lista dei risultati di una dichiarazione di selezione, possono anche essere usate nelle clausole WHERE, ORDER BY, o in tutte quelle in cui può essere inserita un’espressione.
SELECT product, unit_price, quantity, total_amount FROM detail_lines WHERE total_amount > 10 ORDER BY total_amount
Nonostante ciò, una colonna calcolata non può essere usata nella definizione del vincolo DEFAULT o FOREIGN KEY. Né può essere con una definizione di vincolo NOT NULL.
D’altra parte, le colonne calcolate possono essere usate come parte di vincoli PRIMARY KEY o UNIQUE. Per fare questo, la definizione della colonna calcolata dovrebbe essere un’espressione deterministica.
Conclusione
L’uso delle colonne calcolate può essere molto utile in alcune situazioni. Bisogna studiare attentamente dove usarle, a causa delle restrizioni che hanno, specialmente per creare indici e persistere. Questo è solo l’inizio. Sentiti libero di provare nuove cose e sperimentare con le colonne calcolate per trovare nuove possibilità.