Narcis Miclaus

8 min di lettura

SQL Server, dalle fondamenta Lezione 17 / 40

INSERT, UPDATE, DELETE, e perché MERGE è pericoloso

I quattro verbi DML, i pattern UPSERT che funzionano davvero, la clausola OUTPUT, e la lunga lista di motivi per evitare MERGE.

Il DML — Data Manipulation Language — è l’altra metà di SQL. Finora abbiamo letto. Ora scriviamo. INSERT, UPDATE, DELETE, e il controverso MERGE. Più la clausola OUTPUT, che trasforma uno qualsiasi di loro in un’operazione “dimmi cosa hai cambiato”.

Runehold scrive dati ogni secondo di ogni giorno. Nuove iscrizioni clienti, nuovi ordini, aggiustamenti di inventario, aggiornamenti di stato delle spedizioni, resi, rimborsi. Come si fa tutto questo — atomicamente, in sicurezza, e con la giusta tracciabilità — è il tema di oggi.

INSERT

Una singola riga

INSERT INTO Sales.Customer (Name, Email, Country)
VALUES (N'Maria Rossi', N'maria@example.it', 'IT');

Lista delle colonne esplicita, clausola VALUES esplicita. Puoi omettere la lista delle colonne e inserire per posizione (corrispondendo alla definizione della tabella), ma è fragile — aggiungere una colonna in futuro rompe silenziosamente ogni query. Elenca sempre le colonne.

Più righe

INSERT INTO Sales.Customer (Name, Email, Country)
VALUES
    (N'Maria Rossi',   N'maria@example.it',  'IT'),
    (N'Hans Schmidt',  N'hans@example.de',   'DE'),
    (N'Liesbeth Jong', N'liesbeth@nl.com',   'NL');

Una singola istruzione, tre righe. Più veloce di tre INSERT separate perché SQL Server lo registra come una singola operazione.

Insert da una SELECT

-- Copia i clienti in una tabella di backup
INSERT INTO Sales.Customer_Backup (CustomerId, Name, Email, Country, BackedUpAt)
SELECT CustomerId, Name, Email, Country, SYSUTCDATETIME()
FROM Sales.Customer;

-- Migra i dati di staging in produzione
INSERT INTO Sales.Customer (Name, Email, Country)
SELECT Name, Email, Country
FROM Staging.CustomerImport
WHERE IsValid = 1;

Il pattern di bulk-loading più comune. Sii esplicito sulle colonne da entrambi i lati.

Ottenere l’identity della riga inserita

-- Vecchia maniera: SCOPE_IDENTITY()
INSERT INTO Sales.Orders (CustomerId, Total, CountryCode, VatRate)
VALUES (@cust, @total, @country, @vat);
DECLARE @newId BIGINT = SCOPE_IDENTITY();

-- Maniera moderna: OUTPUT
INSERT INTO Sales.Orders (CustomerId, Total, CountryCode, VatRate)
OUTPUT INSERTED.OrderId, INSERTED.OrderDate
VALUES (@cust, @total, @country, @vat);

OUTPUT INSERTED.* restituisce le righe appena inserite. Funziona anche per i bulk insert — ottieni una riga di output per ogni riga inserita. Utile per restituire i nuovi ID all’applicazione, o per scrivere in una tabella di audit nella stessa istruzione.

UPDATE

Update semplice

UPDATE Sales.Customer
SET    Email = N'maria.new@example.it',
       UpdatedAt = SYSUTCDATETIME(),
       UpdatedBy = SUSER_SNAME()
WHERE  CustomerId = 12345;

Metti sempre una clausola WHERE. Dimenticarla aggiorna ogni riga della tabella. È l’errore disastroso più comune in SQL. Prima di eseguire qualsiasi UPDATE o DELETE faccio due cose:

  1. Copio l’istruzione, sostituisco UPDATE ... SET ... con SELECT *, e verifico che il WHERE restituisca le righe che mi aspetto.
  2. Avvolgo l’istruzione vera in BEGIN TRAN; ...; ROLLBACK;, controllo i row count, e committo solo quando sono sicuro.

Le abitudini paranoiche salvano carriere.

Update con un JOIN

-- Aggiorna il country code sugli ordini in base al paese del cliente
UPDATE o
SET o.CountryCode = c.Country
FROM Sales.Orders AS o
JOIN Sales.Customer AS c ON c.CustomerId = o.CustomerId
WHERE o.CountryCode IS NULL;

Sintassi strana, specifica di SQL Server: UPDATE <alias> seguito da SET e poi FROM. È lo standard de facto per T-SQL e nessuno lo mette in discussione in code review.

OUTPUT su UPDATE

Stesso pattern di INSERT:

UPDATE Sales.Orders
SET Status = 2
OUTPUT INSERTED.OrderId, INSERTED.Status AS new_status, DELETED.Status AS old_status
WHERE Status = 1 AND OrderDate < DATEADD(DAY, -7, GETDATE());

