T-SQL avanzato: tecniche da ricordare

In SQL Server, padroneggiare le tecniche avanzate di T-SQL è fondamentale per sviluppare applicazioni database efficienti e manutenibili. In questo articolo approfondito, esploreremo alcune delle funzionalità più potenti di T-SQL, con esempi pratici e spiegazioni dettagliate. Per approfondimenti e altri articoli tecnici su SQL Server, vi invito a visitare il mio sito su SQL Server www.nicolaiantomasi.com.

Gestione Avanzata dei NULL nelle JOIN

Uno degli aspetti più sottovalutati di T-SQL è la corretta gestione dei NULL nelle operazioni di JOIN. Consideriamo un esempio pratico:

CREATE TABLE dbo.Magazzino(
    Codice1 VARCHAR(5),
    Codice2 VARCHAR(5),
    Nome VARCHAR(50)
);

CREATE TABLE dbo.Prezzario(
    Codice1 VARCHAR(5),
    Codice2 VARCHAR(5),
    Prezzo DECIMAL(18,4)
);

-- Dati di esempio con NULL intenzionali
INSERT INTO dbo.Magazzino VALUES 
    ('A1', 'B1', 'Prodotto1'),
    ('A1', 'B2', 'Prodotto2'),
    ('A2', NULL, 'Prodotto3');

INSERT INTO dbo.Prezzario VALUES 
    ('A1', 'B1', 3.24),
    ('A2', NULL, 1.4);`

Una comune svista è pensare che questa query funzioni correttamente:

SELECT * 
FROM dbo.Magazzino m
INNER JOIN dbo.Prezzario p 
    ON m.Codice1 = p.Codice1 
    AND m.Codice2 = p.Codice2;

Il problema? In SQL, NULL = NULL restituisce NULL, non TRUE. La soluzione corretta è:

SELECT * 
FROM dbo.Magazzino m
INNER JOIN dbo.Prezzario p 
    ON m.Codice1 = p.Codice1 
    AND (m.Codice2 = p.Codice2 OR 
        (m.Codice2 IS NULL AND p.Codice2 IS NULL));

Questa query gestisce correttamente i casi in cui Codice2 è NULL in entrambe le tabelle, un requisito comune in applicazioni reali.

Window Functions: Oltre il GROUP BY

Le Window Functions sono uno degli strumenti più potenti di T-SQL moderno. Permettono calcoli sofisticati mantenendo il dettaglio dei dati originali. Vediamo alcuni esempi pratici:
Calcolo di Totali Progressivi

WITH Vendite AS (
    SELECT * FROM (VALUES
        ('2024-01-01', 1000),
        ('2024-01-02', 1500),
        ('2024-01-03', 800)
    ) v(Data, Importo)
)
SELECT 
    Data,
    Importo,
    SUM(Importo) OVER (
        ORDER BY Data 
    ) AS TotaleProgressivo,
    SUM(Importo) OVER () AS TotaleComplessivo,
    Importo * 100.0 / SUM(Importo) OVER () AS PercentualeSuTotale
FROM Vendite;

Questa query mostra diverse funzionalità:

• Calcolo del totale progressivo giorno per giorno
• Calcolo del totale complessivo per confronto
• Calcolo della percentuale sul totale

La clausola OVER è fondamentale: definisce la "finestra" su cui operare. ROWS BETWEEN specifica l'intervallo di righe da considerare.
Analisi Trend con LAG e LEAD

SELECT 
    Data,
    Importo,
    LAG(Importo) OVER (ORDER BY Data) AS ImportoPrecedente,
    LEAD(Importo) OVER (ORDER BY Data) AS ImportoSuccessivo,
    Importo - LAG(Importo) OVER (ORDER BY Data) AS Differenza,
    CASE 
        WHEN LAG(Importo) OVER (ORDER BY Data) IS NULL THEN NULL
        ELSE (Importo - LAG(Importo) OVER (ORDER BY Data)) * 100.0 / 
             LAG(Importo) OVER (ORDER BY Data)
    END AS VariazionePercentuale
FROM Vendite;

Questo codice illustra diverse funzionalità per l'analisi dei dati in sequenza temporale. In particolare, dimostra come la funzione LAG ci permetta di accedere ai valori delle righe precedenti nella sequenza, mentre LEAD ci consente di vedere i valori delle righe successive. Grazie a queste funzioni, possiamo facilmente calcolare sia le variazioni assolute che quelle percentuali tra diversi punti temporali dei nostri dati.

Ranking e Partitioning

WITH VenditeClienti AS (
    SELECT * FROM (VALUES
        ('A', '2024-01-01', 1000),
        ('A', '2024-01-02', 1500),
        ('B', '2024-01-01', 800),
        ('B', '2024-01-02', 800)
    ) v(Cliente, Data, Importo)
)
SELECT 
    Cliente,
    Data,
    Importo,
    ROW_NUMBER() OVER (PARTITION BY Cliente ORDER BY Importo DESC) AS RigaNum,
    RANK() OVER (PARTITION BY Cliente ORDER BY Importo DESC) AS Rank,
    DENSE_RANK() OVER (PARTITION BY Cliente ORDER BY Importo DESC) AS DenseRank
FROM VenditeClienti;

Questo esempio mostra diversi modi di numerare e classificare i dati all'interno di partizioni. Innanzitutto, utilizzando PARTITION BY possiamo suddividere i nostri dati in sezioni separate per ogni cliente. Per quanto riguarda la numerazione, ROW_NUMBER ci fornisce una sequenza di numeri senza duplicati, anche se dobbiamo fare attenzione poiché in caso di valori identici l'ordine potrebbe non essere deterministico. Quando abbiamo bisogno di gestire i pari merito, possiamo utilizzare RANK, che lascerà dei "vuoti" nella numerazione in questi casi, oppure DENSE_RANK che invece mantiene una sequenza continua senza interruzioni.

Pivot Dinamiche con SQL Dinamico

Un uso avanzato di T-SQL è la creazione di pivot dinamiche. Invece di codificare staticamente le colonne:

DECLARE @cols NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

SELECT @cols = STRING_AGG(QUOTENAME(Categoria), ',')
FROM (SELECT DISTINCT Categoria FROM Vendite) AS cats;

SET @sql = N'
SELECT *
FROM (
    SELECT Anno, Categoria, Importo
    FROM Vendite
) p
PIVOT (
    SUM(Importo) 
    FOR Categoria IN (' + @cols + ')
) AS pvt;';

EXEC sp_executesql @sql;

Punti chiave:

1. QUOTENAME protegge da SQL injection
2. STRING_AGG concatena i valori (sostituisce il vecchio XML PATH)
3. sp_executesql esegue SQL dinamico in modo sicuro

Il T-SQL avanzato richiede una comprensione profonda di come SQL Server elabora i dati. L'uso corretto di queste tecniche può migliorare significativamente sia la qualità che la manutenibilità del codice.