Da: Atif Shehzad | Aggiornato: 2011-08-29 | Commenti (8) | Correlati: Più > T-SQL
Webinar gratuito MSSQLTips: Migliori pratiche di sviluppo per SQL Server
Partecipa a questo webinar per imparare le migliori pratiche di sviluppo per SQL Server. Andy Warren condividerà i suoi molti anni di esperienza per dare alcuni suggerimenti su ciò che ha funzionato meglio per lui e come potete utilizzare alcune di queste conoscenze.
Problema
Ho usato colonne calcolate in scenari semplici e queste stanno funzionando bene. Tuttavia in alcuni casi stiamo affrontando delle limitazioni mentre implementiamo la logica di business attraverso le colonne calcolate. Per esempio ci viene richiesto di avere diversi valori basati su un’espressione separata per determinare i valori calcolati. Inoltre, c’è forse la possibilità di un errore di divisione per zero che deve essere evitato. Ci viene anche richiesto di accedere a colonne al di fuori della tabella delle colonne calcolate per usarle nell’espressione della colonna calcolata. In questo suggerimento vedremo come rendere le colonne calcolate più flessibili.
Soluzione
Le colonne calcolate possono aggiungere grande flessibilità nella progettazione del database. È possibile applicare condizionatamente l’espressione di calcolo, gestendo l’errore diviso per zero e accedendo a qualsiasi colonna al di fuori della tabella della colonna calcolata. Questo renderebbe la colonna calcolata più flessibile e comoda per la progettazione del vostro database. Di seguito sono elencati alcuni scenari che tratteremo in questo suggerimento.
- Calcolo condizionale dei valori
- Errore diviso per zero
- Accesso a una colonna al di fuori della tabella della colonna calcolata
Useremo semplici tecniche T-SQL per risolvere i problemi sopra menzionati. Questo dimostrerà la flessibilità e la capacità delle colonne calcolate di gestire tali problemi.
Calcolo condizionale dei valori
Abbiamo una semplice tabella con alcune colonne relative agli impiegati. C’è anche una colonna calcolata che calcola il pensionamento a 60 anni. Abbiamo un nuovo requisito per impostare il limite di età a 65 anni per i manager, ma tenerlo a 60 anni per tutti gli altri dipendenti.
Possiamo realizzare questo utilizzando una formula condizionale con un’istruzione CASE come mostrato nel seguente codice. Per la colonna DORetirement se la designazione è “Manager” allora la pensione sarà 65 altrimenti sarà 60.
-- Script# 1: Computed column with conditional formula-- Use sample databaseUSE GO -- Create Table with computed columnIF OBJECT_ID('CCtest', 'U') IS NOT NULL DROP TABLE .GO CREATE TABLE . ( INT NOT NULL, VARCHAR(50) NOT NULL, DATETIME NOT NULL, AS CASE WHEN designation = 'Manager' THEN (DATEADD(YEAR,(65),)) ELSE (DATEADD(YEAR,(60),)) END)GO --Insert sample data INSERT INTO CCTest (empNumb, Designation, DOBirth) SELECT 84, 'DBA', '1985-12-13' UNION ALLSELECT 85, 'DBA', '1980-11-18' UNION ALLSELECT 86, 'Manager', '1978-01-19' UNION ALLSELECT 88, 'Manager', '1985-12-13' UNION ALLSELECT 90, 'Developer', '1975-07-23' GO -- Check the required functionality in resultSELECT Designation, datediff(yy,dobirth,doretirement ) AgeLimit, DOBirth, DORetirement FROM CCTestGO
Se si esegue il codice di cui sopra si dovrebbe ottenere un output come il seguente. Usando un’istruzione CASE, possiamo definire espressioni separate a seconda dei criteri forniti.
Errore di divisione per zero
Nell’espressione di calcolo, se stiamo dividendo, ci può essere la possibilità di avere uno zero nel denominatore. In questi casi corriamo il rischio di avere un errore a causa di un divisore per zero.
Consideriamo uno scenario in cui abbiamo una colonna calcolata con formula AS (numeratore/denominatore) dove le colonne numeratore e denominatore sono utilizzate per la colonna calcolata. In questo caso un errore di divisione per zero verrebbe riscontrato ogni volta che l’espressione della colonna calcolata viene calcolata con uno zero nella colonna del denominatore.
Possiamo evitare questo errore sostituendo lo zero nel denominatore con un valore NULL usando la funzione NULLIF come mostrato di seguito. Il quarto record che inseriamo nella tabella causerebbe un problema di divisione per zero, ma la funzione NULLIF lo converte in un NULL.
-- Script# 2: Avoiding divided by zero error-- Use sample databaseUSE GO -- Create Table with computed columnIF OBJECT_ID('CCtest', 'U') IS NOT NULL DROP TABLE CCtestGOCREATE TABLE . ( int NOT NULL, int NOT NULL, AS (Numerator/NULLIF(Denominator,0)) )GO--Insert sample dataINSERT INTO CCTest (Numerator, Denominator) SELECT 840, 12 UNION ALLSELECT 805, 6 UNION ALLSELECT 846, 3 UNION ALLSELECT 88, 0 UNION ALLSELECT 90, 15GO-- Check the resultSELECT * from CCTestGO
Non potendo dividere per NULL il valore restituito è NULL per questa colonna per questo record.
Per default una colonna calcolata permette valori NULL. Tuttavia è possibile specificare esplicitamente NOT NULL solo con le colonne calcolate persistenti.
Accedere a una colonna al di fuori della tabella della colonna calcolata
Una colonna calcolata non può accedere direttamente a nessuna colonna al di fuori della sua tabella. Questa limitazione può essere superata usando una funzione definita dall’utente. Una UDF può essere usata nell’espressione per accedere a qualsiasi colonna al di fuori della tabella delle colonne calcolate.
Nello script qui sotto, viene creata una UDF per calcolare il saldo delle ferie del dipendente per mostrare quanti giorni di ferie rimangono a un dipendente oltre il massimo di 20 giorni. Questi dati provengono da una tabella secondaria chiamata LeaveBalance.
--Script # 3: Use UDF to access column in other table-- Use sample databaseUSE GO -- Create Table to reference in UDFIF OBJECT_ID('LeaveBalance', 'U') IS NOT NULL DROP TABLE LeaveBalanceGOCREATE TABLE . ( INT NOT NULL, TINYINT NOT NULL, )GO--Insert sample dataINSERT INTO LeaveBalanceSELECT 840, 12 UNION ALLSELECT 805, 6 UNION ALLSELECT 846, 13 UNION ALLSELECT 88, 7 UNION ALLSELECT 90, 15GO-- Create UDF to get leave balanceIF OBJECT_ID('UDF_GetLeaveBalance', 'FN') IS NOT NULL DROP FUNCTION UDF_GetLeaveBalanceGO-- Create UDF to use in computed columnCREATE FUNCTION UDF_GetLeaveBalance (@EmpNumb int)RETURNS TINYINTASBEGIN DECLARE @LeaveBalance TINYINT SELECT @LeaveBalance = (20 - LeavesAvailed) FROM LeaveBalance WHERE EmpNumb = @empnumb RETURN @LeaveBalanceENDGO-- Create Table to use computed columnIF OBJECT_ID('CCTest', 'U') IS NOT NULL DROP TABLE CCtestGOCREATE TABLE . ( INT NOT NULL, VARCHAR(50) NOT NULL, AS (.UDF_GetLeaveBalance(EmpNumb)) )GO--Insert sample dataINSERT INTO CCTest (EmpNumb, Designation) SELECT 840, 'DBA' UNION ALLSELECT 805, 'DBA' UNION ALLSELECT 846, 'Manager' UNION ALLSELECT 88, 'Manager' UNION ALLSELECT 90, 'Developer' GO-- Check the resultSELECT * from CCTestGO
Così sotto possiamo vedere il numero di giorni rimanenti disponibili per ogni dipendente.
Ci sono alcune considerazioni relative alle UDF quando una colonna calcolata deve essere usata in un indice. Potete saperne di più in questo suggerimento: Come creare indici su colonne calcolate in SQL Server.
Passi successivi
I problemi sopra menzionati e le loro soluzioni funzionano allo stesso modo per colonne calcolate persistenti o meno. Utilizzando la flessibilità disponibile delle colonne calcolate si può migliorare la progettazione del database. Le colonne calcolate sono una buona opzione da usare per implementare la logica di business attraverso le espressioni.
- Clicca qui per leggere il consiglio sulle basi del lavoro con le colonne calcolate
- Clicca qui per leggere il consiglio sulla creazione di indici sulle colonne calcolate
Ultimo aggiornamento: 2011-08-29
Informazioni sull’autore
Atif Shehzad è un appassionato DBA di SQL Server, revisore tecnico e autore di articoli.Vedi tutti i miei consigli
- Altri consigli per sviluppatori di database…