Narcis Miclaus

11 min de citit

SQL Server, de la zero Lecția 5 / 40

CREATE, ALTER, DROP: facerea și desfacerea tabelelor

Bazele DDL, pattern-uri IF EXISTS, scripturi idempotente și exercițiul „o, nu, am dat drop la prod”. Plus cum schimbi o coloană fără să strici aplicația.

Fiecare bază de date începe ca un cursor care clipește și un statement CREATE TABLE. Fiecare bază de date se termină ca o serie lungă de statements ALTER TABLE scrise de oameni care n-au planificat ca schema lor să se schimbe. Iar din când în când, cineva rulează DROP TABLE în fereastra greșită de query și ziua devine despre CV-uri.

Lecția asta e despre Data Definition Language — DDL — subsetul de SQL care creează, modifică și distruge obiecte din baza de date. E primul SQL pe care majoritatea oamenilor îl învață și partea unde se întâmplă cele mai catastrofale greșeli. La final vei ști pattern-urile sigure, cele nesigure și ce să faci dacă deja ai făcut alegerea nesigură.

Cele trei mari

  • CREATE — face un obiect nou (tabel, index, view, procedură, schemă, bază de date).
  • ALTER — schimbă un obiect existent.
  • DROP — șterge un obiect.

Mai există și TRUNCATE TABLE, care șterge toate rândurile dar păstrează tabelul. E mult mai rapid decât DELETE pentru că e o operație de metadate care doar resetează paginile de date și nu loghează fiecare rând individual. Mai multe într-un minut.

Fiecare comandă DDL în SQL Server rulează într-o tranzacție, exact ca DML. Asta înseamnă că poți face BEGIN TRAN; DROP TABLE ...; ROLLBACK; și-ți recapeți tabelul. De mai multe ori, această singură funcționalitate mi-a salvat cariera. Învață să începi fiecare sesiune ad-hoc DDL cu BEGIN TRAN și s-o închei fie cu COMMIT fie cu ROLLBACK. Lecția 18 va acoperi tranzacțiile în detaliu. Pentru moment, știi că DDL e tranzacțional în SQL Server și folosește-l.

Crearea tabelelor, versiunea practică

Exemplul din lecția 3 era intenționat minim. Iată un CREATE TABLE din lumea reală — genul pe care echipa de backend de la Runehold l-ar livra efectiv în producție pentru stocarea comenzilor de vânzare:

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,   -- sume în EUR, 4 zecimale pentru calcul exact
    Currency        CHAR(3)                     NOT NULL DEFAULT 'EUR',
    CountryCode     CHAR(2)                     NOT NULL,   -- NL, DE, IT etc.
    VatRate         DECIMAL(5, 4)               NOT NULL,   -- 0.2100 pentru 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)
);

(Am numit tabelul Orders — la plural — intenționat. Order e cuvânt rezervat în T-SQL, deci dacă-l folosești ca nume de tabel ar trebui să-l pui între paranteze drepte [Order] de fiecare dată. Pluralizarea te scapă de tăietura aia de hârtie pentru totdeauna. Convențiile de denumire sunt un război personal; alege o tabără și nu mai litiga.)

