Narcis Miclaus

11 min di lettura

SQL Server, dalle fondamenta Lezione 5 / 40

CREATE, ALTER, DROP: fare e rompere le tabelle

Le basi del DDL, i pattern IF EXISTS, gli script idempotenti, e l'esercizio 'oh no ho droppato la prod'. Più come cambiare una colonna senza rompere l'app.

Ogni database inizia con un cursore lampeggiante e uno statement CREATE TABLE. Ogni database finisce come una lunga serie di statement ALTER TABLE scritti da gente che non aveva pianificato che il proprio schema cambiasse. E una volta ogni tanto, qualcuno esegue DROP TABLE nella finestra di query sbagliata e la giornata diventa una questione di curriculum.

Questa lezione è sul Data Definition Language — DDL — il sottoinsieme di SQL che crea, modifica, e distrugge oggetti del database. È il primo SQL che la maggior parte della gente impara e la parte in cui avvengono gli errori più catastrofici. Alla fine conoscerai i pattern sicuri, quelli non sicuri, e cosa fare se hai già fatto la scelta non sicura.

I tre grandi

  • CREATE — crea un nuovo oggetto (tabella, indice, vista, procedura, schema, database).
  • ALTER — modifica un oggetto esistente.
  • DROP — elimina un oggetto.

C’è anche TRUNCATE TABLE, che cancella tutte le righe ma tiene la tabella. È molto più veloce di DELETE perché è un’operazione di metadati che resetta semplicemente le pagine dati, e non logga ogni riga individualmente. Ne parliamo tra un attimo.

Ogni comando DDL in SQL Server gira dentro una transazione, esattamente come il DML. Significa che puoi fare BEGIN TRAN; DROP TABLE ...; ROLLBACK; e riavere la tua tabella. Più di una volta, questa singola funzionalità mi ha salvato la carriera. Impara a iniziare ogni sessione DDL ad-hoc con BEGIN TRAN e a finirla con COMMIT o ROLLBACK. La lezione 18 coprirà le transazioni in dettaglio. Per ora, sappi che il DDL è transazionale in SQL Server e usalo.

Creare tabelle, la versione pratica

L’esempio della lezione 3 era intenzionalmente minimale. Ecco un CREATE TABLE del mondo reale — il tipo che il backend team di Runehold spedirebbe davvero in produzione per memorizzare ordini di vendita:

USE Runehold;
GO

CREATE TABLE Sales.Orders (
    OrderId         BIGINT        IDENTITY(1,1) NOT NULL,
    CustomerId      INT                         NOT NULL,
    OrderDate       DATETIME2(0)                NOT NULL DEFAULT SYSUTCDATETIME(),
    Status          TINYINT                     NOT NULL DEFAULT 0,
    Total           DECIMAL(19, 4)              NOT NULL,   -- importi in EUR, 4 decimali per matematica esatta
    Currency        CHAR(3)                     NOT NULL DEFAULT 'EUR',
    CountryCode     CHAR(2)                     NOT NULL,   -- NL, DE, IT, ecc.
    VatRate         DECIMAL(5, 4)               NOT NULL,   -- 0.2100 per NL 21%
    Notes           NVARCHAR(4000)                  NULL,
    CreatedAt       DATETIME2(0)                NOT NULL DEFAULT SYSUTCDATETIME(),
    CreatedBy       NVARCHAR(128)               NOT NULL DEFAULT SUSER_SNAME(),
    UpdatedAt       DATETIME2(0)                NOT NULL DEFAULT SYSUTCDATETIME(),
    UpdatedBy       NVARCHAR(128)               NOT NULL DEFAULT SUSER_SNAME(),
    RowVersion      ROWVERSION,
    CONSTRAINT pk_Orders        PRIMARY KEY CLUSTERED (OrderId),
    CONSTRAINT fk_Orders_Customer FOREIGN KEY (CustomerId)
        REFERENCES Sales.Customer (CustomerId),
    CONSTRAINT ck_Orders_Total_NonNegative CHECK (Total >= 0),
    CONSTRAINT ck_Orders_Vat_Sensible       CHECK (VatRate BETWEEN 0 AND 0.3)
);

