DEV Community

Cover image for T-SQL avanzato: tecniche da ricordare
Nicola Iantomasi
Nicola Iantomasi

Posted on

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);`
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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));
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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.

API Trace View

How I Cut 22.3 Seconds Off an API Call with Sentry 🕒

Struggling with slow API calls? Dan Mindru walks through how he used Sentry's new Trace View feature to shave off 22.3 seconds from an API call.

Get a practical walkthrough of how to identify bottlenecks, split tasks into multiple parallel tasks, identify slow AI model calls, and more.

Read more →

Top comments (0)

Billboard image

Try REST API Generation for Snowflake

DevOps for Private APIs. Automate the building, securing, and documenting of internal/private REST APIs with built-in enterprise security on bare-metal, VMs, or containers.

  • Auto-generated live APIs mapped from Snowflake database schema
  • Interactive Swagger API documentation
  • Scripting engine to customize your API
  • Built-in role-based access control

Learn more

👋 Kindness is contagious

Engage with a sea of insights in this enlightening article, highly esteemed within the encouraging DEV Community. Programmers of every skill level are invited to participate and enrich our shared knowledge.

A simple "thank you" can uplift someone's spirits. Express your appreciation in the comments section!

On DEV, sharing knowledge smooths our journey and strengthens our community bonds. Found this useful? A brief thank you to the author can mean a lot.

Okay