Risposta.

L'archiviazione dei dati è una delle compiti più importanti per i sistemi OLTP ad alta intensità con grandi tabelle. Storicamente, i primi tentativi di implementare ciò sono stati estremamente semplici: i dati venivano eliminati o copiati manualmente in tabelle separate tramite script o applicazioni. In seguito sono emersi approcci più sistematici, che considerano l'integrità delle transazioni e il minimo impatto sul lavoro principale del database.

Il problema qui non è solo nel trasferimento fisico delle informazioni, ma anche nel mantenimento della coerenza, nella minimizzazione dei blocchi e nel garantire alte prestazioni. Errori durante l'archiviazione possono portare a perdita di dati, blocco degli utenti o un aumento significativo del carico.

La soluzione consiste nell'utilizzare operazioni batch con controllo delle transazioni, nonché nella creazione di tabelle di archiviazione speciali con strutture identiche, o nell'automazione tramite pianificatori di attività e procedure.

Esempio di codice:

-- Trasferiamo 5000 record più vecchi di un anno nella tabella di archivio
INSERT INTO archive_orders SELECT * FROM orders WHERE order_date < DATEADD(year, -1, GETDATE())
    AND id IN (SELECT TOP 5000 id FROM orders WHERE order_date < DATEADD(year, -1, GETDATE()) ORDER BY id);
DELETE FROM orders WHERE id IN (SELECT TOP 5000 id FROM orders WHERE order_date < DATEADD(year, -1, GETDATE()) ORDER BY id);

Caratteristiche chiave:

Trasferimento di dati obsoleti attraverso batch per ridurre il carico.
Coordinamento delle operazioni di inserimento e cancellazione tramite transazioni.
Pianificazione dell'automazione tramite programmi di lavori e procedure.

Domande insidiose.

Qual è il rischio di un DELETE massiccio di record obsoleti e come evitarlo?

Un DELETE massiccio può portare a escalation di blocchi e rallentare l'intero database. Ciò può essere evitato eseguendo la cancellazione in piccole porzioni all'interno di un ciclo o utilizzando LIMIT/TOP, se supportato dal DBMS.

WHILE 1=1
BEGIN
  DELETE TOP (1000) FROM orders WHERE order_date < '2023-01-01';
  IF @@ROWCOUNT = 0 BREAK;
END

È possibile utilizzare TRUNCATE per eliminare i dati da archiviare?

TRUNCATE elimina tutte le righe nella tabella e non è adatto per la cancellazione condizionale di righe specifiche. Non attiva i trigger, non supporta WHERE e viene utilizzato solo per la pulizia completa, non per l'archiviazione selettiva.

Come garantire che il trasferimento sia avvenuto correttamente se l'eliminazione e l'inserimento avvengono in transazioni diverse?

È meglio eseguire il trasferimento di righe in una sola transazione: prima copiamo nell'archivio, poi eliminiamo dalla principale. Altrimenti, si potrebbe ottenere incoerenza in caso di errore tra le operazioni.

BEGIN TRANSACTION
INSERT INTO archive_orders SELECT * FROM orders WHERE ...
DELETE FROM orders WHERE ...
COMMIT

Errori comuni e anti-patterns

Eliminazione di enormi insiemi di dati con una sola query, causando blocchi.
Archiviazione senza verifica che tutte le righe siano state effettivamente trasferite.
Utilizzo di TRUNCATE invece di DELETE — porta alla perdita di tutti i dati della tabella.

Esempio dalla vita reale

Caso Negativo

Un ingegnere esegue uno script su un milione di record DELETE FROM logs WHERE event_date < '2022-01-01' durante l'orario lavorativo.

Vantaggi:

Lo script è semplice

Svantaggi:

La tabella è bloccata, gli utenti non possono lavorare, il processo dura ore, non è possibile annullare senza un backup.

Caso Positivo

Piano per trasferire 5000 righe tramite una procedura memorizzata e un lavoro pianificato di notte, con registrazione del successo di ogni porzione.