(Ho chiamato la tabella Orders — al plurale — apposta. Order è una parola riservata di T-SQL, quindi se la usi come nome di tabella dovresti racchiuderla con [Order] ogni singola volta. Metterla al plurale ti salva da quel piccolo taglietto per sempre. Le convenzioni di naming sono una guerra personale; scegli un lato e smetti di processare la cosa.)

Note sui pattern, perché ogni riga sta facendo il suo lavoro:

  • Primary key BIGINT IDENTITY(1,1). BIGINT perché tabelle in stile order crescono in fretta. IDENTITY(1,1) per l’auto-increment. Primary key clustered perché è il default e lo vogliamo.
  • NOT NULL di default. Solo una colonna (Notes) è nullable, ed è esplicito. Ogni altra colonna deve avere un valore. Questa singola abitudine previene più bug di qualsiasi altra cosa.
  • Colonne di audit. CreatedAt, CreatedBy, UpdatedAt, UpdatedBy. Se non stai tracciando chi ha inserito e chi ha aggiornato per ultimo la riga, le vorrai la prima volta che succede qualcosa di strano in produzione e nessuno sa perché.
  • ROWVERSION — un valore a 8 byte che si auto-aggiorna a ogni cambiamento. Perfetto per concorrenza ottimistica (“qualcun altro ha modificato questa riga da quando l’ho letta?”) e per scenari di sync e change tracking.
  • Vincoli nominati. pk_, fk_, ck_. Quando SQL Server auto-nomina i vincoli, ottieni nomi tipo PK__Orders__6A50D8DAF4D8EFDE che sono diversi su ogni ambiente. I vincoli nominati tengono i deploy ripetibili.
  • Vincolo CHECK. Spinge una regola di business (il totale non può essere negativo) giù nello schema. Una riga; catturerà bug per il resto della vita della tabella.
  • FOREIGN KEY. Integrità dei dati, integrità referenziale, il motivo per cui i database relazionali non sono solo “file con un indice”.

Ce n’è di più che puoi aggiungere (FILLFACTOR, FILESTREAM, partitioning, temporal table, colonne calcolate) ma quanto sopra è la forma da cavallo da tiro.

Create idempotente: IF NOT EXISTS

Spesso vorrai scrivere uno script che possa essere eseguito più volte senza esplodere. Il pattern per una tabella:

IF OBJECT_ID(N'Sales.Orders', N'U') IS NULL
BEGIN
    CREATE TABLE Sales.Orders (
        OrderId BIGINT IDENTITY(1,1) NOT NULL,
        -- ... resto delle colonne ...
        CONSTRAINT pk_Orders PRIMARY KEY CLUSTERED (OrderId)
    );
END;

OBJECT_ID('name', 'type') restituisce l’ID se l’oggetto esiste, NULL altrimenti. Il parametro di tipo 'U' significa “user table”. Altri valori comuni: 'V' (vista), 'P' (procedura), 'FN' (funzione scalare), 'TR' (trigger).

Scorciatoia da SQL Server 2016+:

CREATE TABLE IF NOT EXISTS Sales.Orders (...);  -- 2022+
DROP TABLE IF EXISTS Sales.Orders;              -- 2016+

Usa IF EXISTS / IF NOT EXISTS ovunque puoi. Gli script di migrazione che crashano alla seconda esecuzione sono una brutta abitudine da prendere.

Modificare le tabelle

Tre flavour di ALTER che coprono il 90% dei tuoi bisogni:

Aggiungere una colonna

ALTER TABLE Sales.Orders
ADD PromotionCode NVARCHAR(40) NULL;