Note de pattern, pentru că fiecare linie își face treaba:

  • Cheia primară BIGINT IDENTITY(1,1). BIGINT pentru că tabelele de tip orders cresc rapid. IDENTITY(1,1) pentru auto-increment. Cheie primară clustered pentru că ăsta e default-ul și asta vrem.
  • NOT NULL implicit. O singură coloană (Notes) e nullable, și e explicit. Fiecare altă coloană trebuie să aibă o valoare. Obiceiul ăsta unul singur previne mai multe bug-uri decât oricare altul.
  • Coloane de audit. CreatedAt, CreatedBy, UpdatedAt, UpdatedBy. Dacă nu urmărești cine a inserat și cine a actualizat ultimul rândul, le vei dori prima dată când se întâmplă ceva ciudat în producție și nimeni nu știe de ce.
  • ROWVERSION — o valoare de 8 bytes care se actualizează automat la fiecare schimbare. Perfect pentru concurență optimistă („a modificat altcineva rândul ăsta de când l-am citit?”) și pentru scenarii de sincronizare cu change-tracking.
  • Constrângeri denumite. pk_, fk_, ck_. Când SQL Server numește automat constrângerile, primești nume ca PK__Orders__6A50D8DAF4D8EFDE care sunt diferite în fiecare mediu. Constrângerile denumite păstrează deploy-urile repetabile.
  • Constrângere CHECK. Împinge o regulă de business (totalul nu poate fi negativ) jos în schemă. O linie; va prinde bug-uri pentru tot restul vieții tabelului.
  • FOREIGN KEY. Integritatea datelor, integritatea referențială, motivul pentru care bazele de date relaționale nu sunt doar „fișiere cu un index.”

Mai e și altele pe care le poți adăuga (FILLFACTOR, FILESTREAM, partiționare, tabele temporale, coloane calculate) dar mai sus e forma de cal de tracțiune.

Create idempotent: IF NOT EXISTS

Vei vrea adesea să scrii un script care să poată fi rulat de mai multe ori fără să explodeze. Pattern-ul pentru un tabel:

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

OBJECT_ID('name', 'type') returnează ID-ul dacă obiectul există, NULL dacă nu. Parametrul de tip 'U' înseamnă „user table.” Alte valori comune: 'V' (view), 'P' (procedure), 'FN' (scalar function), 'TR' (trigger).

Prescurtarea din SQL Server 2016+:

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

Folosește IF EXISTS / IF NOT EXISTS oriunde poți. Scripturile de migrare care crapă la o re-rulare sunt un obicei prost de adoptat.

Modificarea tabelelor

Trei arome de ALTER care acoperă 90% din nevoile tale:

Adăugarea unei coloane

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

Adăugarea unei coloane NULL-able e o operație doar de metadate. E instantanee chiar și pe un tabel de un miliard de rânduri — SQL Server doar actualizează schema; rândurile existente primesc coloana „umplută” cu NULL virtual. Nicio dată nu e rescrisă.

Adăugarea unei coloane NOT NULL cu un DEFAULT e de asemenea doar de metadate din SQL Server 2012, atâta timp cât default-ul e o constantă (nu un apel de funcție). Asta se cheamă funcționalitatea „online add column” și e excelentă.

Adăugarea unei coloane NOT NULL fără default eșuează, pentru că SQL Server nu poate ghici ce să pună în rândurile existente. Workaround-ul clasic: adaug-o ca nullable, populeaz-o cu un UPDATE, apoi ALTER-o la NOT NULL odată ce fiecare rând are o valoare.

Modificarea tipului unei coloane

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

Lărgirea unei coloane (NVARCHAR(100) → NVARCHAR(200)) e de obicei rapidă — schimbare de metadate. Îngustarea (NVARCHAR(200) → NVARCHAR(100)) necesită scanarea fiecărui rând să se asigure că încap, și rescrierea celor care nu. Pe un tabel mare asta nu e instantaneu. Planifică o fereastră de mentenanță sau folosește online index rebuilds pentru tabele cu mult trafic.

Schimbarea totală de tip (INT → BIGINT) rescrie de asemenea fiecare rând. Din nou, tabele mari = downtime mare = planifică atent.

Eliminarea unei coloane

ALTER TABLE Sales.Orders
DROP COLUMN PromotionCode;

Doar de metadate. Instantaneu. Dar bytes-ii actuali nu sunt recuperați până la următorul rebuild de index, deci amprenta pe disc a tabelului nu se micșorează imediat. Pe un tabel fierbinte unde trebuie să recuperezi spațiu, programează un ALTER INDEX ... REBUILD după drop.

Constrângeri, redenumiri și părțile fiddly