INSERTED contiene la riga dopo l’update, DELETED la contiene prima. Perfetto per gli audit log: “chi ha cambiato cosa, da cosa a cosa.”

DELETE

DELETE FROM Sales.Orders
WHERE Status = 9 AND OrderDate < DATEADD(YEAR, -7, GETDATE());

Stesso avvertimento: clausola WHERE o cancelli tutto.

TRUNCATE TABLE è più veloce di un DELETE senza WHERE, ma droppa temporaneamente le foreign key (lo abbiamo coperto nella lezione 5). TRUNCATE se vuoi tutte le righe via e la tabella vuota; DELETE ... WHERE per tutto il resto.

DELETE con un JOIN

-- Cancella gli ordini il cui cliente non esiste più (se la FK lo permette)
DELETE o
FROM Sales.Orders AS o
LEFT JOIN Sales.Customer AS c ON c.CustomerId = o.CustomerId
WHERE c.CustomerId IS NULL;

Stessa sintassi a due parti dell’UPDATE. Funziona pulita una volta che ci si è abituati.

OUTPUT su DELETE

-- Archivia gli ordini prima di cancellarli
DELETE FROM Sales.Orders
OUTPUT DELETED.*
INTO Sales.Orders_Archive
WHERE OrderDate < DATEADD(YEAR, -7, GETDATE());

OUTPUT ... INTO <table> reindirizza l’output direttamente in un’altra tabella. Cancellazione + archiviazione atomica in una sola istruzione. Per i flussi di data retention, questo pattern è oro.

Il problema dell’UPSERT

“Inserisci se non esiste, altrimenti aggiorna.” Ogni applicazione ha bisogno di questo. La soluzione semplice sembra sbagliata ma in realtà è giusta:

-- Il pattern "IF EXISTS", sicuro e leggibile
IF EXISTS (SELECT 1 FROM Sales.Customer WHERE Email = @email)
BEGIN
    UPDATE Sales.Customer
    SET    Name = @name, UpdatedAt = SYSUTCDATETIME()
    WHERE  Email = @email;
END
ELSE
BEGIN
    INSERT INTO Sales.Customer (Name, Email, Country)
    VALUES (@name, @email, @country);
END;

Semplice, esplicito, sicuro. Avvolgilo in una transazione e hai finito.

Problema: sotto insert concorrenti, due sessioni potrebbero entrambe superare il check IF EXISTS e provare entrambe a fare INSERT, una prende una violazione di unique constraint. Per tabelle a basso traffico, va bene. Per tabelle calde, aggiungi HOLDLOCK:

BEGIN TRAN;

    IF EXISTS (SELECT 1 FROM Sales.Customer WITH (UPDLOCK, HOLDLOCK) WHERE Email = @email)
        UPDATE Sales.Customer SET ... WHERE Email = @email;
    ELSE
        INSERT INTO Sales.Customer (Name, Email, Country) VALUES (@name, @email, @country);

COMMIT;

UPDLOCK, HOLDLOCK garantisce che nessun altro possa intrufolarsi tra il check EXISTS e l’insert. Tratteremo il bloccaggio per bene nella lezione 20.

Alternativa: lascia che la unique constraint faccia il lavoro e cattura l’errore:

BEGIN TRY
    INSERT INTO Sales.Customer (Name, Email, Country) VALUES (@name, @email, @country);
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() = 2627   -- violazione di unique constraint
        UPDATE Sales.Customer SET Name = @name WHERE Email = @email;
    ELSE
        THROW;
END CATCH;

Ottimistico: prova prima l’insert, ricadi sull’update se collide. Va bene per tabelle dove le collisioni sono rare.

MERGE: perché è pericoloso

MERGE prova a fare l’UPSERT in una sola istruzione:

MERGE INTO Sales.Customer AS target
USING (SELECT @email AS Email, @name AS Name, @country AS Country) AS src
ON target.Email = src.Email
WHEN MATCHED THEN
    UPDATE SET Name = src.Name, UpdatedAt = SYSUTCDATETIME()
WHEN NOT MATCHED THEN
    INSERT (Name, Email, Country) VALUES (src.Name, src.Email, src.Country);

Elegante, conciso, sconsigliato.

La lista dei motivi per evitare MERGE in SQL Server:

  • Diversi bug documentati in cui MERGE restituisce risultati sbagliati o silenziosamente non fa nulla. Aaron Bertrand tiene una lista aggiornata da 15 anni. Alcuni sono stati corretti; altri persistono.
  • Problemi di concorrenza peggiori che con IF EXISTS + INSERT/UPDATE.
  • Interagisce male con indici filtrati, viste indicizzate, e trigger INSTEAD OF.
  • La clausola WHEN NOT MATCHED BY SOURCE può cancellare silenziosamente più di quanto ti aspetti.
  • Più difficile da leggere e debuggare del pattern esplicito IF/ELSE.

Persino le linee guida di Microsoft hanno tranquillamente de-enfatizzato MERGE. Se lo vedi nel codice esistente, capisci cosa fa ma non ricorrervi nel codice nuovo. I pattern sicuri sono IF EXISTS + INSERT/UPDATE o INSERT + try/catch.