Aggiungere una colonna NULL-able è un’operazione solo metadati. È istantanea anche su una tabella da un miliardo di righe — SQL Server aggiorna solo lo schema; le righe esistenti hanno la colonna “riempita” con NULL virtualmente. Nessun dato viene riscritto.

Aggiungere una colonna NOT NULL con un DEFAULT è anche solo metadati da SQL Server 2012 in poi, finché il default è una costante (non una chiamata di funzione). Si chiama “online add column” ed è ottimo.

Aggiungere una colonna NOT NULL senza un default fallisce, perché SQL Server non può indovinare cosa mettere nelle righe esistenti. Il workaround classico: aggiungila come nullable, riempi con un UPDATE, poi ALTER a NOT NULL una volta che ogni riga ha un valore.

Modificare il tipo di una colonna

ALTER TABLE Sales.Orders
ALTER COLUMN Notes NVARCHAR(MAX) NULL;

Allargare una colonna (NVARCHAR(100) → NVARCHAR(200)) è tipicamente veloce — cambio di metadati. Restringere (NVARCHAR(200) → NVARCHAR(100)) richiede di scansionare ogni riga per assicurarsi che entrino, e riscrivere quelle che ci entrano. Su una tabella grande questo non è istantaneo. Pianifica una finestra di manutenzione o usa online index rebuild per tabelle con tanto traffico.

Cambiare tipo del tutto (INT → BIGINT) riscrive anche ogni riga. Di nuovo, tabelle grandi = grossi downtime = pianifica con cura.

Eliminare una colonna

ALTER TABLE Sales.Orders
DROP COLUMN PromotionCode;

Solo metadati. Istantaneo. Ma i byte effettivi non vengono recuperati fino al prossimo index rebuild, quindi l’occupazione su disco della tabella non si riduce subito. Su una tabella calda dove devi recuperare spazio, schedula un ALTER INDEX ... REBUILD dopo il drop.

Vincoli, rinomine, e i pezzetti fastidiosi

-- Elimina un vincolo
ALTER TABLE Sales.Orders DROP CONSTRAINT ck_Orders_Total_NonNegative;

-- Aggiungi un vincolo
ALTER TABLE Sales.Orders
ADD CONSTRAINT ck_Orders_Total_NonNegative CHECK (Total >= 0);

-- Rinomina una colonna
EXEC sp_rename 'Sales.Orders.Notes', 'CustomerNotes', 'COLUMN';

-- Rinomina una tabella
EXEC sp_rename 'Sales.Orders', 'OrderHeader';

sp_rename è il modo dal nome strano ma ufficiale per rinominare le cose. Nota: rinominare rompe ogni stored procedure e vista che riferisce il vecchio nome. SQL Server non segue le dipendenze. Devi cacciarle a mano, di solito con una query a sys.sql_modules (vedi sotto).

Eliminare cose

DROP TABLE Sales.Orders;
DROP INDEX IX_OrderDate ON Sales.Orders;
DROP VIEW Sales.v_ActiveOrders;
DROP PROCEDURE Sales.usp_GetOrder;
DROP DATABASE OldDatabase;
DROP SCHEMA Legacy;

Eliminare una tabella con riferimenti foreign key dentro fallirà. Devi prima eliminare le foreign key, o prima le tabelle figlie. Questo ti salva dall’eliminare per sbaglio tabelle riferite, ed è il motivo per cui il messaggio di errore è probabilmente una funzionalità.

Eliminare uno schema che contiene ancora oggetti fallirà. Sposta o elimina prima gli oggetti, poi elimina lo schema.

Eliminare un indice è di solito sicuro. Eliminare un indice clustered converte la tabella in un heap, cosa che abbiamo coperto nella lezione 21 come “quasi sempre un errore”. Quindi: non eliminare indici clustered se non ne stai aggiungendo subito un altro.

L’esercizio “oh no l’ho droppato”

Assumerò che tu abbia fatto tutto bene e che lo abbia scritto a memoria per quando arriva il momento:

Passo 1. Vai nel panico in silenzio

