TUTORIAL SQL SERVER - PARTE III

Terza parte del tutorial dedicato ad SQL Server, il software RDBMS di casa Microsoft utilizzato in moltissime applicazioni professionali di medio-alto livello. In questo articolo vedremo come creare le nostre tabelle, le viste e come effettuare le varie interrogazioni sia guidate che tramite codice TSQL direttamente.

La versione presa in considerazione in questa guida è Microsoft SQL Server 2012.

Creazione di una tabella

Tralasciando quella che è la teoria dei database relazionali affrontiamo il tema pratico e diretto delle tabelle. Una tabella è un insieme di dati divisi in colonna ripetuti in righe, a tutti gli effetti equiparabile ad un foglio Excel dove ogni colonna ha un determinato formato (numero, testo, data...). Queste tabelle possono contenere una mastodontica quantità di dati ed è possibile effettuare delle interrogazioni per ottenere un sotto insieme di dati utili ad un nostro scopo. Una o più colonne possono formare la "chiave" di una tabella, una informazione che discrimina in modo univoco una riga della tabella.

Colleghiamoci al nostro motore di database tramite SQL Server Management Studio come visto nel primo articolo. Selezioniamo il database che abbiamo creato nel secondo articolo.

Degli elementi che compongono il nostro database, i fondamentali sono:

• Database Diagrams
• normalmente vuoto. Può contenere una serie di "diagrammi", rappresentazioni grafiche delle relazioni fra le tabelle che aiutano a comprendere il modello relazionale del database. Durante la definizione di un diagramma vengono automaticamente create le dipendenze fra le tabelle.
• Tables
• Contiene le tabelle del database, sia quelle definite dall'utente che quelle di sistema.
• Views
• Contiene le viste del database, sia quelle definite dall'utente che quelle di sistema.
• Programmability
• Contiene funzioni, trigger e procedure, sia quelle definite dall'utente che quelle di sistema.
• Security
• Contiene la definizione specifica dei livelli di sicurezza associati agli utenti e gruppi SQL Server nell'ambito dello specifico database.

Per creare una nuova tabella è sufficiente fare un tasto destro con il mouse sulla voce "Tables", quindi selezionare la voce "New Table". 

L'editor permette di definire le colonne che comprenderanno la nostra tabella. Per ogni colonna è possibile definire:

• Il nome della colonna. Le limitazioni riguardo i caratteri che sono ammessi sono riportate in questo articolo su MSDN.
• Il tipo di dato. Ce ne sono molti, ogni versione di SQL Server ne aggiunge di nuovi. Fondamentalmente ci sono lettere e numeri, nel caso di valori di tipo stringa può essere specificata la lunghezza (ad esempio char(10) è una stringa di 10 caratteri).
• Il flag di NULL. Indica se, per quella colonna, sono ammessi valori NULL (non valorizzati).

Una volta composta la nostra tabella, è possibile salvare tramite il tasto salva, il sistema vi presenterà un form per l'inserimento del nome della tabella.

Best Practice

Giusto due consigli riguardo il nome da dare alle tabelle e alle colonne:

• Tutti i nomi scritti con caratteri MAIUSCOLI e in lingua INGLESE
• I nomi delle tabelle plurali (non USER ma USERS, non REPORT ma REPORTS)
• I nomi delle colonne che richiamano un prefisso della tabella al singolare (la tabella USERS avrà le colonne USER_NAME USER_SURNAME USER_PASSWORD...)

Questi sono i pochi ma utili consigli per realizzare un database il più chiaro e ordinato possibile. A voi la scelta se condividere o meno queste convenzioni. 

Char, NChar, VarChar, NVarChar... che mal di testa!

Più di una volta ho visto usare questo tipo di dato in maniera sconsiderata, una volta per tutte:

