Narcis Miclaus

10 min de citit

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

Baze de date, scheme, tabele: păpușile rusești

Spațiul de nume pe trei niveluri pe care SQL Server îl folosește ca să organizeze totul, la ce e bun fiecare nivel și de ce dbo nu e chiar prietenul tău.

Să zicem că tocmai ai un SQL Server proaspăt din lecția 2 și te uiți la arborele Object Explorer din SSMS. Vezi „Databases”, iar sub el „System Databases”, iar bazele de date de sistem se cheamă master, model, msdb și tempdb. N-ai creat încă nimic și deja există patru baze de date.

Dacă ești nou în SQL Server, partea asta a lumii e profund derutantă. Devine și mai rău: în interiorul unei baze de date există un lucru numit schemă, care nu e același lucru cu ce înseamnă „schemă” în Postgres, sau în uneltele ORM, sau în limba română de zi cu zi. Iar în interiorul schemelor există tabele, view-uri, proceduri stocate și funcții.

Lecția de azi e despre desfacerea cuiburilor. La final vei ști exact ce înseamnă fiecare nivel, la ce e bun și de ce „pune-l pur și simplu în dbo” e cea mai comună greșeală pe care o fac oamenii când încep.

Cuiburile, dinspre exterior spre interior

SQL Server are un spațiu de nume pe trei niveluri pentru majoritatea obiectelor:

Instanță → Bază de date → Schemă → Obiect

Fiecare tabel, view, procedură și funcție are un nume complet cu trei puncte:

NumeBazăDate.NumeSchemă.NumeObiect

De exemplu: AdventureWorks2022.Sales.SalesOrderHeader. Numele instanței e implicit (e oriunde ești conectat în acel moment). Unele obiecte precum login-urile, rolurile de server și serverele linkate trăiesc la nivel de instanță, nu în interiorul unei baze de date — dar e excepția. Pentru lucrurile cu care vei scrie query-uri zi de zi, numele din trei părți e legea.

Hai să mergem nivel cu nivel.

Instanța: la ce te conectezi

O instanță e un proces SQL Server care rulează. O singură mașină server poate avea mai multe instanțe (cea implicită, plus unele cu nume precum SERVERNAME\SQL2019 și SERVERNAME\SQL2022), fiecare cu propriul port, propria configurație, propriile baze de date și propria memorie. E mai puțin comun decât era — în era cloud, „o instanță per mașină” e norma — dar vei întâlni mașini on-prem vechi care rulează trei instanțe pe un server, fiecare ascultând pe un port diferit, pentru că cineva în 2010 a crezut că e o idee bună.

Pentru cursul ăsta ai o singură instanță, fie localhost (dacă ai instalat local) fie localhost,1433 (dacă folosești Docker). Atât. Tot restul se cuibărește înăuntru.

Instanța e unde trăiesc login-urile. Un login e felul în care te autentifici la server. Login-urile se mapează la utilizatori în interiorul bazelor de date, dar ajungem acolo în lecția 33 despre securitate.

Bazele de date

În interiorul unei instanțe ai baze de date. Fiecare bază de date are propriul storage (propriile fișiere .mdf și .ldf), propriul ciclu de backup și restore, și propriii utilizatori. Când rulezi CREATE DATABASE MyApp;, SQL Server pornește două fișiere — un fișier de date și un fișier de log — și înregistrează noua bază de date în master.