Non fare CREATE TABLE per rimetterla a posto. Crea una nuova tabella vuota con lo stesso nome. I tuoi dati sono ancora persi dalla loro posizione originale ma ora hai creato una nuova lapide vuota.

Passo 2. Era dentro una transazione?

Se la tua finestra SSMS aveva BEGIN TRAN in cima, esegui subito ROLLBACK. Se la transazione è ancora aperta, riavrai tutto. Controlla con:

SELECT @@TRANCOUNT;

Se restituisce > 0, hai una transazione aperta. ROLLBACK TRAN; annulla il drop. Hai appena ottenuto una seconda chance.

Se @@TRANCOUNT è 0, la transazione si è committata (o non c’era transazione). Vai al passo 3.

Passo 3. Restore da backup

È qui che la tua strategia di backup si guadagna lo stipendio. Se fai backup full giornalieri con backup dei transaction log ogni 15 minuti (standard per qualsiasi sistema OLTP serio), puoi fare il restore a un point-in-time appena prima del drop. Quel restore va in un database temporaneo, dal quale copi la tabella mancante in produzione.

RESTORE DATABASE MyApp_Recovery
FROM DISK = N'C:\Backups\MyApp_2025-12-10.bak'
WITH MOVE ...

RESTORE LOG MyApp_Recovery
FROM DISK = N'C:\Backups\MyApp_log_2025-12-10_1415.trn'
WITH STOPAT = '2025-12-10 14:29:59', NORECOVERY, ...

-- Continua ad applicare i log fino al tuo stopat.
RESTORE LOG MyApp_Recovery
FROM DISK = N'C:\Backups\MyApp_log_2025-12-10_1430.trn'
WITH STOPAT = '2025-12-10 14:29:59', RECOVERY;

-- Adesso tira fuori la tabella
SELECT * INTO Sales.Orders_Recovered
FROM MyApp_Recovery.Sales.Orders;

È per questo che la lezione 31 (backup) è la lezione più importante del modulo DBA. Una schedule di full giornaliero + log orario significa che qualsiasi errore di drop è al massimo un’ora di perdita dati. Senza quella schedule, potresti star spiegando al consiglio di amministrazione perché hai perso un trimestre di transazioni di un cliente.

Passo 4. Dillo a tutti

Non nasconderlo. “Ho droppato la cosa sbagliata, ecco il piano di recovery, ecco l’ETA” è il messaggio giusto. Provare a nascondere una perdita di dati non finisce mai bene.

Passo 5. Il post-mortem

Una volta sistemato, blocca tempo in calendario e scrivi cosa è successo. La singola classe più prevenibile di “ho droppato la cosa sbagliata” è eseguire script nell’ambiente sbagliato. Usa connessioni colorate in SSMS (Options → Connection Colors → Rosso per prod). Usa un prompt in Azure Data Studio che urli “SEI IN PRODUZIONE.” Mai, mai, avere la stessa connessione aperta a dev e prod allo stesso tempo. Ogni DBA ha una di queste storie. Il trucco è fermarsi a una.

TRUNCATE TABLE vs DELETE

Confronto rapido:

-- DELETE: logga ogni riga, più lento, può essere filtrato
DELETE FROM Sales.Orders WHERE Status = 9;

-- TRUNCATE: resetta tutte le pagine, veloce, niente filtro
TRUNCATE TABLE Sales.Orders;

TRUNCATE è un comando DDL, non DML. È quasi istantaneo su qualsiasi dimensione di tabella perché in realtà sta solo segnando le pagine come vuote. Tre trappole:

  1. Niente filtro. TRUNCATE TABLE X WHERE condition; non esiste. O cancelli l’intera tabella o niente.
  2. Niente foreign key che la punta. Se un’altra tabella riferisce questa con una FK, TRUNCATE fallisce. Microsoft assume che preferiresti sapere della FK piuttosto che rompere silenziosamente l’integrità referenziale.
  3. L’identity si resetta. TRUNCATE resetta il seme IDENTITY al valore di partenza. DELETE lo lascia dov’era. Se stai scriptando test, può contare.