-- Drop o constrângere
ALTER TABLE Sales.Orders DROP CONSTRAINT ck_Orders_Total_NonNegative;

-- Adaugă o constrângere
ALTER TABLE Sales.Orders
ADD CONSTRAINT ck_Orders_Total_NonNegative CHECK (Total >= 0);

-- Redenumește o coloană
EXEC sp_rename 'Sales.Orders.Notes', 'CustomerNotes', 'COLUMN';

-- Redenumește un tabel
EXEC sp_rename 'Sales.Orders', 'OrderHeader';

sp_rename e modalitatea oficială, ciudat numită, de a redenumi lucruri. Notă: redenumirea sparge fiecare procedură stocată și view care referențiază numele vechi. SQL Server nu urmărește dependențele. Trebuie să le vânezi singur, de obicei cu un query pe sys.sql_modules (vezi mai jos).

Eliminarea lucrurilor

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;

Eliminarea unui tabel cu referințe de chei străine spre el va eșua. Trebuie să elimini cheile străine întâi, sau să elimini tabelele copil întâi. Asta te scapă de eliminarea tabelelor referențiate din greșeală, motiv pentru care mesajul de eroare e probabil o funcționalitate.

Eliminarea unei scheme care conține încă obiecte va eșua. Mută sau elimină obiectele întâi, apoi elimină schema.

Eliminarea unui index e de obicei sigură. Eliminarea unui index clustered convertește tabelul într-un heap, ceea ce am acoperit în lecția 21 ca „aproape mereu o greșeală.” Deci: nu elimina indexuri clustered decât dacă imediat adaugi altul.

Exercițiul „o, nu, l-am eliminat”

Voi presupune că ai făcut totul bine și ai scris asta din memorie când vine momentul:

Pasul 1. Panică în liniște

Nu face CREATE TABLE ca să-l pui înapoi. Asta creează un tabel nou gol cu același nume. Datele tale tot sunt dispărute din locația originală dar acum ai făcut o nouă piatră de mormânt goală.

Pasul 2. A fost într-o tranzacție?

Dacă fereastra ta SSMS avea BEGIN TRAN la început, rulează imediat ROLLBACK. Dacă tranzacția e încă deschisă, recuperezi totul. Verifică cu:

SELECT @@TRANCOUNT;

Dacă asta returnează > 0, ai o tranzacție deschisă. ROLLBACK TRAN; anulează drop-ul. Tocmai ai primit a doua șansă.

Dacă @@TRANCOUNT e 0, tranzacția a fost commit-ată (sau n-a existat tranzacție). Treci la pasul 3.

Pasul 3. Restore din backup

Aici își câștigă pâinea strategia ta de backup. Dacă faci backup-uri full zilnice cu backup-uri de log de tranzacții la fiecare 15 minute (standard pentru orice sistem OLTP serios), poți face restore la un moment specific din timp chiar înainte de drop. Restore-ul ăla merge într-o bază de date temporară, din care copiezi tabelul lipsă în producție.

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, ...

-- Continuă aplicarea log-urilor până la timpul 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;

-- Acum trage tabelul înapoi
SELECT * INTO Sales.Orders_Recovered
FROM MyApp_Recovery.Sales.Orders;

De-asta lecția 31 (backup-uri) e cea mai importantă lecție din modulul de DBA. O programare de full zilnic + log orar înseamnă că orice greșeală legată de drop e cel mult o oră de pierdere de date. Fără programarea aia, ai putea să-i explici board-ului de ce ai pierdut un sfert de tranzacții ale unui client.

Pasul 4. Spune-le tuturor

Nu ascunde. „Am eliminat ce nu trebuia, iată planul de recuperare, iată ETA-ul” e mesajul corect. Ascunderea unei pierderi de date nu se termină niciodată bine.

Pasul 5. Post-mortem-ul