Fiecare instanță vine cu patru baze de date de sistem pe care ar trebui să înveți să le recunoști:

  • master — catalogul instanței. Ține evidența ce baze de date există, ce login-uri există, ce servere linkate sunt configurate și setările generale la nivel de server. Dacă master moare, moare toată instanța. Fă-i backup. Tratează-l cu respect.
  • model — șablonul. De fiecare dată când rulezi CREATE DATABASE, SQL Server copiază model și-l folosește ca punct de plecare. Dacă vrei ca fiecare bază de date nouă să înceapă cu un set implicit de tabele, utilizatori sau proprietăți extinse, le pui în model. 95% din timp lași model în pace.
  • msdb — unde trăiește SQL Server Agent. Job-uri programate, istoric de job-uri, istoric de backup-uri, database mail, planuri de mentenanță. Dacă te-ai întrebat vreodată „când a rulat ultimul meu backup?”, răspunsul e în msdb.
  • tempdb — o zonă de scribbling împărțită de fiecare sesiune din instanță. Tabele temporare, variabile de tip tabel, sort spills, date din version store pentru snapshot isolation, și multe altele aterizează aici. tempdb se resetează de fiecare dată când SQL Server se restartează. E și cea mai comună sursă unică de surprize de performanță pe un server aglomerat, și-i vom dedica o lecție întreagă mai târziu (lecția 34).

Apoi ai bazele de date de utilizator — cele pe care le creezi pentru aplicațiile tale. Fiecare are propriul nume, propriile setări, proprii utilizatori. O instanță SQL Server poate găzdui mii de baze de date; în practică, companiile țin numărul la zeci.

Schimbarea între bazele de date din SSMS se face cu dropdown-ul din partea de sus a ferestrei de query, sau rulând USE NumeBazăDate; la începutul unui script. USE e un statement T-SQL care schimbă spre care bază de date e îndreptată sesiunea ta.

USE master;
SELECT name, database_id, create_date, state_desc
FROM sys.databases
ORDER BY database_id;

Rulează asta și vei vedea bazele de date de sistem plus orice baze de date de utilizator ai creat. database_id pentru master e mereu 1, tempdb e 2, model e 3, msdb e 4. Bazele tale de utilizator încep de la 5.

Schemele și de ce contează

În interiorul unei baze de date, obiectele sunt organizate în scheme. O schemă e un spațiu de nume logic. Există ca să poți spune Sales.Customer și HR.Customer fără ca acestea să fie același obiect.

Schemele sunt o funcționalitate pe care majoritatea oamenilor o ignoră, ceea ce e păcat. În AdventureWorks vei vedea Person.*, Sales.*, HumanResources.*, Production.*, Purchasing.*. Fiecare schemă grupează tabele înrudite: Person.Address, Person.EmailAddress, Person.PersonPhone. Gruparea aia e tot rostul.

Schema implicită în SQL Server se cheamă dbo (prescurtare de la „database owner”). Când rulezi CREATE TABLE Foo; fără un prefix de schemă, SQL Server creează dbo.Foo. E ok pentru o bază de date demo micuță. E un dezastru pentru orice aplicație reală.

De ce dbo nu e chiar prietenul tău:

  • Fiecare obiect pe care nu-l plasezi explicit într-o schemă ajunge în dbo. Șase luni mai târziu, ai 340 de tabele în dbo fără nicio structură descoperibilă.
  • Permisiunile bazate pe schemă îți permit să spui „toți utilizatorii din rolul Reporting pot face SELECT pe fiecare tabel din schema Reporting.” Acordarea per tabel e un coșmar.
  • Module diferite ale aplicației (Sales, Billing, HR, Ops) se calcă în picioare când împart toate dbo. Segregarea pe scheme permite modulelor să evolueze independent.
  • Când trebuie să muți un tabel mai târziu, mutările între scheme sunt ieftine și bine suportate. Redenumirea jumătății din tabelele tale dbo nu e.

Regula pe care le-o dau juniorilor din echipă: niciodată să nu creezi nimic în dbo. Chiar și un proof-of-concept de aruncat primește propria schemă numită după ce face. Staging.CustomerImport, nu dbo.Stage_Customer_Import_Temp.

Crearea unei scheme e o singură linie:

CREATE SCHEMA Sales AUTHORIZATION dbo;

„AUTHORIZATION dbo” înseamnă că schema e deținută de utilizatorul dbo. Poți schimba dreptul de proprietate mai târziu. Crearea unui obiect într-o anumită schemă înseamnă doar prefixarea numelui:

CREATE TABLE Sales.Customer (
    CustomerId INT NOT NULL PRIMARY KEY,
    Name       NVARCHAR(100) NOT NULL
);