Se proprio devi usare MERGE (magari per uno script ETL una tantum), leggi prima la lista dei bug esistenti. Non sto scherzando.

Update batchati per tabelle grandi

Eseguire UPDATE Orders SET Status = 9 WHERE OrderDate < ... su una tabella con 200 milioni di righe coincidenti bloccherà la tabella, riempirà il log, e farà aspettare ogni utente.

Batchalo:

WHILE 1 = 1
BEGIN
    UPDATE TOP (10000) Sales.Orders
    SET Status = 9
    WHERE Status = 1 AND OrderDate < '2020-01-01';

    IF @@ROWCOUNT = 0 BREAK;

    -- Lascia respirare le altre query
    WAITFOR DELAY '00:00:00.100';
END;

10.000 righe per batch, pausa di 100ms. Transazioni piccole, picchi di log piccoli, nessuna lock escalation a livello tabella. Stesso pattern per le delete grandi.

Per le delete che riguardano la maggior parte delle righe, un altro approccio: crei una nuova tabella con solo le righe che vuoi tenere, droppi la vecchia, rinomini la nuova. Spesso più veloce che cancellare riga per riga.

La clausola OUTPUT al completo

Riassunto degli usi di OUTPUT:

-- Restituire i cambiamenti al client
INSERT INTO ... OUTPUT INSERTED.Id ... VALUES ...;
UPDATE ...    OUTPUT DELETED.OldVal, INSERTED.NewVal WHERE ...;
DELETE FROM ... OUTPUT DELETED.* WHERE ...;

-- Reindirizzare i cambiamenti in una tabella (per audit, archivio, ecc.)
UPDATE ...
OUTPUT DELETED.*, INSERTED.*, SUSER_SNAME() AS changed_by, SYSUTCDATETIME() AS changed_at
INTO Audit.CustomerChanges
WHERE ...;

Tutto ciò che vedi in DELETED. e INSERTED. è valido. Un caso d’uso comune: una tabella di audit che cattura prima/dopo di ogni cambiamento, scritta dalla stessa istruzione che ha fatto il cambiamento.

Esegui questo sulla tua macchina

USE Runehold;
GO

-- 1. INSERT base con più righe
INSERT INTO Sales.Customer (Name, Country)
VALUES
    (N'Elena García', 'ES'),
    (N'Pierre Dupont', 'FR'),
    (N'Lars Hansen',  'SE');

-- 2. UPDATE con JOIN + OUTPUT
UPDATE o
SET o.Notes = CONCAT(o.Notes, N'[' + c.Country + N']')
OUTPUT INSERTED.OrderId, DELETED.Notes AS old_notes, INSERTED.Notes AS new_notes
FROM Sales.Orders AS o
JOIN Sales.Customer AS c ON c.CustomerId = o.CustomerId
WHERE o.Notes IS NULL OR o.Notes NOT LIKE N'%[%]%';

-- 3. DELETE con OUTPUT verso una tabella di archivio
IF OBJECT_ID('Sales.Orders_Archive', 'U') IS NULL
BEGIN
    SELECT TOP 0 * INTO Sales.Orders_Archive FROM Sales.Orders;
END;

DELETE FROM Sales.Orders
OUTPUT DELETED.*
INTO Sales.Orders_Archive
WHERE OrderDate < '2026-01-01';

SELECT COUNT(*) FROM Sales.Orders_Archive;

-- 4. UPSERT alla maniera sicura, dentro una transazione
DECLARE @email NVARCHAR(200) = N'newcomer@example.com';
DECLARE @name  NVARCHAR(100) = N'Newcomer One';
DECLARE @country CHAR(2)     = 'PT';

BEGIN TRAN;
    IF EXISTS (
        SELECT 1 FROM Sales.Customer WITH (UPDLOCK, HOLDLOCK) WHERE Name = @name
    )
        UPDATE Sales.Customer
        SET Country = @country
        WHERE Name = @name;
    ELSE
        INSERT INTO Sales.Customer (Name, Country) VALUES (@name, @country);
COMMIT;

-- 5. Pattern di update batchato (gira una sola volta perché i dati sono pochi)
WHILE 1 = 1
BEGIN
    UPDATE TOP (100) Sales.Orders
    SET Notes = COALESCE(Notes, N'') + N' '
    WHERE Notes IS NULL OR Notes NOT LIKE '% ';

    IF @@ROWCOUNT = 0 BREAK;
END;

Ogni istruzione DML dovrebbe essere qualcosa che eseguiresti davanti al tuo team. “Aggiorno il record di tutti” non è mai l’obiettivo; “aggiorno le 47 righe che corrispondono a questo filtro” sì. Conosci sempre il row count prima di committare.

Prossima lezione: transazioni e ACID. Cosa significa davvero “atomico” quando il server crasha a metà commit, e perché la tua abitudine al BEGIN TRAN + COMMIT è la migliore amica del DBA.

Cerca