TRUNCATE è transazionale, cosa che spesso sorprende. Puoi fare BEGIN TRAN; TRUNCATE TABLE X; ROLLBACK; e i dati tornano. In questo caso specifico, SQL Server logga i metadati di deallocazione delle pagine — solo molto compattamente.

Trovare chi riferisce cosa

La domanda “se elimino questa colonna, cosa si rompe?” salta fuori a ogni sprint. Ecco la query:

SELECT
    SCHEMA_NAME(o.schema_id) AS schema_name,
    o.name                   AS object_name,
    o.type_desc              AS object_type
FROM sys.sql_modules AS m
JOIN sys.objects     AS o ON o.object_id = m.object_id
WHERE m.definition LIKE N'%YourColumnName%'
   OR m.definition LIKE N'%YourTableName%';

Cerca in ogni procedura, funzione, vista, e trigger riferimenti testuali. Non perfetta — l’SQL dinamico è opaco a questo — ma il 90% di “cosa si rompe se cambio questo?” trova risposta in trenta secondi.

Esegui questo sulla tua macchina

Un playground sicuro per i pattern di oggi:

USE tempdb;
GO

-- Crea in uno schema così possiamo droppare l'intero schema alla fine
CREATE SCHEMA Playground AUTHORIZATION dbo;
GO

-- 1. Create idempotente
IF OBJECT_ID(N'Playground.Orders', N'U') IS NULL
BEGIN
    CREATE TABLE Playground.Orders (
        OrderId   BIGINT IDENTITY(1,1) NOT NULL,
        Total     DECIMAL(19,4)         NOT NULL DEFAULT 0,
        CreatedAt DATETIME2(0)          NOT NULL DEFAULT SYSUTCDATETIME(),
        CONSTRAINT pk_Orders PRIMARY KEY CLUSTERED (OrderId)
    );
END;

-- 2. Inserisci un po' di dati
INSERT INTO Playground.Orders (Total) VALUES (100.00), (250.75), (42.42);

-- 3. Aggiungi una colonna NOT NULL con un default (solo metadati)
ALTER TABLE Playground.Orders
ADD Status TINYINT NOT NULL CONSTRAINT df_Orders_Status DEFAULT 0;

-- 4. Rinomina una colonna
EXEC sp_rename 'Playground.Orders.Total', 'OrderTotal', 'COLUMN';

-- 5. Avvolgi un'operazione pericolosa in una transazione
BEGIN TRAN;
    DELETE FROM Playground.Orders;
    SELECT COUNT(*) AS rows_left FROM Playground.Orders; -- 0
ROLLBACK;
SELECT COUNT(*) AS rows_back FROM Playground.Orders;     -- 3 di nuovo

-- 6. Trova qualsiasi cosa riferisca "Orders"
SELECT SCHEMA_NAME(o.schema_id) + '.' + o.name AS obj, o.type_desc
FROM sys.sql_modules m JOIN sys.objects o ON o.object_id = m.object_id
WHERE m.definition LIKE N'%Orders%';

-- 7. Pulizia
DROP TABLE Playground.Orders;
DROP SCHEMA Playground;

Eseguilo, leggi ogni risultato, capisci ogni passo. Poi rifallo un’altra volta senza guardare. Questi pattern sono il tuo pane quotidiano per tutto il tempo che lavorerai con SQL Server.

Prossima lezione: SELECT come si deve. Proiezione, alias, i peccati di SELECT *, e la cosa sottile di cui nessuno ti aveva avvertito quando hai imparato SQL la prima volta: l’ordine in cui le clausole sono scritte non è l’ordine in cui vengono eseguite. Quel piccolo fatto spiega metà della confusione che i principianti hanno con gli aggregati, ed è la lezione 6.

Cerca