Iar interogarea folosește numele din două părți:

SELECT CustomerId, Name FROM Sales.Customer;

Poți scăpa cu SELECT * FROM Customer pentru că SQL Server caută întâi în schema ta implicită (de obicei dbo), dar scrierea de nume complet calificate e un obicei pe care merită să-l construiești. Elimină ambiguitatea, e mai ușor de citit peste șase luni și-ți face mai puternic jocul cu grep când vânezi referințe.

Tabelele: lucrul pentru care ai venit

În interiorul unei scheme trăiește obiectul de care toată lumea chiar îi pasă: tabelele. Un tabel e un dreptunghi de date: coloane fixe cu nume și tipuri de date și zero sau mai multe rânduri. Fiecare rând e o instanță a entității pe care tabelul o reprezintă. Un tabel Sales.Customer are rânduri unde fiecare rând e un singur client.

Tabelele se creează cu CREATE TABLE:

CREATE TABLE Sales.Customer (
    CustomerId      INT           NOT NULL IDENTITY(1,1),
    Name            NVARCHAR(100) NOT NULL,
    Email           NVARCHAR(200) NOT NULL,
    CreatedAt       DATETIME2(0)  NOT NULL DEFAULT SYSUTCDATETIME(),
    IsActive        BIT           NOT NULL DEFAULT 1,
    CONSTRAINT pk_Customer PRIMARY KEY CLUSTERED (CustomerId)
);

Câteva lucruri de observat, pentru că sunt fundamentale:

  • Fiecare coloană are un tip de date. INT, NVARCHAR(100), DATETIME2(0), BIT. Vom petrece o lecție întreagă pe tipuri de date data viitoare, pentru că să le greșești costă bani reali. Pentru moment, știi că SQL Server e puternic tipizat: '42' și 42 nu sunt același lucru, iar amestecarea lor cauzează conversii implicite pe care nu le vrei.
  • Fiecare coloană poate fi NOT NULL sau implicit nullable. Fii mereu explicit. A scrie NOT NULL pe coloane care n-ar trebui să accepte NULL te scapă de categorii întregi de bug-uri.
  • IDENTITY(1,1) face SQL Server să auto-genereze valori întregi secvențiale. Sămânță 1, increment 1. Echivalent cu AUTO_INCREMENT în MySQL sau SERIAL în Postgres.
  • DEFAULT dă unei coloane o valoare dacă INSERT-ul nu specifică una. SYSUTCDATETIME() e funcția încorporată pentru „acum, în UTC.” Folosește UTC, mereu. Vom acoperi durerea de a manipula date în lecția 15.
  • Linia CONSTRAINT pk_Customer PRIMARY KEY CLUSTERED (CustomerId) creează atât o cheie primară cât și un index clustered pe CustomerId. În SQL Server, o cheie primară primește implicit un index clustered dacă nu spui altceva. Vezi lecția 21 pentru povestea completă despre indexurile clustered — sunt probabil cea mai importantă idee din întregul curs.

Alte lucruri care trăiesc într-o schemă

Schemele țin mai mult decât tabele. Țin și:

  • Vederi (views) — query-uri salvate care arată ca tabele.
  • Proceduri stocate — T-SQL salvat pe care-l poți chema cu EXEC.
  • Funcții — scalare sau cu valoare-tabel; chemate ca o funcție în interiorul unui query.
  • Triggere — T-SQL care rulează automat la INSERT, UPDATE, DELETE sau DDL.
  • Tipuri definite de utilizator — tipuri de date personalizate construite peste cele încorporate.
  • Sinonime — alias-uri locale pentru obiecte din alte baze de date.

Toate astea sunt namespace-uite după schemă. Sales.GetActiveCustomers e o procedură. Reporting.CustomerSummary e o vedere. Staging.LoadOrders e o procedură într-o schemă de staging pe care o folosești pentru ETL. Granițele schemelor sunt un cadou. Acceptă cadoul.

Listarea a ceea ce e într-o bază de date