• Char: contiene un array di dimensione fissa di caratteri ASCII
• NChar: contiene un array di dimensione fissa di caratteri Unicode
• VarChar: contiene un array di dimensione variabile di caratteri ASCII(il numero specificato nell'array è la dimensione massima)
• NVarChar: contiene un array di dimensione variabile di caratteri Unicode (il numero specificato nell'array è la dimensione massima)

Vediamo di analizzare l'occupazione di byte in memoria:

• CHAR(10)    = 1byte x 10 = 10 byte
• NCHAR(10)   = 2byte x 10 = 20 byte
• VARCHAR(10) = 1byte x N = N byte (dove N è il numero effettivo di caratteri)
• NVARCHAR(10)= 2byte x N = 2N byte (dove N è il numero effettivo id caratteri)

Chiave primaria

Esistono due tipi di chiave in SQL Server:

1. Chiave Primaria
2. Chiave Esterna

Una Chiave Primaria è una informazione formata da una o più colonne che discrimina in modo univoco una riga della tabella dalle altre. Non è possibile effettuare l'inserimento di una riga che possiede la medesima chiave primaria di un record già presente nella tabella.

Una Chiave Esterna è una chiave primaria relativa ad un'altra tabella del database. Viene ammesso l'inserimento di una riga che possiede la medesima chiave esterna di un record già presente nella tabella.

Per definire la chiave primaria di una tabella è sufficiente selezionare una o più colonne nell'editor della tabella, accedere al menu contestuale, quindi selezionare la voce "Set Primary Key".

Notare che, quando una colonna viene definita chiave primaria, il campo "Allow Nulls" viene automaticamente deselezionato. 

Il nostro esempio

Per continuare nel corso di questo articolo faremo riferimento sempre ad un unico esempio. Supponiamo di avere la seguente situazione:

• tabella FOLDERS
• FOLDER_NAME nvarchar(10) - chiave primaria
• FOLDER_INFO nvarchar(100)
• tabella FILES
• FILE_NAME nvarchar(10) - chiave primara
• FILE_INFO nvarchar(100)
• FILE_SIZE int
• FILE_ISLOCKED bit
• tabella FOLDERS_FILES
• FOLDER_NAME nvarchar(10) - chiave esterna tabella FOLDERS
• FILE_NAME nvarchar(10) - chiave esterna tabella FOLDERS

Per effettuare dei test popoliamo le tabelle (tasto destro su una tabella, voce Edit top 200 rows):

Interrogazione guidata

Dopo aver creato le tabelle sopra citate, proviamo a fare qualche interrogazione di base. Iniziamo interrogando la nostra tabella FILES selezionandola dal menu ad albero sulla sinistra e selezionando la voce "Edit top 200 rows" del menu contestuale.

Il risultato della query viene mostrato sotto forma tabellare. Questa è una query di base che comprende i primi 200 risultati disponibili per la tabella richiesta. Se vogliamo applicare dei filtri, in maniera guidata, è necessario premere sulla icona di struttura in alto a sinistra (Criteria Pane).

Il Criteria Pane permette le seguenti operazioni:

1. visualizzare o meno una colonna come risultato
2. rinominare una colonna per inserire un testo a piacere come risultato
3. ordinare i risultati in ordine crescente o decrescente di una determinata colonna
4. filtrare i risultati secondo una o più colonne e caratteristiche
5. concatenare in AND o in OR le clausole di interrogazione

Proviamo a fare una query semplice semplice volendo ricavare dalla tabella FILES tutti i file con dimensione maggiore di 100. Andremo a compilare la Criteria Pane come mostrato:

Per fare il refresh del risultato premere la combinazione di tasti CTRL+R oppure il punto esclamativo rosso nella toolbar in alto.

Per ulteriori dettagli sulla interrogazione guidata tramite Criteria Pane è sufficiente premere il tasto F1 della tastiera ed accedere all'help Microsoft ufficiale di questo editor. In ogni caso, dopo un po' di esperienza, un programmatore difficilmente utilizza il Criteria Pane, preferendo la scrittura a mano di query con sintassi TSQL.

Criteria Pane può essere comodo anche per imparare il linguaggio TSQL. Una volta applicati in maniera guidata i vari filtri è sufficiente premere sul tasto SQL della toolbar per visualizzare il codice SQL corrispondente:

Nel nostro caso, la query corrispondente all'interrogazione guidata che abbiamo appena fatto, riporta il seguente risultato:

SELECT * FROM FILES WHERE FILE_SIZE > 100

Creazione di una vista

Una vista è una specie di tabella ottenuta mediante una interrogazione verso una o più tabelle, secondo criteri specifici. Una Vista è praticamente una query SQL che viene memorizzata all'interno del database per evitare di ripetere sempre le stesse interrogazioni. 

In realtà una Vista, all'interno del motore del database Microsoft SQL Server, viene ottimizzata per ridurre i tempi di caricamento. In questo articolo del TN Microsoft è possibile approfondire l'argomento.

Per creare una vista è necessario selezionare la sezione "Views" nel menu ad albero sulla sinistra, quindi selezionare la voce "New View" dal menu contestuale:

L'editor di composizione di una vista è molto simile al Criteria Pane, con la differenza che è possibile inserire più di una tabella.

Supponiamo di voler ottenere come risultato una tabella composta da FILE_NAME e FOLDER_NAME a seconda di quanto riportato nella tabella FOLDERS_FILES. (ad esempio per la folder DOC ci saranno i file DOC_1 e DOC_2). Per ottenere un risultato congiunto dobbiamo eseguire una operazione di JOIN fra tabelle. 

Iniziamo selezionando le tabelle dalla finestra di dialogo:

A questo punto dobbiamo associare i vari dati delle tabelle. Iniziamo dalla associazione fra FILES e FOLDER_FILES. Nella tabella FOLDER_FILES esistono due chiavi esterne: FILE_NAME e FOLDER_NAME. Per tracciare una associazione di JOIN è sufficiente trascinare il campo FILE_NAME della tabella FILES sul campo FILE_NAME della tabella FOLDERS_FILES:

Effettuare la stessa operazione con il campo FOLDER_NAME della tabella FOLDERS verso il campo FOLDER_NAME della tabella FOLDERS_FILES. In questo modo abbiamo tracciato i criteri di JOIN fra le tabelle. Dato che vogliamo solo FILE_NAME e FOLDER_NAME andiamo a selezionare le caselle FILE_NAME della tabella FILES e la casella FOLDER_NAME della tabella FOLDERS.

Se abbiamo effettuato tutto correttamente il risultato sarà il seguente:

A questo punto possiamo decidere di salvare la vista o perdere le modifiche. Se decidiamo di salvare sarà richiesto l'inserimento del nome della vista.

Dal momento che una vista viene salvata è possibile eseguire interrogazioni guidate o manuali come se fosse una normale tabella del database.

Come nel caso del Criteria Pane è possibile utilizzare le viste per ricavare il codice TSQL corrispondente a ciò che è stato selezionato, nel nostro caso il codice è il seguente:

SELECT dbo.FILES.FILE_NAME, dbo.FOLDERS.FOLDER_NAME 
FROM dbo.FILES 
INNER JOIN dbo.FOLDERS_FILES 
ON dbo.FILES.FILE_NAME = dbo.FOLDERS_FILES.FILE_NAME 
INNER JOIN dbo.FOLDERS 
ON dbo.FOLDERS_FILES.FOLDER_NAME = dbo.FOLDERS.FOLDER_NAME

Best Practice

Normalmente le viste vengono condividono il prefisso VW seguito da un testo autoesplicativo della interrogazione che contiene. Se volessimo dare un nome preciso alla vista appena creata, il nome migliore sarebbe VW_FILESNAME_FOLDERSNAME o direttamente VW_FOLDERS_FILES.

Query SQL

Il 99% delle volte in cui dovrete fare delle interrogazioni "di un certo livello", il Criteria Pane non sarà facile da configurare. Dobbiamo quindi ricorrere al linguaggio TSQL.

Per una guida completa al linguaggio TSQL vi consiglio di dare un'occhiata alla guida W3Cschool SQL o alla guida ufficiale Microsoft (preferisco la guida W3C leggermente più alla portata dei principianti).

Supponiamo di voler interrogare in maniera avanzata il nostro database per scoprire la dimensione di ogni FOLDERS secondo la somma dei FILES associati tramite la tabella FOLDERS_FILES (ad esempio la dimensione della folder DOC sarà 250 = 100 + 150)

Per scrivere manualmente una query è sufficiente premere la combinazione di tast CTRL+N o selezionare il bottone "New Query" dal menu in alto.

Come base di partenza per la nostra interrogazione complessa utilizzeremo la vista appena creata rimuovendo la selezione esclusiva del nome del file e del nome della folder:

SELECT * 
FROM dbo.FILES 
INNER JOIN dbo.FOLDERS_FILES 
ON dbo.FILES.FILE_NAME = dbo.FOLDERS_FILES.FILE_NAME 
INNER JOIN dbo.FOLDERS 
ON dbo.FOLDERS_FILES.FOLDER_NAME = dbo.FOLDERS.FOLDER_NAME

Andiamo ad aggiungere il fatto che vogliamo il nome della folder e la somma dei size dei file associati (dobbiamo aggiungere anche un raggruppamento per folder_name):

SELECT dbo.FOLDERS.FOLDER_NAME,SUM(FILE_SIZE) 
FROM dbo.FILES 
INNER JOIN dbo.FOLDERS_FILES 
ON dbo.FILES.FILE_NAME = dbo.FOLDERS_FILES.FILE_NAME 
INNER JOIN dbo.FOLDERS 
ON dbo.FOLDERS_FILES.FOLDER_NAME = dbo.FOLDERS.FOLDER_NAME
GROUP BY dbo.FOLDERS.FOLDER_NAME

Se abbiamo configurato tutto correttamente il risultato ottenuto sarà simile a quello mostrato in figura:

Le query SQL che vengono create manualmente possono essere salvate su file con estensione *.sql per essere poi eseguiti in un secondo momento.

Verso la Parte IV

Nel prossimo articolo vedremo come creare trigger sulle tabelle e come scrivere procedure. Parleremo anche dei pregi e dei difetti nel tenere una porzione di codice della propria applicazione all'interno del database SQL.