După ce e rezolvat, blochează timp în calendar și scrie ce s-a întâmplat. Cea mai prevenibilă clasă de „am eliminat ce nu trebuia” e rularea scripturilor în mediul greșit. Folosește conexiuni SSMS colorate (Options → Connection Colors → Roșu pentru prod). Folosește un prompt în Azure Data Studio care urlă „EȘTI ÎN PRODUCȚIE.” Niciodată, niciodată să nu ai aceeași conexiune deschisă la dev și prod în același timp. Fiecare DBA are o astfel de poveste. Trucul e să te oprești la una.

TRUNCATE TABLE vs DELETE

Comparație rapidă:

-- DELETE: loghează fiecare rând, mai lent, poate fi filtrat
DELETE FROM Sales.Orders WHERE Status = 9;

-- TRUNCATE: resetează toate paginile, rapid, fără filtru
TRUNCATE TABLE Sales.Orders;

TRUNCATE e o comandă DDL, nu DML. E aproape instantanee pe orice mărime de tabel pentru că în realitate doar marchează paginile ca goale. Trei capcane:

  1. Fără filtru. TRUNCATE TABLE X WHERE condition; nu există. Ori arunci în aer tot tabelul, ori nu.
  2. Fără cheie străină care arată spre el. Dacă alt tabel îl referențiază cu o FK, TRUNCATE eșuează. Microsoft presupune că ai prefera să afli despre FK decât să strice silențios integritatea referențială.
  3. Resetare de identity. TRUNCATE resetează sămânța IDENTITY înapoi la valoarea de pornire. DELETE o ține unde era. Dacă scriptezi teste, asta poate conta.

TRUNCATE e tranzacțional, ceea ce surprinde adesea oamenii. Poți face BEGIN TRAN; TRUNCATE TABLE X; ROLLBACK; și datele se întorc. În cazul ăsta specific, SQL Server loghează metadatele de dealocare a paginilor — doar foarte compact.

Găsirea cui referențiază ce

Întrebarea „dacă elimin coloana asta, ce se sparge?” apare în fiecare sprint. Iată query-ul:

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%';

Caută în fiecare procedură, funcție, view și trigger pentru referințe text. Nu e perfect — SQL-ul dinamic e opac pentru asta — dar 90% din „ce se sparge dacă schimb asta?” primește răspuns în treizeci de secunde.

Rulează asta pe propria mașină

Un teren de joacă sigur pentru pattern-urile de azi:

USE tempdb;
GO

-- Creează într-o schemă ca să putem elimina toată schema la final
CREATE SCHEMA Playground AUTHORIZATION dbo;
GO

-- 1. Create idempotent
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. Inserează niște date
INSERT INTO Playground.Orders (Total) VALUES (100.00), (250.75), (42.42);

-- 3. Adaugă o coloană NOT NULL cu default (doar metadate)
ALTER TABLE Playground.Orders
ADD Status TINYINT NOT NULL CONSTRAINT df_Orders_Status DEFAULT 0;

-- 4. Redenumește o coloană
EXEC sp_rename 'Playground.Orders.Total', 'OrderTotal', 'COLUMN';

-- 5. Înfășoară o operație periculoasă într-o tranzacție
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 din nou

-- 6. Găsește orice referențiază „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. Curățenie
DROP TABLE Playground.Orders;
DROP SCHEMA Playground;

Rulează-l, citește fiecare rezultat, înțelege fiecare pas. Apoi fă-o încă o dată fără să te uiți. Pattern-urile astea sunt pâinea ta zilnică pentru cât timp lucrezi cu SQL Server.

Lecția următoare: SELECT ca lumea. Proiecție, alias-uri, păcatele lui SELECT * și lucrul subtil despre care nimeni nu te-a avertizat când ai învățat prima dată SQL: ordinea în care clauzele sunt scrise nu e ordinea în care sunt executate. Acel mic fapt explică jumătate din confuzia pe care o au începătorii cu agregările, și e lecția 6.

Caută