Diciamo che hai appena ottenuto un SQL Server fresco dalla lezione 2, e stai fissando l’albero dell’Object Explorer in SSMS. Vedi “Databases”, e sotto “System Databases”, e i database di sistema si chiamano master, model, msdb, e tempdb. Non hai ancora creato niente e ci sono già quattro database.
Se sei nuovo a SQL Server, questa parte del mondo è profondamente confusa. E va peggio: dentro un database c’è una cosa chiamata schema, che non è la stessa cosa che “schema” significa in Postgres, o nei tool ORM, o nella lingua italiana. E dentro gli schema ci sono tabelle, viste, stored procedure, e funzioni.
La lezione di oggi è per sbrogliare l’annidamento. Alla fine saprai esattamente cosa significa ogni livello, a cosa serve, e perché “metti tutto in dbo” è il singolo errore più comune che la gente fa quando inizia.
L’annidamento, dall’esterno verso l’interno
SQL Server ha uno spazio dei nomi a tre livelli per la maggior parte degli oggetti:
Instance → Database → Schema → ObjectOgni tabella, vista, procedura, e funzione ha un nome completo con tre punti:
DatabaseName.SchemaName.ObjectNamePer esempio: AdventureWorks2022.Sales.SalesOrderHeader. Il nome dell’istanza è implicito (è dovunque tu sia connesso al momento). Alcuni oggetti come login, server role, e linked server vivono al livello dell’istanza, non dentro un database — ma è l’eccezione. Per le cose di tutti i giorni contro cui scriverai query, il nome a tre parti è la legge.
Andiamo livello per livello.
L’istanza: a cosa ti connetti
Un’istanza è un processo SQL Server in esecuzione. Una macchina server può avere più istanze (quella di default, più istanze nominate come SERVERNAME\SQL2019 e SERVERNAME\SQL2022), ognuna con la sua porta, la sua configurazione, i suoi database, la sua memoria. È meno comune di una volta — nell’era cloud, “un’istanza per macchina” è la norma — ma incontrerai vecchie macchine on-prem che fanno girare tre istanze su un server, ognuna in ascolto su una porta diversa, perché qualcuno nel 2010 ha pensato fosse una buona idea.
Per questo corso, hai un’istanza, o localhost (se hai installato in locale) o localhost,1433 (se stai usando Docker). Tutto qui. Tutto il resto si annida dentro.
L’istanza è dove vivono i login. Un login è il modo in cui ti autentichi al server. I login mappano agli utenti dentro i database, ma ci arriveremo nella lezione 33 sulla sicurezza.
I database
Dentro un’istanza, hai i database. Ogni database ha il suo storage (i suoi file .mdf e .ldf), il suo ciclo di backup e restore, e i suoi utenti. Quando esegui CREATE DATABASE MyApp;, SQL Server crea due file — un data file e un log file — e registra il nuovo database in master.
Ogni istanza viene con quattro database di sistema che dovresti imparare a riconoscere:
master— il catalogo dell’istanza. Tiene traccia di quali database esistono, quali login esistono, quali linked server sono configurati, e le impostazioni generali a livello server. Semastermuore, l’intera istanza muore. Fanne il backup. Trattalo con rispetto.model— il template. Ogni volta che eseguiCREATE DATABASE, SQL Server copiamodele lo usa come punto di partenza. Se vuoi che ogni nuovo database parta con un set di default di tabelle, utenti, o proprietà estese, le metti inmodel. Il 95% delle volte lascimodelin pace.msdb— dove vive SQL Server Agent. Job pianificati, storia dei job, storia dei backup, database mail, maintenance plan. Se ti sei mai chiesto “quando è stato eseguito l’ultimo backup?” la risposta è inmsdb.tempdb— uno spazio temporaneo condiviso da ogni sessione sull’istanza. Tabelle temporanee, table variable, sort spill, dati del version store per snapshot isolation, e tante altre cose finiscono qui.tempdbsi resetta ogni volta che SQL Server viene riavviato. È anche la singola fonte più comune di sorprese di performance su un server occupato, e ci passeremo un’intera lezione più avanti (lezione 34).
Poi hai i database utente — quelli che crei per le tue applicazioni. Ognuno ha il suo nome, le sue impostazioni, i suoi utenti. Un’istanza SQL Server può ospitare migliaia di database; in pratica, le aziende si tengono sulle decine.
Cambiare tra database dentro SSMS si fa con il dropdown in cima alla finestra di query, o eseguendo USE DatabaseName; in cima a uno script. USE è uno statement T-SQL che cambia su quale database è puntata la tua sessione.
USE master;
SELECT name, database_id, create_date, state_desc
FROM sys.databases
ORDER BY database_id;Esegui questa e vedrai i tuoi database di sistema più gli eventuali database utente che hai creato. Il database_id per master è sempre 1, tempdb è 2, model è 3, msdb è 4. I tuoi database utente partono da 5.
Gli schema, e perché contano
Dentro un database, gli oggetti sono organizzati in schema. Uno schema è uno spazio dei nomi logico. Esiste perché tu possa dire Sales.Customer e HR.Customer senza che siano lo stesso oggetto.
Gli schema sono una funzionalità che la maggior parte della gente ignora, il che è un peccato. In AdventureWorks vedrai Person.*, Sales.*, HumanResources.*, Production.*, Purchasing.*. Ogni schema raggruppa tabelle correlate: Person.Address, Person.EmailAddress, Person.PersonPhone. Quel raggruppamento è proprio il punto.
Lo schema di default in SQL Server si chiama dbo (abbreviazione di “database owner”). Quando esegui CREATE TABLE Foo; senza un prefisso di schema, SQL Server crea dbo.Foo. Va bene per un database demo minuscolo. È un disastro per qualsiasi applicazione vera.
Perché dbo non è davvero tuo amico:
- Ogni oggetto che non collochi esplicitamente in uno schema finisce in
dbo. Sei mesi dopo, hai 340 tabelle indbosenza alcuna struttura riconoscibile. - Le permission basate su schema ti permettono di dire “tutti gli utenti nel ruolo Reporting possono fare SELECT su ogni tabella nello schema Reporting”. Concedere per tabella è un incubo.
- Diversi moduli applicativi (Sales, Billing, HR, Ops) si pestano i piedi quando condividono tutti
dbo. La segregazione per schema permette ai moduli di evolvere in modo indipendente. - Quando devi spostare una tabella più tardi, gli spostamenti tra schema sono economici e ben supportati. Rinominare metà delle tue tabelle
dbonon lo è.
La regola che do ai junior nel mio team: non creare mai niente in dbo. Anche un proof-of-concept usa-e-getta riceve il suo schema dedicato, nominato per quello che fa. Staging.CustomerImport, non dbo.Stage_Customer_Import_Temp.
Creare uno schema è una riga:
CREATE SCHEMA Sales AUTHORIZATION dbo;“AUTHORIZATION dbo” significa che lo schema è di proprietà dell’utente dbo. Puoi cambiare la proprietà in seguito. Creare un oggetto in uno schema specifico è solo questione di prefissare il nome:
CREATE TABLE Sales.Customer (
CustomerId INT NOT NULL PRIMARY KEY,
Name NVARCHAR(100) NOT NULL
);E le query usano il nome a due parti:
SELECT CustomerId, Name FROM Sales.Customer;Puoi cavartela con SELECT * FROM Customer perché SQL Server cerca prima nel tuo schema di default (di solito dbo), ma scrivere nomi pienamente qualificati è un’abitudine che vale la pena costruire. Toglie ambiguità, è più facile da leggere sei mesi dopo, e migliora il tuo gioco di grep quando vai a caccia di riferimenti.
Tabelle: la cosa per cui sei venuto
Dentro uno schema vive l’oggetto a cui tutti tengono davvero: le tabelle. Una tabella è un rettangolo di dati: colonne fisse con nomi e tipi, e zero o più righe. Ogni riga è un’istanza dell’entità che la tabella rappresenta. Una tabella Sales.Customer ha righe dove ognuna è un singolo cliente.
Le tabelle si creano con 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)
);Qualche cosa da notare, perché sono fondamentali:
- Ogni colonna ha un tipo di dato.
INT,NVARCHAR(100),DATETIME2(0),BIT. Passeremo un’intera lezione sui tipi la prossima volta, perché sbagliarli costa soldi veri. Per ora, sappi che SQL Server è fortemente tipizzato:'42'e42non sono la stessa cosa, e mischiarli causa conversioni implicite che non vuoi. - Ogni colonna può essere
NOT NULLo implicitamente nullable. Sii sempre esplicito. ScrivereNOT NULLsulle colonne che non dovrebbero accettareNULLti salva da intere categorie di bug. IDENTITY(1,1)fa sì che SQL Server auto-generi valori interi sequenziali. Seme 1, incremento 1. Equivalente aAUTO_INCREMENTin MySQL oSERIALin Postgres.DEFAULTdà a una colonna un valore se l’INSERTnon ne specifica uno.SYSUTCDATETIME()è la funzione built-in per “adesso, in UTC”. Usa UTC, sempre. Tratteremo il dolore della gestione delle date nella lezione 15.- La riga
CONSTRAINT pk_Customer PRIMARY KEY CLUSTERED (CustomerId)crea sia una primary key sia un indice clustered suCustomerId. In SQL Server, una primary key ottiene un indice clustered di default a meno che tu non dica altrimenti. Vedi la lezione 21 per la storia completa sugli indici clustered — sono probabilmente l’idea più importante di tutto il corso.
Altre cose che vivono in uno schema
Gli schema contengono di più che tabelle. Contengono anche:
- Viste — query salvate che sembrano tabelle.
- Stored procedure — T-SQL salvato che puoi chiamare con
EXEC. - Funzioni — scalari o table-valued; chiamate come una funzione dentro una query.
- Trigger — T-SQL che gira automaticamente su
INSERT,UPDATE,DELETE, o DDL. - Tipi definiti dall’utente — tipi di dati custom costruiti sopra quelli built-in.
- Synonym — alias locali per oggetti in altri database.
Tutti questi sono organizzati per schema. Sales.GetActiveCustomers è una procedura. Reporting.CustomerSummary è una vista. Staging.LoadOrders è una procedura in uno schema staging che usi per ETL. I confini di schema sono un regalo. Accetta il regalo.
Elencare cosa c’è in un database
Due query da memorizzare. Prima, “quali tabelle esistono nel database corrente e in che schema sono?”
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;Seconda, “quali schema esistono in questo database?”
SELECT name AS schema_name,
SCHEMA_OWNER = USER_NAME(principal_id) AS owner
FROM sys.schemas
ORDER BY name;Queste due diventeranno riflesse in un mese. Ogni volta che entri in un nuovo team o apri un database non familiare, eseguile per prime per orientarti.
Esegui questo sulla tua macchina
Un piccolo esercizio per rendere tangibili i tre livelli. Costruiremo una versione minuscola dello schema Runehold che estenderemo durante tutto il corso. Incolla questo in una finestra di query ed esegui tutto:
-- 1. Crea il database Runehold
IF DB_ID('Runehold') IS NULL
CREATE DATABASE Runehold;
GO
USE Runehold;
GO
-- 2. Crea due schema che appartengono a due team diversi
CREATE SCHEMA Sales AUTHORIZATION dbo;
GO
CREATE SCHEMA HR AUTHORIZATION dbo;
GO
-- 3. Ogni team ha un concetto di "Customer" con un significato diverso.
-- Sales.Customer = persone che ci comprano beni incantati.
-- HR.Customer = il nome interno che HR usa per "dipendenti" (strano ma
-- succede nelle aziende vere tutti i giorni).
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. Inserisci dati realistici
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. Interroga entrambi, pienamente qualificati
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. Elenca cosa hai appena costruito
SELECT SCHEMA_NAME(schema_id) AS schema_name, name AS table_name
FROM sys.tables
ORDER BY schema_name, table_name;Due tabelle chiamate Customer, in due schema, dentro un database, su un’istanza. Pienamente nominate: LessonsPlayground.Sales.Customer e LessonsPlayground.HR.Customer. Possono coesistere per sempre perché gli schema le tengono separate.
Lascia il database Runehold al suo posto — lo arricchiremo nella prossima lezione e in ogni lezione successiva. Se mai vuoi ripartire da zero:
USE master;
DROP DATABASE Runehold;Prossima lezione: i tipi di dato. Copriremo la differenza tra VARCHAR(50) e VARCHAR(MAX), perché DATETIME2 batte DATETIME, e quella volta che ho visto una tabella progettata interamente con colonne NVARCHAR(MAX). È stata una brutta giornata per tutti.