Două query-uri de memorat. Primul, „ce tabele există în baza de date curentă și în ce schemă sunt?”:

SELECT
    SCHEMA_NAME(t.schema_id) AS schema_name,
    t.name                   AS table_name,
    p.rows                   AS approx_rows
FROM sys.tables AS t
JOIN sys.partitions AS p
    ON p.object_id = t.object_id
    AND p.index_id IN (0, 1)
ORDER BY schema_name, table_name;

Al doilea, „ce scheme există în baza asta de date?”:

SELECT name AS schema_name,
       SCHEMA_OWNER = USER_NAME(principal_id) AS owner
FROM sys.schemas
ORDER BY name;

Astea două vor deveni reflex într-o lună. De fiecare dată când te alături unei echipe noi sau deschizi o bază de date necunoscută, rulează-le primele ca să vezi terenul.

Rulează asta pe propria mașină

Un mic exercițiu ca să facem cele trei niveluri tangibile. Vom construi o versiune mică a schemei Runehold pe care o vom extinde de-a lungul cursului. Lipește asta într-o fereastră de query și rulează tot:

-- 1. Creează baza de date Runehold
IF DB_ID('Runehold') IS NULL
    CREATE DATABASE Runehold;
GO

USE Runehold;
GO

-- 2. Creează două scheme care aparțin la două echipe diferite
CREATE SCHEMA Sales AUTHORIZATION dbo;
GO
CREATE SCHEMA HR AUTHORIZATION dbo;
GO

-- 3. Fiecare echipă are un concept „Customer" cu o semnificație diferită.
--    Sales.Customer = oameni care cumpără bunuri vrăjite de la noi.
--    HR.Customer    = numele intern pe care HR îl folosește pentru „angajați"
--                     (ciudat, dar se întâmplă tot timpul la companii reale).
CREATE TABLE Sales.Customer (
    CustomerId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    Name       NVARCHAR(100) NOT NULL,
    Country    CHAR(2)       NOT NULL     -- ISO 3166-1 alpha-2: NL, DE, IT...
);

CREATE TABLE HR.Customer (
    EmployeeId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    FullName   NVARCHAR(200) NOT NULL,
    Team       NVARCHAR(50)  NOT NULL
);

-- 4. Inserează date care arată realist
INSERT INTO Sales.Customer (Name, Country)
VALUES ('Anne de Vries',    'NL'),
       ('Marco Rossi',       'IT'),
       ('Franz Hofmann',     'DE'),
       ('Ioana Popescu',     'RO');

INSERT INTO HR.Customer (FullName, Team)
VALUES ('Ada Lovelace',  'Engineering'),
       ('Grace Hopper',  'Engineering'),
       ('Djenna Akkad',  'Marketing'),
       ('Piotr Nowak',   'Warehouse');

-- 5. Interoghează-le pe ambele, complet calificate
SELECT 'Sales' AS src, CustomerId AS id, Name    AS name, Country AS extra
FROM Sales.Customer
UNION ALL
SELECT 'HR',           EmployeeId,       FullName,        Team
FROM HR.Customer
ORDER BY src, id;

-- 6. Listează ce-ai construit
SELECT SCHEMA_NAME(schema_id) AS schema_name, name AS table_name
FROM sys.tables
ORDER BY schema_name, table_name;

Două tabele numite Customer, în două scheme, într-o singură bază de date, pe o singură instanță. Complet denumite: LessonsPlayground.Sales.Customer și LessonsPlayground.HR.Customer. Pot coexista pentru totdeauna pentru că schemele le țin separate.

Lasă baza de date Runehold la locul ei — vom adăuga la ea în lecția următoare și în fiecare lecție de după. Dacă vrei vreodată să o iei de la zero:

USE master;
DROP DATABASE Runehold;

Lecția următoare: tipuri de date. Vom acoperi diferența dintre VARCHAR(50) și VARCHAR(MAX), de ce DATETIME2 bate DATETIME și singura dată când am văzut un tabel proiectat în întregime cu coloane NVARCHAR(MAX). A fost o zi proastă pentru toată lumea.

Caută