Guida SQL Server 2008 1. Introduzione 2. Sistemi di database 3. Panoramica di SQL Server 2008 4. Installazione 5. SQL Server Management Studio 6. Elementi di un database e database di sistema 7. Linee guida per la progettazione di un database 8. Sicurezza 9. Creare un database 10. Componenti SQL e tipi di dati 11. Funzioni predefinite, operatori e variabili globali 12. Definire e creare tabelle 13. Chiavi e relazioni 14. Indici 15. Diagrammi 16. Lavorare con i dati 17. Interrogare i dati 18. Transazioni 19. Stored procedure e funzioni 20. Viste 21. Trigger 22. Reporting Services 23. Considerazioni sulle prestazioni

Guida scritta da Vincenzo Gaglio1

1. IntroduzioneSQL Server un sistema di gestione di database (DBMS DataBase Management System) che si colloca in un mercato in cui si trova a competere con diversi altri DBMS ed in particolare con Oracle e Sybase. Oracle un DBMS largamente utilizzato e non si pu negare che rappresenti un ottimo prodotto con cui lavorare, sebbene sia pi complesso da installare e amministrare rispetto a SQL Server. Si tratta di una soluzione scelta da compagnie di grandi dimensioni per le sue caratteristiche di scalabilit, per le prestazioni e perch fornisce una grande flessibilit riguardo agli strumenti addizionali che si possono utilizzare. Tuttavia esso dal punto di vista dei programmatori non si presenta cos semplice da utilizzare come SQL Server, che fornisce strumenti ad hoc per la progettazione di strutture di database complete. Sybase molto simile a SQL Server ma presenta un grosso limite: lassenza di uninterfaccia grafica che permetta agli utenti di interagire con il database. Questo DBMS solitamente viene utilizzato su macchine Unix, veloce e robusto ma non fornisce il ricco linguaggio di programmazione e le funzionalit avanzate di SQL Server. Ogni database ha la propria sintassi e i tre DBMS citati utilizzano la stessa, conosciuta con il nome di ANSI-92. Questo significa che la sintassi per interrogare i dati e per svolgere le altre operazioni uguale nei tre sistemi, ma ciascun database ha una propria sintassi speciale per effettuare operazioni avanzate. SQL Server sembra essere la soluzione giusta sia per realt di grandi dimensioni, sia per ambiti pi ristretti perch a costi minori rispetto ad Oracle fornisce una soluzione molto scalabile e performante. Inoltre, come vedremo, SQL Server molto semplice da installare e viene fornito in diverse versioni a seconda delle funzionalit che si desidera utilizzare. Il DBMS di casa Microsoft stato lanciato nel 1988 e ha subito diversi miglioramenti e restyling nel corso degli anni fino a giungere alla versione 2008, oggetto della presente guida. Le modifiche pi significative in realt sono state introdotte con SQL Server 2005, in cui varie aree sono state riprogettate e migliorate rispetto alle versioni precedenti e in cui stata realizzata lintegrazione con le funzionalit del .NET Framework. Lobiettivo di SQL Server 2008 quello di gestire tutte le forme possibili di dati. Esso si basa sullinfrastruttura di SQL Server 2005 ma offre nuovi tipi di dati, nuove funzionalit e la possibilit di utilizzare il Language Integrated Query, conosciuto con il nome di LINQ. Esso permette inoltre di operare su dati in formato XML e tante altre funzionalit molto interessanti che analizzeremo nel corso della guida. SQL Server 2008 disponibile nelle seguenti versioni:

SQL Server 2008 Enterprise - Piattaforma integrata per la gestione dati e la business intelligence, in grado di fornire scalabilit, applicazioni di data warehousing, sicurezza, analisi e report avanzati. Con questa edizione possibile effettuare processi transazionali online su larga scala. SQL Server 2008 Standard - Piattaforma di gestione dati e business intelligence dotata di facilit d'uso e di gestione per eseguire applicazioni relative tipicamente ai singoli dipartimenti aziendali.

2

SQL Server 2008 Workgroup - Piattaforma di gestione dati e creazione report che offre funzionalit di gestione e sincronizzazione remota sicure per l'esecuzione di applicazioni di filiale. Questa edizione include caratteristiche chiave del database ed facilmente aggiornabile alledizione Standard o Enteprise. SQL Server 2008 Web - Appositamente progettata per gli ambienti dedicati ai servizi web ad elevata disponibilit eseguibili su Windows Server. SQL Server 2008 Web fornisce qualsiasi tipo di supporto per tutte le applicazioni web. SQL Server 2008 Developer - Permette agli sviluppatori di realizzare e testare qualsiasi tipo di applicazione con SQL Server. Questa edizione comprende tutte le funzionalit di SQL Server Enterprise, ma la licenza limitata solo allo sviluppo, al test e alle demo. Le applicazioni e i database sviluppati su questa edizione possono essere facilmente aggiornati alla versione Enterprise. SQL Server 2008 Express - Edizione gratuita di SQL Server per imparare a costruire applicazioni desktop o per piccoli server. SQL Server Compact 3.5 - Versione gratuita ideale per costruire applicazioni standalone e occasionalmente connesse per dispositivi mobili, desktop e client web. SQL Server Compact supportato da tutte le piattaforme Windows, inclusi i sistemi operativi Windows XP e Windows Vista, e da tutti i Pocket PC e gli smartphone.

Per quanto riguarda i requisiti hardware Microsoft raccomanda per SQL Server 2008 un processore da almeno un 1GHz per le versioni a 32 bit e da 1.6GHz per le versioni a 64 bit. Almeno 512 MB di memoria RAM, anche se la quantit di memoria influenza molto le prestazioni e quindi sarebbe meglio averne a disposizione almeno 1GB (soprattutto per utilizzare la versione Enterprise di SQL Server). E inoltre opportuno avere a disposizione un hard disk da almeno 80 GB, mentre per quanto riguarda il sistema operativo per il lato client possibile utilizzare Windows 7, Vista o XP, mentre per il lato server Windows Server 2003 (con service pack 2) o Windows Server 2008.

2. Sistemi di databaseUn sistema di database un insieme di diversi componenti tra cui:

Programmi per la gestione di database Componenti client Uno o pi server di database Diversi database

Un programma per la gestione di un database (DBMS DataBase Management System), come SQL Server, un software tecnicamente specifico che viene progettato ed implementato da compagnie specializzate nel settore. Di contro un componente client un software generico che permette agli utenti di consultare i dati presenti su un database. I server di database servono per la gestione dai dati contenuti in vari database e ogni componente client comunica con essi inviando opportune query che vengono processate ed il cui risultato viene inviato come risposta al client. Un database invece pu essere visto da due prospettive differenti. La prima quella dellutente che lo vede come una collezione di dati correlati; la seconda quella del sistema di gestione di database (DBMS) che lo vede semplicemente come una serie di byte, tipicamente memorizzati su un disco rigido.

3

E fondamentale che un sistema di database fornisca alcune caratteristiche e meccanismi fondamentali:

Variet di interfacce utente Indipendenza fisica dei dati Indipendenza logica dei dati Ottimizzazione delle query Integrit dei dati Controllo di concorrenza Funzionalit di backup e ripristino Funzionalit di sicurezza

La maggior parte di tali caratteristiche e funzionalit sono assicurate dal DBMS utilizzato. Variet di interfacce utente Molti database sono progettati per essere utilizzati da diversi tipi di utenti con vari livelli di conoscenza. Per tale ragione un DBMS dovrebbe offrire diverse interfacce utente a diversi target di utenti. Indipendenza fisica dei dati Indipendenza fisica dei dati significa che le applicazioni che operano sui dati di un database non dipendono dalla struttura fisica dei dati memorizzati. Questa importante caratteristica permette di effettuare modifiche ai dati senza dover modificare i programmi applicativi che si interfacciano con gli stessi. Per esempio se i dati memorizzati allinterno del database prima sono ordinati secondo un certo criterio e poi tale criterio viene variato, tale modifica fisica non influisce n sulle applicazioni esistenti, n sullo schema del database stesso. Indipendenza logica dei dati Indipendenza logica dei dati significa che possibile effettuare modifiche alla struttura logica di un database senza dover effettuare alcuna modifica ai programmi che operano sul database stesso. Ottimizzazione delle query Ogni DBMS contiene un componente chiamato ottimizzatore (optimizer) che considera diverse strategie di esecuzione per linterrogazione dei dati e poi seleziona quella pi efficiente. La strategia selezionata chiamata piano di esecuzione (execution plan) della query. Integrit dei dati Uno degli obiettivi fondamentali di un DBMS quello di identificare dati logicamente inconsistenti (ad esempio la data 30 febbraio) ed impedire la relativa memorizzazione su un database. Questo obiettivo pu essere assicurato dai programmi applicativi o dal DBMS stesso. Controllo di concorrenza Un database pu essere interrogato e modificato da diverse applicazioni nello stesso momento e per tale motivo un DBMS deve avere qualche tipo di meccanismo di controllo per assicurare che le diverse applicazioni che tentano di modificare gli stessi dati lo facciano in un modo controllato.

4

Funzionalit di backup e ripristino Un DBMS deve avere un sistema in grado di effettuare il ripristino dei database in caso di errori hardware o software. Funzionalit di sicurezza La sicurezza di un database si basa su due concetti fondamentali: autenticazione e autorizzazione. Lautenticazione il processo di validazione delle credenziali degli utenti per prevenire utilizzi non consentiti del sistema. Tipicamente tale obiettivo viene raggiunto utilizzando username e password. Lautorizzazione il processo successivo allautenticazione e consiste nellindividuazione delle risorse che un determinato utente pu utilizzare dopo essersi autenticato.

3. Panoramica di SQL Server 2008Lobiettivo di Microsoft con il rilascio di SQL Server 2008 quello di facilitare e migliorare il lavoro degli amministratori di database e dei programmatori di applicazioni che si interfacciano con tali sistemi. Per raggiungere questo obiettivo la versione 2008 del DBMS (Data Base Management System) offre una grande variet di nuove caratteristiche e funzionalit.

In particolare gli amministratori di database che utilizzano gi SQL Server 2005 troveranno nella versione 2008 tutti gli strumenti che utilizzano quotidianamente nel proprio lavoro e noteranno che molti di essi sono stati arricchiti e migliorati. E possibile suddividere le novit principali in varie categorie: gestione, scalabilit, prestazioni, sicurezza, sviluppo. Gestione La prima caratteristica da citare nellambito della gestione dei database la gestione delle policy (Policy Management). Le policy sono insiemi di regole che possono essere stabilite su un server per fare in modo che tutte le workstation collegate siano gestite allo stesso modo e presentino le stesse caratteristiche. Tale gestione permette dunque di creare ed eseguire diverse regole di configurazione su uno o pi server di database. Tramite tali regole lamministratore del database pu stabilire che la configurazione standard dei settaggi venga applicata e mantenuta su ciascuno dei server e dei database coinvolti. Altra funzionalit da citare la cosiddetta Multiple Server Interaction and Configuration Servers, che permette di eseguire nello stesso momento interrogazioni su dati residenti su diversi server. E possibile registrare i diversi server nel Management Studio di SQL Server ed inserirli in uno stesso gruppo. Quando poi si rende necessario applicare una policy o eseguire una query su tutti i server del gruppo basta cliccare con il tasto destro del mouse sul gruppo ed effettuare loperazione desiderata. Questa funzionalit pu essere configurata per restituire un insieme di risultati per ogni server oppure per unire tutti i risultati in un unico insieme. 5

Unaltra interessante nuova funzionalit di gestione il Data Collector. Spesso gli amministratori di database infatti hanno la necessit di raccogliere dati di gestione da un diverso numero di server. Il Data Collector un meccanismo che facilit questa attivit e permette di utilizzare il SQL Server Agent e Integration Services per creare un frame work che raccolga e memorizzi i dati di interesse, fornendo anche funzionalit di gestione degli errori. Scalabilit Nel corso degli anni le dimensioni di un database tendono a crescere in modo considerevole e sono necessari strumenti per gestire questo aspetto. Per tale motivo SQL Server 2008 introduce alcune nuove caratteristiche che semplificano queste operazioni ed in particolare mette a disposizione una funzionalit di compressione predefinita dei file dei database e dei file relativi ai transaction log. Anche SQL Server 2005 permetteva la compressione dei dati in un file di sola lettura, utilizzando la modalit di compressione di Windows, ma nella versione 2008 possibile anche scegliere il livello di compressione (a livello di riga o di pagina). Questi tipi di compressione riducono la quantit di spazio richiesto e la quantit di memoria necessaria a manternere i dati, con i relativi ovvi benefici. SQL Server 2008 introduce anche la compressione a livello di backup. Un altro strumento molto utile il cosiddetto Resource Governor (gestore delle risorse). Questa funzionalit serve a gestire il carico di lavoro e l'utilizzo delle risorse di sistema da parte di SQL Server, consentendo di specificare limiti sulla quantit di CPU e memoria che le richieste dell'applicazione in ingresso possono utilizzare. Questo si traduce in un utilizzo pi efficiente delle risorse del sistema. Prestazioni Grazie a diverse nuove caratteristiche di SQL Server 2008 possibile controllare e monitorare in modo molto preciso le prestazioni dei database e delle applicazioni che si interfacciano con essi. Quando vengono eseguite un gran numero di transazioni al secondo i blocchi, necessari durante lesecuzione delle stesse, potrebbero avere un impatto negativo sulle prestazioni delle applicazioni che operano sullo stesso database. SQL Server progettato proprio per ridurre il numero totale di blocchi generati dai vari processi e lavora su un meccanismo di partizionamento delle tabelle. Sicurezza SQL Server 2005 ha introdotto la sicurezza dei dati tramite lutilizzo della crittografia ma con SQL Server 2008 tale aspetto stato migliorato con lintroduzione di due nuove caratteristiche: Extensible Key Management e Transparent Data Encryption. Il primo strumento fornisce opportuni meccanismi per memorizzare in modo sicuro le chiavi utilizzate nellinfrastruttura di crittografia. Il secondo offre una maggiore flessibilit nella crittografia dei dati, rendendo la crittografia una propriet del database e non soltanto il risultato di una funzione, facilitando il lavoro degli amministratori quando essi vogliono effettuare la crittografia a livello di dati. Sviluppo SQL Server 2008 naturalmente fornisce una grande variet di nuove caratteristiche volte a migliorare e facilitare il lavoro dei programmatori di database che vanno da miglioramenti del linguaggio T-SQL a nuovi componenti per creare ed utilizzare query. Una prima cosa da sottolineare che stato migliorato il supporto a LINQ creando un nuovo provider LINQ to SQL che permette di effettuare interrogazioni LINQ direttamente sulle tabelle del database.

6

Sono stati introdotti vari miglioramenti al linguaggio T-SQL. Un esempio la nuova istruzione MERGE che permette ai programmatori di verificare lesistenza dei dati prima di inserirli in tabella. Tramite questa istruzione non pi necessario creare join complessi per verificare lesistenza dei dati al fine di effettuare un aggiornamento degli stessi o un nuovo inserimento. A livello di tipi di dati sono stati separati i tipi date e time con lintroduzione di due nuovi tipi di dati e sono stati introdotti due nuovi tipi di dati spaziali: geography e geometry. Questultimi consentono di memorizzare direttamente su database informazioni spaziali. Un altro nuovo tipo di dati il filestream, introdotto per risolvere il problema della memorizzazione e dellutilizzo di oggetti binari di dimensioni consistenti, come documenti e file multimediali. In passato infatti il metodo utilizzato era quello di memorizzare i file esternamente al database e mantenere nel database un puntatore ad essi. In questo modo per quando i file venivano spostati era necessario aggiornare anche il puntatore relativo. Con il nuovo tipo filestream i file possono ancora essere memorizzati allesterno del database ma essi vengono considerati parte dello stesso. Chiaramente in questa introduzione ci siamo soffermati soltanto su alcune novit e nel corso della presente guida verranno esaminate pi in dettaglio le caratteristiche salienti di SQL Server 2008.

7

4. InstallazioneVediamo adesso di analizzare a grandi linee i passaggi necessari per portare a termine linstallazione di SQL Server 2008. Per prima cosa occorre assicurarsi di essersi autenticati sulla macchina su cui di sta operando con unutenza che gode dei privilegi di amministratore, poich durante linstallazione verranno creati diversi file e cartelle. Allinizio dellinstallazione viene richiesta linstallazione del .NET Framework 3.5 (se non precedentemente installato sulla macchina) che SQL Server 2008 utilizza per vari scopi e che permette ai programmatori di scrivere codice in uno qualunque dei linguaggio .NET e utilizzare tale codice per interagire con SQL Server. La prima finestra del wizard di installazione la SQL Server Installation Center

Questa schermata consente di gestire diversi aspetti di SQL Server ma per gli scopi della presente lezione ci soffermeremo sul processo di installazione. Clicchiamo dunque sul link Installation a sinistra e selezioniamo nella finestra successiva lopzione New SQL Server stand-alone installation

Viene effettuata una breve scansione del sistema prima della richiesta di inserire il codice prodotto e di accettare la licenza duso.

8

A questo punto viene proposta la finestra Feature Selection in cui necessario selezionare i componenti da installare

Si pu scegliere di installare tutti gli strumenti disponibili oppure di selezionare esclusivamente quelli che si conosce in anticipo che verranno utilizzati e proseguire nel processo di installazione (sar comunque possibile installare successivamente gli altri). Una cosa importante da sottolineare che possibile installare pi volte SQL Server su una macchina e ci molto utile quando si dispone di un server abbastanza potente che dispone di risorse sufficienti a supportare due o tre diverse applicazioni in esecuzione che richiedono di avere ognuna i propri database SQL. Ogni installazione denominata istanza e ciascuna di esse deve avere un nome univoco. Spesso ad esempio nelle grandi aziende si decide di creare unistanza di SQL Server per lo sviluppo delle applicazioni e unaltra per il testing. Nel nostro caso non abbiamo la necessit di installare pi istanze e quindi selezioniamo lopzione Default instance nella finestra Instance Configuration

Proseguendo nellinstallazione giungiamo alla sezione Database Engine Configuration in cui dobbiamo scegliere la modalit di autenticazione (Authentication Mode)

9

E possibile scegliere tra due opzioni di autenticazione: Windows authentication mode e Mixed mode. La prima determina lutilizzo dellautenticazione di Windows per la gestione dei login a SQL Server (ed quella che utilizzeremo nel presente esempio); la seconda permette di utilizzare sia lautenticazione di Windows sia quella di SQL Server. Sempre nella stessa schermata necessario definire un account di amministrazione, da utilizzare in caso di emergenze. Potete notare come nella finestra Database Engine Configuration siano presenti tre tab in alto. Il primo quello relativo alle impostazioni di autenticazione (Account Provisioning) che abbiamo appena visto. Clicchiamo sul tab Data Directories tramite cui possibile impostare i percorsi in cui SQL Server memorizza tutti i sui dati (possiamo lasciare i percorsi proposti)

Lasciamo le impostazioni di default in questa e nelle schermate successive e proseguiamo fino alla fine della procedura di installazione. A questo punto ci verr presentata una finestra di riepilogo delle scelte fatte e non ci resta che cliccare su Install.

5. SQL Server Management StudioDopo aver installato correttamente SQL Server 2008 il momento di iniziare ad esplorare le aree che rendono questo prodotto facile da utilizzare e molto efficace. Una componente findamentale di SQL Server SQL Server Management Studio (SSMS), uninterfaccia utente da utilizzare per sviluppare ed effettuare la manutenzione dei nostri database. Si tratta di uno strumento intuitivo e facile da utilizzare che permette di lavorare in modo veloce e produttivo.

10

SQL Server consiste in un processo di Windows separato, pertanto se aprite il Task Manager e date un occhiata ai processi attivi troverete, tra gli altri, sqlservr.exe. Tale processo viene eseguito come servizio, monitorato da Windows stesso, e ad esso vengono riservate le opportune quantit di memoria e capacit di elaborazione del processore. Chiaramente in base al carico sul server SQL Server modifica le sue richieste in funzione delle risorse disponibili. Siccome SQL Server viene eseguito come un servizio esso non ha interfacce collegate per linterazione con gli utenti e per tale motivo necessario uno strumento separato che permetta di comunicare comandi e funzioni da un utente al servizio di SQL Server, il quale poi eseguir le richieste sul sottostante database. Tale strumento, come avrete intuito, proprio SSMS e per tale motivo andiamo ad analizzarlo pi in dettaglio. Per avviare SSMS dal men Start selezionate la cartella di installazione di SQL Server 2008 e cliccate su SQL Server Management Studio. Viene proposta la finestra Connect to Server (e se non viene proposta automaticamente cliccate dal men File sullopzione Connect Object Explorer)

In tale finestra occorre specificare: il tipo di server (lasciate il valore Database Engine); il nome del server (espandete il men a tendina e cliccando sullopzione Browse for more dovrebbe comparire il nome della macchina su cui avete installato SQL Server, selezionate questa oppure uno degli altri server eventualmente disponibili); il tipo di autenticazione, che dipende dallopzione scelta in fase di installazione (se si scelto di installare con lopzione Windows Authentication questa sar lunica opzione disponibile, altrimenti sar possibile selezionare anche il tipo SQL Server Authentication inserendo le opportune informazioni di login). Cliccando sul tasto Options inoltre possibile andare ad impostare altre propriet della connessione, tra cui anche quella per scegliere se criptare la connessione (Encrypt connection)

11

Dopo avere fatto le opportune scelte clicchiamo su Connect ed entriamo in SSMS. Chi ha familiarit con Visual Studio .NET trover un layout grafico molto simile ad esso

12

Nel mio caso io mi sono connesso allistanza di SQL Server che gira sul mio computer locale, chiaramente ognuno di voi si connetter alla propria. Nella finestra di sinistra in basso potete vedere due opzioni: Object Explorer e Registered Servers. Cliccate sulla seconda e andiamo ad analizzarne le funzionalit. Larea Registered Servers mostra tutti i server SQL Server che vengono registrati nel SSMS. Allinizio chiaramente vedrete solo il server che avete appena registrato. Per registrare un nuovo server basta cliccare con il tasto destro del mouse sul nodo Local Server Groups e scegliere lopzione New Server Registration

La finestra di registrazione di un nuovo server molto simile a quella di connessione che abbiamo visto allinizio, basta inserire i dati correttamente e cliccare su Save. Lunica differenza la presenza della sezione Registered Server, tramite cui possibile dare un nome diverso ai server registrati. Passiamo ora alla sezione Object Explorer

13

Come potete vedere sono presenti diversi nodi:

Databases Contiene i database sia di sistema che di utente ai quale si connessi Security Contiene la lista dei login degli utenti che possono connettersi a SQL Server Server Objects Contiene oggetti come periferiche di backup e fornisce la lista dei linked server (server remoti connessi al nostro) Replication Mostra i dettagli relativi alla riproduzione dei dati dal database sul server ad un altro database sullo stesso server o su un altro Management Permette di gestire i piani di manutenzione (maintenance plans), di gestire le policy, di gestire i log e i messaggi di errore SQL Server Agent Permette di pianificare ed eseguire attivit specifiche in determinati momenti, riportando i dettagli sia in caso di successo che di fallimento delle stesse

Chiaramente in questa lezione diamo solo un accenno a queste aree e molte di esse verranno approfondite nel corso della guida. Osservando la barra dei men di SQL Server 2008 un men interessante da approfondire il View, che determina la visualizzazione di diverse aree di SSMS

In tale men la prima e la terza voce sono relative alle due aree che abbiamo visto prima, mentre cliccando sulla seconda viene proposta una pagina di riepilogo relativa al nodo selezionato nella finestra Object Explorer. La maggior parte delle altre opzioni determinano la visualizzazione di finestra specifiche:

Template Explorer - Contenente esempi di codice T-SQL Property Windows Mostra le propriet di un oggetto Error List Mostra gli errori riscontrati nel codice presente nelleditor Toolbars Permette di impostare diverse barre degli strumenti

Vedremo in dettaglio il funzionamento di queste e di altre aree nel prosieguo della guida.

14

6. Elementi di un database e database di sistemaLa progettazione di un database non semplice e richiede molta attenzione. In questa lezione cercheremo di analizzare le procedure relative a tale area che hanno come obiettivi soprattutto quello di rendere pi efficiente la ricerca delle informazioni e quello di ridurre le duplicazioni delle stesse. Un database un contenitore di oggetti che memorizzano dati e permettono di effettuare ricerche su di essi in modo sicuro. Un database di SQL Server 2008 tipicamente contiene i seguenti oggetti:

Tabelle Colonne e righe allinterno delle tabelle Procedure (stored procedure) Indici Viste Funzioni

Vediamoli nel dettaglio: Tabelle Le tabelle sono gli oggetti che contengono i dati di un database. Le tabelle definite dagli utenti sono dette user tables. Esistono anche le tabelle di sistema (system tables), che contengono molte utili informazioni, e le tabelle tempranee (temporary tables). Colonne e righe Le colonne provvedono alla definizione di ogni singolo elemento di informazione allinterno della definizione di una tabella. Esse sono simili alle colonne di un foglio Excel ma a differenza di questultime, in cui ciascuna cella pu contenere un tipo differente di dati, una colonna allinterno di una tabella di SQL Server limitata da un solo tipo di dati e dalla quantit di informazioni che essa pu contenere. Una riga (o record) semplicemente costituita da una cella per ogni colonna presente nella tabella e rappresenta una singola unit di informazione. Procedure Quando necessario manipolare grandi quantit di dati o eseguire ripetutamente sugli stessi alcune operazioni conveniente memorizzare il codice di tali attivit nelle cosiddette stored procedure (SP). Una SP contiene una o pi istruzioni T-SQL, che vengono compilate e rese pronte da utilizzare quando richiesto. Tali procedure sono permanentemente memorizzate nel database e pronte per luso in qualsiasi momento. Indici Gli indici possono essere visti come liste predefinite di informazioni che servono al database per sapere come i dati sono fisicamente memorizzati e ordinati, al fine di rendere le ricerche pi veloci ed efficienti. Un indice consiste di una o pi colonne della tabella per la quale viene definito.

15

Viste Le viste sono una sorta di tabelle virtuali che possono contenere informazioni derivanti da diverse tabelle (opportunamente legate tra di loro). Funzioni Una funzione simile ad una stored procedure ma essa opera su una riga alla volta dellinsieme di righe che si trova ad elaborare. Solitamente si utilizza una stored procedure per attivit che devono restituire un output e una funzione se si desidera conoscere i dettagli di ciascuna transazione di una determinata attivit. Durante linstallazione di SQL Server vengono installati anche alcuni database predefiniti, quelli di sistema (master, tempdb, model e msdb) e due database di esempio che contengono dati relativi ad una compagnia che produce biciclette (AdventureWorks e AdventureWorksDW). Il database master il cuore di SQL Server e nel caso in cui si danneggiasse probabilmente SQL Server non funzionerebbe pi correttamente. Esso contiene diverse informazioni fondamentali: tutti i login ed i ruoli relativi agli utenti; tutte le impostazioni di sistema (come ad esempio il linguaggio di default); i nomi e le informazioni relative a tutti i database allinterno del server; la posizione dei database; le modalit di utilizzo della cache; i linguaggi di disponibili; i messaggi derrore di sistema; ecc. Il database tempdb un database temporaneo la cui durata corrisponde alla durata di una sessione di SQL Server. Esso viene creato allavvio di SQL Server e viene eliminato quando esso viene terminato. Un utilizzo molto comune di tale database quello di memorizzare in esso i dati derivanti da uninterrogazione particolare per riutilizzarli in un secondo momento (tramite le tabelle temporanee). Solitamente quando si crea un nuovo database si desidera che esso abbia un insieme di impostazioni predefinite e tali informazioni possono essere inserite e gestite tramite il database model, che rappresenta una sorta di modello per gli altri database. Il database msdb un altro elemento fondamentale di SQL Server e fornisce le informazioni necessarie allesecuzione automatica di attivit (job) definite tramite il SQL Server Agent. Questultimo un servizio di Windows che esegue tutti i job schedulati. Altri processi molto importanti che utilizzano in database msdb sono quelli di backup e restore.

7. Linee guida per la progettazione di un databaseQuando ci si trova a dover progettare un database necessario prendere alcune decisioni fondamentali come:

Definire la tipologia del database: transazionale o analitico Definire il tipo di informazioni che esso dovr gestire Definire i componenti del database (tabelle, relazioni, chiavi, ecc.)

Il primo passo quello di stabilire se il nostro sistema sar di tipo OLTP (Online Transaction Processing) o di tipo OLAP (Online Analytical Processing). Vediamo di capire la differenza tra questi due tipi di sistema.

16

OLTP Un sistema OLTP permette di effettuare aggiornamenti dei dati istantaneamente e tipicamente si interfaccia con applicazioni utente che operano direttamente sui dati, permettendo agli utenti di rendere definitive tutte le modifiche che essi apportano. I sistemi OLTP richiedono diversi accorgimenti per assicurare prestazioni, affidabilit ed integrit dei dati e per tale motivo non importante soltanto la progettazione del database, ma anche lindividuazione della locazione fisica dei dati. Molti di questi sistemi sono utilizzati continuativamente e quindi la grande frequenza di modifiche ai dati richiede unattenta pianificazione ed esecuzione dellattivit di backup. Inoltre poich la velocit delle operazioni un obiettivo fondamentale in tali sistemi, tipicamente in essi presente un numero di indici molto pi grande che nei sistemi OLAP. OLAP A differenza dei sistemi OLTP un sistema OLAP progettato in base al requisito che i dati presenti in esso vengano aggiornati non molto frequentemente. Tali aggiornamenti possono essere pianificati per essere eseguiti quando stabilito dallamministratore del sistema: di notte, ogni settimana, ogni mese e cos via. Come suggerisce il termine analytical, questi sistemi sono utilizzati per effettuare analisi sui dati esistenti e non per aggiornare i dati stessi, quindi non necessario effettuare frequenti operazioni di backup. In essi inoltre sono presenti molti meno indici rispetto ai sistemi OLTP. I sistemi OLAP sono anche conosciuti con il nome di data warehouse (letteralmente deposito di dati), anche se questultimi sono solo una parte di un sistema OLAP. Una delle prime cose da fare quando si progetta un database stabilire che tipo di informazioni verranno gestite dallo stesso. Individuate tali informazioni si passa alla definizione delle tabelle relative (vedremo prossimamente come effettuare tale operazione). Nella prima fase di progettazione di un database le tabelle sono essenzialmente entit separate non correlate tra loro. Tipicamente esse hanno alcuni nomi di colonne corrispondenti (si pensi ad una tabella AnagraficaDipendenti con un IdDipendente e una tabella PresenzeDipendenti che contiene ovviamente anche la colonna IdDipendente) ma non c niente che le colleghi tra loro. Per legare tra loro tali tabelle necessario definire le cosiddette chiavi e relazioni, fondamentali per evitare che modifiche ad una determinata tabella causino la non validit dei dati contenuti in unaltra tabella e quindi danneggino la cosiddetta integrit dei dati. Una chiave un modo per identificare univocamente una riga in una tabella di un database e le chiavi sono molto utili per la creazione delle relazioni tra tabelle. La chiave pu essere definita su una sola colonna o su pi colonne allo stesso tempo e pu essere di tre tipi: primaria, esterna (o di integrit referenziale) e alternativa. La chiave primaria quella pi importante. Le colonne che formano la chiave primaria devono contenere valori univoci, ovvero ciascun valore della chiave primaria deve restituire un solo record della tabella. Chiaramente tale chiave non pu essere definita su colonne che ammettono valori NULL e pu essere utilizzata per collegare i dati di tabelle diverse (tramite operazioni di JOIN). La chiave esterna viene invece tipicamente definita tra un insieme di colonne di una tabella figlia e la chiave primaria di una tabella padre (master table), in modo che ad ogni riga della tabella padre corrispondano zero o pi righe della tabella figlia. Per esempio tornando alle tabelle citate in precedenza, AnagraficaDipendenti e PresenzeDipendenti, possibile creare una relazione tra di esse tramite la colonna IdDipendente, che la chiave primaria della tabella di anagrafica.

17

Come detto in precedenza non possibile definire su una tabella pi di una chiave primaria, tuttavia possibile definire un altro tipo di chiave molto simile alla primaria, la chiave alternativa. In pratica non ci sono differenze logiche tra i due tipi di chiave e la chiave alternativa deve contenere valori univoci. Passiamo adesso ad esaminare i diversi tipi di relazioni. Come accennato in precedenza, una relazione un legame logico tra due tabelle. Quando definiamo una relazione logica informiamo SQL Server che stiamo legando la chiave primaria di una tabella alla chiave esterna di unaltra, sono quindi necessarie due chiavi. Una relazione pu essere utilizzata per assicurare lintegrit dei dati. Se ad esempio due tabelle sono legate da una relazione non logicamente corretto eliminare una riga della tabella padre senza eliminare le corrispondenti righe della tabella figlia. In caso contrario si verrebbe a creare la situazione in cui la tabella figlia contiene righe orfane, che non corrispondono ad alcuna riga della tabella padre. Ci vale anche per linserimento di nuove righe: non deve essere possibile inserire righe in una tabella figlia senza aver prima inserito la corrispondente riga nella tabella padre. Le relazioni possono essere di tre tipi:

Uno a uno Uno a molti Molti a molti

Relazione uno a uno Questo tipo di relazione non molto comune allinterno di un database perch tipicamente non esiste alcun motivo per cui un record di una tabella debba corrispondere ad un solo record di unaltra tabella (si potrebbe creare ununica tabella contenente le colonne delle due tabelle di origine). Lunico scenario in cui questo tipo di relazione trova giustificazione quello in cui si desidera suddividere una tabella di grandi dimensioni in tabelle pi piccole (soprattutto per motivi prestazionali) e quindi ha senso definire un vincolo di tipo uno a uno tra tutte le tabelle derivate. Relazione uno a molti E il tipo di relazione pi diffuso. In una relazione di questo tipo una riga di una tabella padre logicamente collegata a zero, una o pi righe di una tabella figlia. Ad esempio un record della tabella AnagraficaDipendenti pu essere logicamente legato a n record della tabella presenze dipendenti, ognuno dei quali riporta i dati delle presenze del dipendente per ogni giorno di lavoro. Relazione molti a molti In questo tipo di relazione zero, una o pi righe di una tabella padre sono collegate a zero o pi righe di una tabella figlia. Un altro concetto molto importante nella progettazione di un database quello di normalizzazione. Con questo termine viene indicata la pratica di ridurre la ridondanza dei dati allinterno delle tabelle. Quest operazione viene spesso effettuata suddividendo i dati su pi tabelle in modo gerarchico e favorisce prestazioni migliori delle query, rendendo pi efficiente il database.

18

8. SicurezzaLa sicurezza di un database un elemento fondamentale quanto la progettazione, la creazione e le prestazioni dello stesso. Se nel nostro database non vengono assicurate le opportune misure di sicurezza chiunque pu accedere ai dati e modificarli a proprio piacimento a danno del proprietario degli stessi. La sicurezza in SQL Server pu essere assicurata in diversi modi:

Attraverso lautenticazione di Windows Restringendo laccesso degli utenti ai dati tramite viste Creando utenze, login e ruoli che abbiano espliciti livelli di accesso Applicando tecniche di crittografia a database, log e file

Nelle lezioni precedenti abbiamo gi visto la differenza tra autenticazione di Windows e autenticazione di SQL Server quindi nella presente lezione andremo oltre soffermandoci su: Login, Ruoli e Schemi. Login Lunico modo di connettersi a SQL Server tramite credenziali di login. Quando un database viene creato inizialmente soltanto il proprietario dello stesso possiede i diritti per effettuare qualsiasi operazione su di esso. E una pratica comune quella di creare un gruppo di Windows e porre gli account di Windows in tale gruppo per poi aggiungerlo a SQL Server. Per creare una nuova login basta cliccare con il tasto destro del mouse sul nodo Logins nella finestra Object Explorer e scegliere lopzione New Login

Si aprir la seguente finestra

19

in cui occorre inserire il nome utente (login name), scegliere il tipo di autenticazione e, se lo si desidera, selezionare il database ed il linguaggio di default. Cliccando sul tasto Search in alto a destra si aprir una finestra che permette di cercare utenze o gruppi di Windows e quindi impostare le credenziali di login per essi. A questo punto occorre selezionare i database ai quali lutente che si sta creando potr accedere e per fare questo clicchiamo sulla sinistra della finestra precedente sulla voce Users Mapping, si aprir la seguente pagina

20

in cui come potete notare ho selezionato AdventureWorks e AdventureWorksDW. Ruoli

per

lutente

UTENZA1

i

database

A questo punto abbiamo creato unutenza in grado di collegarsi con il server su cui risiedono i database. Il prossimo passo quello di creare lutenza per il database e per fare ci dobbiamo prima parlare dei ruoli. Allinterno di SQL Server esistono tre differenti tipi di ruoli: ruoli a livello server, ruoli a livello database e ruoli a livello applicazione. I ruoli a livello server sono predefiniti e consentono di autorizzare certe operazioni e negarne altre. Un amministratore di sistema pu assegnare tali ruoli a ciascun utente del server e per vedere quali sono basta espandere il nodo Server Roles, che si trova sotto il nodo Logins

21

Ciascuno di questi ruoli permette di effettuare determinate operazioni e il ruolo sysadmin quello che permette di effettuare qualsiasi operazione. Per aggiungere unutenza ad un determinato ruolo basta cliccare con il tasto destro del mouse sul ruolo corrispondente e scegliere lopzione Properties. Si aprir una finestra in cui possibile aggiungere le utenze desiderate. Nel mio caso scelgo di assegnare ad UTENTE1 il ruolo sysadmin

I ruoli a livello di database invece riguardano le azioni che possibile effettuare sui database stessi. Se andate a riguardare limmagine della procedura di creazione in cui abbiamo selezionato i database a cui pu accedere UTENTE1 noterete in basso lelenco dei ruoli a livello di database che possibile impostare. Analogamente a sysadmin a livello di server il ruolo che gode di tutti i privilegi db_owner. Generalmente i database vengono creati per interagire con applicazioni perch non tutti i database interagiscono soltanto con unapplicazione. Un ruolo a livello di applicazione non coinvolge utenti ma viene utilizzato quando si desidera stabilire cosa unapplicazione pu fare e cosa non pu fare allinterno di un database. Per creare un nuovo ruolo di questo tipo (ma anche a livello di database) basta espandere nella finestra Object Explorer il nodo Roles che si trova allinterno del nodo Security del database in oggetto e cliccare con il tasto destro su Application Roles, selezionando New Application Role. Si aprir la seguente finestra

22

tramite cui impostare le opzioni desiderate. Schemi Uno schema essenzialmente un modo di creare un gruppo ed inserire al suo interno oggetti che possono essere utilizzati per garantire o revocare permessi. Prima di SQL Server 2005 ogni oggetto di un database apparteneva ad un account utente, con evidenti problemi legati alla gestione di tali account. A partire da SQL Server 2005 gli oggetti appartengono invece a schemi e diversi utenti possono essere assegnati ad uno stesso schema.

9.Creare un databaseAndiamo adesso a creare il nostro semplice database di prova. Per fare questo possibile procedere in due modi: utilizzando linterfaccia grafica di SQL Server Management Studio o scrivendo manualmente codice T-SQL. Dati gli scopi della presente guida vedremo come procedere in modalit grafica, sebbene entrambi siano validi approcci da utilizzare in contesti differenti. Chiaramente per chi non abbia familiarit con le istruzioni T-SQL la scelta dellinterfaccia grafica di SSMS obbligata. Dopo aver avviato SSMS ed esserci collegati al nostro server espandiamo nella finestra Object Explorer il nodo Databases e visualizziamo i database predefiniti, tra cui AdventureWorks

23

Prima di creare un database dobbiamo avere chiare le seguenti informazioni:

Il nome da assegnare al database La locazione del database Il nome dei file utilizzati per memorizzare le informazioni contenute nel database

Clicchiamo con il tasto destro del mouse sul nodo Databases e scegliamo lopzione New Database

Si

aprir

a

questo

punto

la

pagina

General

della

finestra

New

Database

24

Inseriamo il nome del database (nel mio caso DBTEST) e i nomi dei file relativi al database e ignoriamo per il momento lopzione Use full.text indexing. Sotto il nome del database potete vedere il campo owner (proprietario) il quale pu essere valorizzato con qualsiasi login che abbia lautorit per creare un database. Il file di database e viene memorizzato sul disco con estensione .MDF (ad esempio DBTEST.mdf). MDF sta per Master Data File (file di dati principale) ed ogni database deve avere almeno un file di questo tipo. E anche possibile avere un Secondary Data File con estensione .NDF. Questultimo tipo di file utile se si desidera suddividere le tabelle e gli indici di un database su dischi diversi, chiaramente al prezzo di un peggioramento delle prestazioni del database. Per creare un file secondario basta cliccare sul tasto Add nella finestra New Database e nella nuova riga selezionare come valore della colonna Filegroup SECONDARY. Altre tre colonne relative ai file sono Initial Size, Autogrowth e Path. La prima indica le dimensioni iniziali del database (quando cio vuoto); la seconda se SQL Server debba gestire automaticamente e come la crescita delle dimensioni del database; la terza serve ad impostare il percorso del file. Vi consiglio di lasciare in tutti questi campi i valori proposti. A questo punto clicchiamo nella finestra a sinistra sulla pagina Options

25

Il primo campo da valorizzare nella pagina Options Collation. Le collation determinano non solo il set di caratteri in uso (dipendente dalla lingua) ma anche il comportamento nella ricerca e negli ordinamenti dei caratteri maiuscoli e minuscoli (ovvero se "A" deve essere uguale ad "a") ed alle lettere accentate (ad esempio se "" deve essere uguale ad "" e a "e"). Queste propriet determinano se il set di caratteri case sensitive e/o accent sensitive. Proseguendo dobbiamo impostare il Recovery Model per il quale sono disponibili tre opzioni: Full, Bulk-Logged e Simple. Per un approfondimento su questa tematica vi invito a leggere un articolo molto interessante al seguente link. La propriet Compatibility Level permette di creare database compatibili con versioni precedenti di SQL Server, al prezzo di sacrificare qualche funzionalit. Seguono tutta una serie di altre opzioni (Other options) delle quali mi soffermo sulle prime tre (vedremo alcune delle altre pi avanti):

Auto Close Indica se il database deve essere chiuso quando lultimo utente si disconnette Auto Create Statistics Determina se devono essere generate statistiche quando i dati vengono interrogati Auto Shrink Permette di abilitare la gestione automatica delle dimensioni del database e dei transaction log

Finalmente possiamo cliccare su OK. Per prima cosa SQL Server verifica che il nome che abbiamo scelto non appartenga gi ad un altro database (e in questo caso occorre ovviamente cambiarlo). Dopo aver validato il nome viene effettuato un controllo di sicurezza per verificare che lutente che sta creando il database possiede i permessi per farlo. Il passo successivo la creazione dei file dei dati sul disco e se tale operazione va a buon fine il database pronto per luso.

26

Nell Object Explorer troveremo a questo punto anche il nostro database

Se vogliamo dare unocchiata al codice T-SQL che SQL Server ha generato ed eseguito automaticamente basta cliccare con il tasto destro del mouse sul nostro database e selezionare lopzione Script Database as -> CREATE to -> New Query Editor Window

si aprir una finestra che conterr il seguente codice (o qualcosa di molto simile)USE [master] GO /****** Object: Database [DBTEST] Script Date: 04/15/2010 17:58:49 ******/ CREATE DATABASE [DBTEST] ON PRIMARY ( NAME = N'DBTEST', FILENAME = N'C:\Programmi\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DBTEST.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'DBTEST_log', FILENAME = N'C:\Programmi\ Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DBTEST_log.ldf' , SIZE = 5120KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO ALTER DATABASE [DBTEST] SET COMPATIBILITY_LEVEL = 90 GO IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC [DBTEST].[dbo].[sp_fulltext_database] @action = 'disable' end GO ALTER DATABASE [DBTEST] SET ANSI_NULL_DEFAULT OFF GO

27

ALTER DATABASE [DBTEST] SET ANSI_NULLS OFF GO ALTER DATABASE [DBTEST] SET ANSI_PADDING OFF GO ALTER DATABASE [DBTEST] SET ANSI_WARNINGS OFF GO ALTER DATABASE [DBTEST] SET ARITHABORT OFF GO ALTER DATABASE [DBTEST] SET AUTO_CLOSE OFF GO ALTER DATABASE [DBTEST] SET AUTO_CREATE_STATISTICS ON GO ALTER DATABASE [DBTEST] SET AUTO_SHRINK OFF GO ALTER DATABASE [DBTEST] SET AUTO_UPDATE_STATISTICS ON GO ALTER DATABASE [DBTEST] SET CURSOR_CLOSE_ON_COMMIT OFF GO ALTER DATABASE [DBTEST] SET CURSOR_DEFAULT GLOBAL GO ALTER DATABASE [DBTEST] SET CONCAT_NULL_YIELDS_NULL OFF GO ALTER DATABASE [DBTEST] SET NUMERIC_ROUNDABORT OFF GO ALTER DATABASE [DBTEST] SET QUOTED_IDENTIFIER OFF GO ALTER DATABASE [DBTEST] SET RECURSIVE_TRIGGERS OFF GO ALTER DATABASE [DBTEST] SET DISABLE_BROKER GO ALTER DATABASE [DBTEST] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GO ALTER DATABASE [DBTEST] SET DATE_CORRELATION_OPTIMIZATION OFF GO ALTER DATABASE [DBTEST] SET TRUSTWORTHY OFF GO ALTER DATABASE [DBTEST] SET ALLOW_SNAPSHOT_ISOLATION OFF GO ALTER DATABASE [DBTEST] SET PARAMETERIZATION SIMPLE GO ALTER DATABASE [DBTEST] SET READ_COMMITTED_SNAPSHOT OFF GO ALTER DATABASE [DBTEST] SET READ_WRITE GO ALTER DATABASE [DBTEST] SET RECOVERY SIMPLE GO ALTER DATABASE [DBTEST] SET MULTI_USER GO ALTER DATABASE [DBTEST] SET PAGE_VERIFY CHECKSUM GO ALTER DATABASE [DBTEST] SET DB_CHAINING OFF GO

Questo codice continene una seriedi queries corrispondenti alle opzioni che abbiamo impostato tramite linterfaccia grafica di SSMS.

28

10. Componenti SQL e tipi di datiCome gi detto il linguaggio T-SQL un linguaggio proprietario della Microsoft, tuttavia esso si basa sugli elementi fondamentali del linguaggio SQL e li integra con funzionalit avanzate. Tali elementi sono comuni a molti linguaggi di programmazione e sono:

Costanti Commenti Identificatori Parole riservate

Costanti Una costante pu essere alfanumerica, esadecimale o numerica. Una stringa costante deve essere delimitata da due apici (ad es. Prova) e se allinterno della stringa si vuole includere un apice occorre digitarne due di seguito (ad es. Questultima). Le costanti esadecimali devono invece cominciare con i caratteri 0x. Una costante ha sempre un tipo dati e una lunghezza. Commenti Esistono due modi per specificare un commento in T-SQL. E possibile racchiudere un commento allinterno di una sequenza del tipo /* */ (ad es. /*Questo un commento*/) ed in tal caso il commento si estender a tutte le linee incluse tra i due delimitatori. Oppure possibile utilizzare i doppi trattini su una singola linea (ad es. --Questo un commento). Questultima soluzione deriva dagli standard del linguaggio SQL, mentre la prima unestensione di T-SQL. Identificatori Gli identificatori vengono invece utilizzati appunto per identificare gli oggetti di un database come tabelle, le stored procedures e gli indici. Essi sono costituiti da una stringa di massimo 128 caratteri. Parole riservate Ogni linguaggio di programmazione caratterizzato da alcune parole riservate che ovviamente non possono essere utilizzate come identificatori e T-SQL non fa eccezione (ad es. SELECT, CREATE, ecc.). Passiamo adesso ai tipi di dati. T-SQL utilizza differenti tipi di dati:

Numerici Caratteri Temporali Misti Altri

Vediamoli nel dettaglio. Numerici Servono ovviamente a rappresentare numeri e di seguito sono elencate le caratteristiche di quelli principali:

decimal[(p[, s])] e numeric[(p[, s])] - Decimal e Numeric sono sinonimi, possono avere valori compresi tra 10^38 - 1 e - 10^38 -1. La memoria che occupano per

29

essere immagazzinati varia a seconda della precisione che utilizziamo per rappresentarli, da un minimo di 2 bytes a un massimo di 17 bytes; "p" la precisione, che rappresenta il numero massimo di cifre decimali che possono essere memorizzate (da entrambe le parti della virgola). Il massimo della precisione 28 cifre; "s" la scala, che rappresenta il numero di massimo di cifre decimali dopo la virgola e deve essere minore od uguale alla precisione. int - Occupa 4 byte di memoria e memorizza i valori da -2147483648 a 2147483647 smallint - Occupa 2 byte di memoria e memorizza i valori da -32768 a 32,767 tinyint - Occupa 1 byte di memoria e memorizza i valori da 0 a 255 float[(n)] - Contiene numeri a virgola mobile positivi e negativi, compresi tra 2.23E-308 e 1.79E308 per i valori positivi e tra -2.23E-308 e -1.79E308 per i valori negativi, occupa 8 bytes di memoria ed ha una precisione di 15 cifre real - Contiene numeri a virgola mobile positivi e negativi comprese tra 1.18E-38 e 3.40E38 per i valori positivi e tra -1.18E-38 e -3.40E38 per i valori negativi, occupa 4 bytes di memoria ed ha una precisione di 7 cifre

Caratteri Esistono due tipi di dati a livello di caratteri. Essi possono essere infatti stringhe di singoli byte di caratteri o stringhe di caratteri Unicode:

char[(n)] - Ha una lunghezza fissa e pu contenere fino ad 8000 caratteri ANSI (cio 8000 bytes) varchar[(n)] - Ha una lunghezza variabile e pu contenere fino ad 8000 caratteri ANSI (cio 8000 bytes) nchar[(n)] - Ha una lunghezza fissa e pu contenere fino a 4000 caratteri UNICODE (cio 8000 bytes, ricordiamo che per i caratteri UNICODE servono 2 bytes per memorizzare un carattere) nvarchar[(n)] - ha una lunghezza variabile e pu contenere fino a 4000 caratteri UNICODE (cio 8000 bytes, ricordiamo che per i caratteri UNICODE servono 2 bytes per memorizzare un carattere)

Temporali Alcuni tipi di dati temporali sono:

datetime - Ammette valori compresi dal 1 gennaio 1753 al 31 dicembre 9999 (precisione al trecentesimo di secondo), occupa uno spazio di 8 byte smalldatetime - Meno preciso del precedente (precisione al minuto), occupa uno spazio di 4 byte date Occupa uno spazio di 3 byte per memorizzare solo date time Occupa uno spazio da 3 a 5 byte per memorizzare solo orari

Misti

bit - Tipicamente usato per rappresentare i flag, vero/false o true/false o si/no, perch pu accettare solo due valori 0 o 1. Occupa un bit ovviamente. Le colonne che hanno un tipo dati bit non possono avere valori nulli e non possono avere indici. timestamp - Occupa 8 bytes ed un contatore incrementale per colonna assegnato automaticamente da SQL Server 7.

Altri

money Contiene valori monetari da -922337203685477.5808 a 922337203685477.5807 con una precisione al decimillesimo di unit monetaria, occupa 8 bytes di memoria

30

smallmoney - Contiene valori monetari da - 214748.3648 a 214748.3647 con una precisione al decimillesimo di unit monetaria, occupa 4 bytes di memoria. binary[(n)] - Ha una lunghezza fissa e pu contenere fino ad 8000 bytes di dati binari varbinary[(n)] - Ha una lunghezza variabile e pu contenere fino ad 8000 bytes di dati binari

Esistono poi i cosiddetti Large Objects Data Types (LOBs) che possono raggiungere una dimensione massima di 2GB. Questi oggetti vengono tipicamente utilizzati per la memorizzazione di grandi quantit di testo o di file multimediali e sono: varchar(MAX), nvarchar(MAX), varbinary(MAX), TEXT, NTEXT, IMAGE. Esistono anche altri tipi di dati e per approfondire la conoscenza delle caratteristiche di questi e di quelli che vi ho elencato vi consiglio di consultare la documentazione ufficiale T-SQL. Valori NULL NULL un particolare valore che pu essere assegnato ad una colonna. Questo valore tipicamente utilizzato quando linformazione relativa ad una cella di una colonna sconosciuta o non applicabile. Ogni espressione aritmetica o booleana che coinvolge valori NULL restituisce un valore NULL. Nelle funzioni di aggregazione i valori NULL vengono eliminati.

11. Funzioni predefinite, operatori e variabili globaliIl linguaggio T-SQL contiene diverse funzioni predefinite che possono essere suddivise in due categorie: aggregate e scalari. Andiamo a vedere quelle pi importanti. Funzioni aggregate Le funzioni aggregate (o funzioni di aggregazione) vengono applicate ad un insieme di dati appartenenti ad una colonna e restituiscono sempre un singolo valore. Quelle principali sono:

AVG - Calcola la media aritmetica dei valori di una colonna (che chiaramente deve contenere valori numerici) MAX Restituisce il valore massimo dei dati di una colonna (che pu avere come tipi di dati numeri, stringhe e valori temporali) MIN Restituisce il valore minimo dei dati di una colonna (che pu avere come tipi di dati numeri, stringhe e valori temporali) SUM Effettua la somma dei valori dei valori di una colonna (che chiaramente deve contenere valori numerici) COUNT Calcola il numero di valori non nulli in una colonna. Se questa funzione viene utilizzata nella forma COUNT(*) restituisce il numero delle righe di una tabella

Funzioni Scalari Le funzioni scalari operano su un valore o su una lista di valori e possono essere suddivise in:

Funzioni numeriche

31

Funzioni temporali Funzioni di gestione delle stringhe Funzioni di sistema

Le principali sono:

Abs(espressione_numerica) - Valore assoluto Acos( espressione_numerica) - Arcoseno Ascii(character_espressione) - Converte un carattere nel suo codice ASCII Asin( espressione_numerica) - Arcoseno Atan( espressione_numerica) Arcotangente Cast(espressione as tipo_di_dato) Converte una espressione SQL in un determinato tipo di dato

Char(espressione_intera) - Converte in ASCII il carattere corrispondente Convert(data_type [(length)], espressione [, style]) - Converte dati da un tipo ad un altro Cos(espressione_numerica) Coseno Cot(espressione_numerica) Cotangente Dateadd(datepart, number, date) - Aggiunge un numero alla parte della data (esempio 3 mesi) Datediff(datepart, startdate, enddate) - Calcola la differenza tra due date (in giorni, mesi o anni in base al parametro datepart) Datename(datepart, date) - Il nome della parte della data Datepart(datepart, date) - Il valore della parte della data Day(date) - Il giorno della data passata Exp(espressione_numerica) Esponente Getdate( ) - Restituisce la data corrente Isdate(espressione) - Valuta se l'espressione di tipo DATETIME Isnull(espressione, valore_di_sostituzione) - Se l'espressione null la sostituisce con il secondo valore Isnumeric(espressione) - Valuta se l'espressione di tipo NUMERIC Left(espressione, numero) - La parte di stringa a partire dal numero Len(string_espressione) - La lunghezza della stringa Log(espressione_numerica) - Logaritmo naturale Log10(espressione_numerica) - Logaritmo in base 10 Lower(character_espressione) - Converte in minuscolo Ltrim(character_espressione) - Toglie gli spazi a sinistra Month(date) - Il mese della data inserita Nullif(espressione, espressione) - Restituisce null se espressioni sono equivalenti

32

Per operare su valori scalari sono necessari operatori scalari e il linguaggio T-SQL supporta diversi operatori di tipo numerico e booleano. Sono presenti operatori aritmetici di tipo unario e binario. Gli operatori unari sono + e -. Quelli binari sono +, -, *, / e %. Gli operatori booleani hanno due differenti notazioni a seconda che vangano applicati a stringhe o ad altri tipi di dati. Gli operatori AND, OR e NOT vengono applicati a tutti i tipi di dati (eccetto BIT). Infine soffermiamoci sulle cosiddette variabili globali che possono essere utilizzate come se fossero costanti. Questo tipo di variabili sono precedute da una doppia chiocciola (@@) e alcune di queste sono:

@@CONNECTIONS Restituisce il numero di tentativi di login effettuati da quando il sistema stato avviato; @@ERROR Restituisce informazioni sul valore ritornato dallultima istruzione eseguita; @@LANGID Restituisce lidentificatore del linguaggio utilizzato correntemente nel database; @@ROWCOUNT Restituisce in numero di righe coinvolte dallultima istruzione; @@VERSION Restituisce la versione corrente del DBMS; @@SERVERNAME Restituisce informazioni riguardanti il server;

12. Definire e creare tabelleA questo punto abbiamo creato il nostro database e conosciamo gli elementi di base del linguaggio T-SQL ma affinch il database sia funzionale necessario che esso contenga almeno una tabella. Tipicamente i database di grandi e medie organizzazioni contengono centinaia di tabelle. La progettazione di una tabella unattivit cruciale poich presuppone la definizione del giusto numero di colonne, del relativo tipo di dati e delle eventuali relazioni con altre tabelle. Una tabella un contenitore di dati raggruppati in una o pi colonne e pu avere zero o pi righe. La definizione delle colonne una fase molto importante poich ciascuna di esse conterr un solo tipo di dati e quindi si deve essere assolutamente certi del tipo di dati da assegnare loro. Chiaramente opportuno dare sia alle colonne che alla tabella un nome appropriato ai dati contenuti. Andiamo adesso a creare la nostra prima tabella. Supponiamo di voler creare una tabella contenente i dati anagrafici dei dipendenti di unazienda. Avviamo SQL Server Management Studio e colleghiamoci al nostro database (DBTEST). Clicchiamo con il tasto destro del mouse sul nodo Tables e scegliamo lopzione New Table

33

Si aprir la finestra Table Designer

In questa schermata occorre definire tutti i dettagli delle colonne che conterr la tabella. Per la nostra tabella di anagrafica dei dipendenti inseriamo le seguenti colonne

34

Come potete notare selezionando ciascuna colonna nella tab Column Properties compaiono tutte le propriet della stessa. Quando si inseriscono i nomi delle colonne buona norma evitare spazi, eventualmente utilizzate gli underscore (_). Tra le propriet di una colonna c Allow Nulls. Questa scelta determina se la colonna potr contenere o meno valori NULL. Le colonne che verranno usate come chiavi o nelle relazioni (come vedremo successivamente) dovranno avere tale propriet settata a false. Adesso impostiamo la propriet Is Identity a true per la colonna IdDipendente

Questa opzione indica a SQL Server di generare automaticamente un progressivo numerico per ogni nuova riga che verr inserita nella tabella.

35

Clicchiamo sul pulsante di salvataggio e si aprir una finestrella in cui inserire il nome della tabella

clicchiamo su ok ed espandendo nuovamente il nodo Tables del nostro database troveremo la tabella appena creata

Chiaramente sempre possibile cliccare con il tasto destro del mouse su ogni colonna e selezionare lopzione Properties, utilizzando la relativa finestra per cambiare le propriet che si desidera. Clicchiamo invece con il tasto destro del mouse proprio sulla tabella e apriamo la finestra delle propriet (Table Properties)

36

in essa possibile consultare importanti informazioni. Oltre allopzione Properties il men contestuale che viene proposto quando si clicca sulla tabella contiene molte utili opzioni

37

Tra queste, a parte quelle il cui funzionamento abbastanza ovvio, mi preme evidenziarne alcune:

Design Permette di modificare la tabella accedendo alla finestra Table Designer che abbiamo visto nella fase di creazione View Dependencies Fornisce una lista di ogni oggetto del database correlato alla tabella Policies Permette di consultare o definire regole per la tabella

Per gli scopi della presente guida creiamo una seconda tabella denominata PresenzeDipendenti con i seguenti campi: IdDipendente (int), DataPresenza(datetime), Ore(int).

13. Chiavi e relazioniDopo la creazione di una tabella il passo successivo quello di definire la sua chiave primaria, operazione molto semplice in SQL Server Management Studio. Selezioniamo la tabella (nel nostro caso AnagraficaDipendenti) e clicchiamo con il tasto destro del mouse su di essa, scegliendo dal men relativo lopzione Design

In questo modo potremo accedere alla finestra di progettazione della tabella, nella quale clicchiamo con in tasto destro del mouse sulla colonna IdDipendente e scegliamo lopzione Set Primary Key

38

A questo punto sulla sinistra della colonna potete notare il simbolo della chiave

Clicchiamo infine sul pulsante di salvataggio nella barra degli strumenti. Per modificare la chiave primaria possiamo cliccare sul tasto Manage Indexes/Keys nella barra degli strumenti,

accedendo alla finestra Indexes/Keys

39

Procedendo allo stesso modo impostiamo la chiave PresenzeDipendenti sulle due colonne IdDipendente-Data

primaria

della

tabella

Passiamo adesso a definire una relazione tra le due tabelle. La relazione tra le tabelle AnagraficaDipendenti e PresenzeDipendenti di tipo uno a molti perch ad ogni dipendente possono corrispondere n record della tabella PresenzeDipendenti. Clicchiamo con il tasto destro del mouse sulla tabella PresenzeDipendenti, scegliamo lopzione Design e nella barra degli strumenti clicchiamo sul tasto Relationship

Nella relativa finestra clicchiamo su Add per inserire una nuova relazione (possiamo lasciare il nome suggerito da SQL Server)

Espandiamo il nodo Tables And Columns Specification e clicchiamo sul tasto con i tre puntini (ellipsis button)

Si aprir la finestra Tables and Columns, nella quale possiamo modificare il nome della relazione e selezionare la tabella padre della relazione (nel nostro caso AnagraficaDipendenti), mentre la tabella figlia ovviamente non modificabile. Selezioniamo la colonna IdDipendente per entrambe le tabelle e clicchiamo su OK

40

Attenzione non necessario che le colonne coinvolte nella relazione abbiano lo stesso nome, sufficiente che abbiano lo stesso tipo di dati. Tornando alla finestra di definizione delle relazioni lasciamo tutte le altre opzioni di default e chiudiamo la stessa. La chiusura della finestra non determina il salvataggio delle modifiche, occorre sempre cliccare sul tasto di salvataggio nella barra degli strumenti. Prima di effettuare nelloperazione: il salvataggio SQL Server ci mostrer le tabelle coinvolte

41

14. IndiciDopo aver creato le tabelle del nostro database potremmo fermarci qui e cominciare a lavorare sui dati. Tuttavia questa non la scelta migliore soprattutto quando ci si trova a dover lavorare con database reali di grandi dimensioni, con molte tabelle e grandi quantit dati. Quando le tabelle contengono grandi quantit di informazioni la ricerca di un singolo record allinterno di esse potrebbe richiedere molto tempo, con un degrado delle prestazioni di tutto il database. In questo scenario il database pu essere paragonato ad un libro senza indice in cui vogliamo trovare una pagina specifica sfogliando le pagine una ad una. Come ben sappiamo i libri hanno invece sempre un indice e tramite esso la ricerca di una pagina specifica unoperazione semplice e veloce. Una cosa simile buona norma applicarla alle tabelle di SQL Server. La definizione di una indice in SQL Server serve a migliorare le prestazioni. Infatti quando viene effettuata una ricerca su una tabella in cui definito un indice la ricerca stessa non viene effettuata sui dati memorizzati nella tabella ma si focalizza su un sottoinsieme di dati corrispondenti allindice della tabella stessa, rendendo loperazione molto pi veloce. Un indice pu essere creato su una singola colonna (indice semplice) o su pi colonne (indice composto) e pu essere di due tipi: clustered o nonclustered. Indici clustered Un indice clustered definisce lordine fisico dei dati di una tabella. Se abbiamo pi di una colonna definita in un indice clustered i dati vengono ordinati in modo sequenziale in base a tali colonne. Pu essere definito solo un indice clustered per tabella perch ovviamente sarebbe impossibile memorizzare i dati in pi ordini contemporaneamente. Quando vengono inseriti dati SQL Server inserisce i riferimenti agli stessi allinterno dellindice e inserisce la riga nella posizione appropriata. Una buona norma quella di non definire un indice su colonne che vengono aggiornate frequentemente poich in questo caso SQL Server si trover a dover continuamente modificare lordine fisico dei dati con un grosso dispendio di capacit di elaborazione della macchina. Indici nonclustered Diversamente da un indice clustered un indice nonclustered non memorizza direttamente una parte dei dati della tabella ma memorizza puntatori ai dati corrispondenti alle colonne in esso definite. Questo tipo di indici viene memorizzato in una struttura separata rispetto alla tabella. Quando si effettua una ricerca sui dati memorizzati su una tabella sulla quale definito un indice nonclustered SQL Server utilizza linformazione definita nei puntatori dellindice per individuare le righe che corrispondono ai criteri della ricerca. Un indice pu essere unico (unique) o non unico (nonunique). Un indice unico assicura che i valori contenuti nelle colonne definite nellindice siano univoci allinterno della tabella. Un indice non unico invece permette di inserire nella tabella pi righe con gli stessi valori per le colonne definite nello stesso. Per tale motivo gli indici unici sono comunemente utilizzati per supportare costanti come le chiavi primarie mentre quelli non unici vengono comunemente utilizzati per supportare la ricerca di dati tramite colonne che non costituiscono chiavi.

42

Chiaramente la definizione di indici richiede unattenta analisi delle tabelle del database e della quantit stimata di dati che esse potranno contenere. Esistono delle norme dettate dal buon senso che utile conoscere prima di definire un indice: un indice pu contenere fino a 16 colonne ma in genere non dovrebbe contenerne pi di quattro o cinque; la quantit totale di dati per le colonne contenute in un indice di una riga non pu superare i 900 byte; non definire indici su colonne il cui contenuto viene aggiornato frequentemente; ecc. Molto spesso gli indici vengono definiti su colonne di una tabella che costituiscono una relazione con altre tabelle. Ma vediamo adesso come si fa a definire un indice. Come ricorderete quando abbiamo creato la tabella AnagraficaDipendenti abbiamo impostato per la colonna IdDipendente la propriet IsIdentity a true. Ci significa che quando viene inserita una riga nella tabella in tale colonna viene inserito automaticamente un valore intero progressivo. Supponiamo di volere creare adesso un indice clustered sulle colonne Nome e Cognome della stessa tabella. Clicchiamo con il tasto destro del mouse sulla tabella e scegliamo lopzione Design che, come ormai ben sappiamo, determina lapertura della finestra di progettazione. Clicchiamo sul tasto Manage Indexes e Keys e lavoriamo nuovamente sulla finestra Indexes/Keys. Nella finestra ovviamente troveremo la chiave primaria che abbiamo definito in precedenza e clicchiamo sul tasto Add. Viene aggiunto di seguito alla chiave primaria un altro valore, quello dellindice che stiamo andando a definire

Nellarea delle propriet dellindice possiamo cambiare il suo nome o lasciare quello suggerito da SQL Server. Selezioniamo le colonne da inserire nellindice (nel nostro caso Cognome e Nome) e impostiamo per la propriet Is Unique il valore Yes (cio non possono esserci pi record con lo stesso nome e cognome)

43

Infine clicchiamo su OK. Come potete vedere la creazione di un indice un processo molto semplice utilizzando SQL Server.

15. DiagrammiQuando si termina di costruire un database, con tutti gli elementi relativi (tabelle, indici e relazioni) si passa alla cosiddetta documentazione dello stesso e SQL Server ci viene in aiuto in questo contesto con il Database Diagram Tool, uno strumento molto utile. Uno degli aspetti pi noiosi nella creazione della documentazione di un database quello di presentare le tabelle e le relative relazioni in un diagramma manualmente. Quando si ha a che fare con database di grandi dimensioni chiaramente disegnare un diagramma relativo manualmente un compito gravoso. Un diagramma standard deve permettere di visualizzare tabelle, colonne e relazioni. Altre utili informazioni da visualizzare sono le chiavi primarie di ciascuna tabella. Fortunatamente per noi il Database Diagram Tool di SQL Server Management Studio ci viene in soccorso nella creazione di diagrammi che presentino tali informazioni. Questo tool tuttavia offre pi della semplice possibilit di creare diagrammi, costituendo anche una sorta di interfaccia attraverso cui creare o modificare tabelle, relazioni, chiavi, indici, ecc. Ogni modifica che viene effettuata nel diagramma viene propagata al database. Una cosa importante da sottolineare che non possibile inserire in un diagramma procedure, schemi, utenti, viste e in generale qualsiasi oggetto che non sia una tabella. Andiamo adesso a creare il diagramma del nostro semplice database di esempio. Clicchiamo con il tasto destro del mouse sul nodo Database Diagrams nella finestra Object Explorer e scegliamo lopzione Install Diagram Support

Comparir una finestra di dialogo in cui ci viene chiesta la conferma per la creazione degli oggetti necessari,clicchiamo su Yes. Fatto questo clicchiamo nuovamente con il tasto destro del mouse sul nodo e scegliamo lopzione New Database Diagram. La prima finestra che ci viene presentata la finestra Add Table. In essa selezioniamo le tabelle del nostro database e clicchiamo su Add

44

Dopo la chiusura della finestra Add Table e dopo aver inserito il nome del diagramma (nel nostro caso DiagrammaTest) nella successiva finestra di dialogo potremo vedere nella finestra principale di SQL Server Management Studio il nostro diagramma

Diamo adesso unocchiata alla barra degli strumenti relativa al diagramma e vediamone le funzionalit pi interessanti

45

Il primo pulsante permette la creazione di una nuova tabella in modo del tutto simile a quello che abbiamo visto nella lezione sulla creazione delle tabelle ma con la differenza che occorre utilizzare la finestra delle propriet per ciascuna colonna invece di avere le propriet visibili in basso. Il secondo permette di aggiungere al diagramma tabelle gi esistenti ma che non sono state incluse in esso al momento della creazione Il pulsante Add Related Tables che permette di inserire nel diagramma tutte le tabelle collegate a quella correntemente selezionata, ovvero fra le quali sono state definite relazioni

Mentre con il seguente pulsante si elimina una tabella dal diagramma

Ogni tabella visualizzata caratterizzata da un layout predefinito. E possibile variarlo cliccando sul pulsante Table View e selezionando la vista desiderata

Il pulsante seguente permette invece di visualizzare opportune linee che rendono lidea dei fogli su cui sar suddivisa uneventuale stampa del database

Agendo tramite questi strumenti possibile qualsiasi tipo di diagramma di un database.

16. Lavorare con i datiIl primo passaggio quando si comincia a lavorare con i dati ovviamente quello di inserire record nelle tabelle del database. In SQL Server questa operazione si pu effettuare o tramite istruzioni SQL nella finestra Query Editor o attraverso SQL Server Management Studio. Chiaramente lo scopo della presente guida non quello di approfondire gli aspetti del linguaggio T-SQL perch viene data per scontata una minima conoscenza del linguaggio SQL (che, ricordiamo, costituisce la base del linguaggio T-SQL) da parte di coloro che stanno leggendo la presente guida, tuttavia verranno presentati alcuni frammenti di codice che possono aiutare nella comprensione delle attivit che possibile effettuare sui dati.

46

Per inserire dati nella nostra tabella AnagraficaDipendenti nella finestra Object Explorer clicchiamo con il tasto destro del mouse su di essa e scegliamo nel men contestuale lopzione Script Table As INSERT to New Query Editor Window

Viene generato il seguente codice T-SQLINSERT INTO [DBTEST].[dbo].[AnagraficaDipendenti] ([Nome] ,[Cognome] ,[DataNascita]) VALUES (< Nome, nvarchar(100),> ,< Cognome, nvarchar(100),> ,< DataNascita, datetime,>) GO

nel quale potete notare la mancanza della colonna IdDipendente che, come abbiamo detto, viene gestita automaticamente da SQL Server e per la quale quindi non possibile inserire valori manualmente. Modificando i dati dello script nel modo seguenteINSERT INTO [DBTEST].[dbo].[AnagraficaDipendenti] ([Nome] ,[Cognome] ,[DataNascita]) VALUES ('Carlo', 'Rossi', '05/05/1965') GO

possiamo inserire il nostro primo record nella tabella premendo il pulsante Execute nella barra degli strumenti o il tasto F5 della tastiera, azioni che indicano a SQL Server di eseguire lo script. Se lo script non contiene errori dovremmo vedere il seguente risultato

47

messaggio che indica il corretto inserimento della riga nella tabella. Eseguendo quindi la seguente query nel Query EditorSELECT * FROM dbo.AnagraficaDipendenti

il risultato sar il seguente

In modo del tutto analogo sempre tramite il men contestuale utilizzato per listruzione di insert possibile generare automaticamente anche altri tipi di istruzione come quelle di update e di delete. Vediamo adesso come operare sui dati in modo visuale tramite SQL Server Management Studio. Clicchiamo con il tasto destro del mouse sulla nostra tabella e scegliamo lopzione Edit All Rows. Nella finestra principale di SQL Server verr visualizzata la seguente griglia (ho inserito altri record nella tabella prima di effettuare questa operazione)

Tramite essa possiamo modificare i dati esistenti, cancellarli o inserirne di nuovi. Chiaramente nel nostro contesto desempio evidente come sia molto pi comodo operare in questo modo, tuttavia se immaginiamo di operare su tabelle con centinaia di migliaia di righe pensare di andare a modificare manualmente ciascuna di esse una pazzia e le istruzioni T-SQL risultano sicuramente lopzione migliore. Quando si opera sui dati in entrambi i modi ovviamente necessario rispettare i tipi di dati delle colonne e tutte le opzioni impostate in fase di creazione delle tabelle (ad esempio occorre fare attenzione a non tentare di inserire valori NULL nelle colonne che non lo permettono). Ogni violazione di queste regole porter SQL Server a mostrare messaggi derrore.

48

Dopo avere inserito alcune righe nelle nostre due tabelle di prova (facendo attenzione ad inserire il valore corretto nella colonna IdDipendente della tabella PresenzeDipendenti in modo che esso corrisponda ad un valore esistente nella tabella AnagraficaDipendenti) avremo una situazione simile alla seguente per le due tabelle

Chiaramente lavorare sui dati implica anche effettuare modifiche e cancellazioni e le sintassi di base per effettuare tali operazioni sono rispettivamente

e

49

Lapprofondimento delle varie forme che queste istruzioni possono assumere esula dagli scopi della presente guida e vi invito ad approfondire autonomamente la conoscenza della sintassi SQL.

17. Interrogare i datiLobiettivo delle interrogazioni (query) sui dati quello di ottenere informazioni da SQL Server in modo pi veloce possibile. Il modo pi semplice di ottenere informazioni quello di utilizzare SQL Server management Studio, utilizzando il quale non necessario conoscere i dettagli della sintassi di una query. Per effettuare per interrogazioni particolari pi opportuno utilizzare direttamente istruzioni T-SQL allinterno della finestra Query Editor. I risultati delle interrogazioni possono essere semplicemente visualizzati o anche memorizzati (anche su file ad esempio) in modo che essi possano essere utilizzati per vari scopi. Effettuare interrogazioni tramite SQL Server Management Studio davvero molto semplice. Per fare questo nella finestra Object Explorer clicchiamo con il tasto destro del mouse sulla tabella AnagraficaDipendenti e scegliamo lopzione Select Top 1000 Rows. Questa azione comporta lapertura di una nuova finestra Query Editor che mostra le righe della tabella

Sopra tale finestra possiamo notare listruzione SELECT generata automaticamente da SSMS/****** Script for SelectTopNRows command from SSMS SELECT TOP 1000 [IdDipendente] ,[Nome] ,[Cognome] ,[DataNascita] FROM [DBTEST].[dbo].[AnagraficaDipendenti] ******/

Il valore 1000 accanto alla clausola top indica a SQL Server di restituire le prime 1000 righe della tabella e ovviamente se desideriamo visualizzare un numero diverso di righe basta modificare tale valore e rieseguire la query. Se invece vogliamo effettuare interrogazioni sui dati senza laiuto di SSMS dobbiamo costruire manualmente la nostra query in uno script T-SQL. La scrittura manuale di una

50

query mette a disposizione funzionalit che linterrogazione automatica di SSMS non pu fornire. Scrivendo una query manualmente possibile estrarre dati in qualsiasi ordine, interrogare un numero di colonne minore di quelle totali di una tabella, effettuare interrogazioni legando i dati di pi tabelle contemporaneamente e molto altro. Per questo motivo molto utile dare unocchiata alla sintassi di una istruzione SELECT nel linguaggio T-SQL

Vi invito a consultare la documentazione ufficiale del linguaggio T-SQL per approfondire il significato di tutte le componenti di questa fondamentale istruzione. Scriviamo a questo punto la nostra prima semplice istruzione SELECT nel Query EditorSELECT * FROM dbo.AnagraficaDipendenti

Questa istruzione, quando eseguita restituisce lo stesso risultato della SELECT generata automaticamente da SSMS, lunica differenza che mentre nella prima le colonne erano tutte elencate, in questultima ho utilizzato il simbolo * che indica a SQL Server che si desidera visualizzare tutte le colonne di una tabella. E in generale una buona norma scrivere sempre il nome delle colonne che si desidera visualizzare, tralasciando quelle non rilevanti. Questa pu sembrare una pratica noiosa da seguire ma nel caso di interrogazioni complesse che coinvolgono diverse tabelle essa migliora la velocit delle query. Esistono diverse modalit di visualizzare i dati di una interrogazione: in griglia, in modalit testuale e in un file. Per scegliere tra queste opzioni basta collocarsi in una finestra Query Editor e cliccare nella barra dei men su Query, selezionando poi lopzione Results To che ci permette di scegliere fra le tre modalit

51

Scegliendo lopzione che desideriamo ed eseguendo la query avremo i dati nella modalit scelta. Ecco ad esempio i nostri dati in formato text

Esistono diversi modi di limitare la ricerca di record in una query ed il principale quello di utilizzare una clausola WHERE. Allinterno di tale clausola infatti possibile impostare dei filtri sui valori delle colonne di una tabella utilizzando operatori come , =, LIKE e NOT. Eseguendo una query simile alla seguente otterremo tutti i record della tabella AnagraficaDipendenti in cui il cognome del dipendente inizia con la lettera ASELECT * FROM dbo.AnagraficaDipendenti WHERE Cognome LIKE A%

Chiaramente si tratta di un esempio banale e lo scopo di tale guida non quello di illustrare le potenzialit del linguaggio SQL, per approfondire le quali vi invito a consultarne la documentazione ufficiale. Quando il motore di SQL Server esegue una istruzione SELECT la clausola WHERE la prima ad essere valutata. I dati vengono analizzati utilizzando gli indici, se presenti, per

52

determinare se le varie righe soddisfino le condizioni specificate nella clausola WHERE e in caso positivo esse vengono incluse nell insieme di risultati (result set). La scansione di una tabella pu presentare importanti problemi di prestazioni e lottenimento dei risultati in alcuni casi pu richiedere davvero molto tempo. Per tale motivo per tabelle di grandi dimensioni sempre consigliabile definire opportuni indici (come abbiamo visto nella lezione su questo tema).

18. TransazioniCome abbiamo visto nelle lezioni precedenti tramite le istruzioni T-SQL possibile inserire dati nelle tabelle, modificare e cancellare tali dati. In alcune circostanze quando si esegue una sequenza di combinazioni di tali operazioni desiderabile che tale sequenza porti o ad eseguire tutte le operazioni in essa contenute o a non eseguirne nessuna. In tali scenari ci vengono in aiuto le cosiddette transazioni. Una transazione fondamentalmente un metodo attraverso cui i programmatori possono definire ununit di lavoro che deve essere completata nella sua interezza per essere considerata valida. Esistono quattro concetti fondamentali legati ad una transazione:

Atomicit - Tutte le modifiche ai dati allinterno di una transazione devono essere valide e trasmesse correttamente al database. Se una soltanto delle operazioni non giunge a buon fine tutte le altre devono essere annullate (il cosiddetto rollback della transazione). Consistenza Finch dati non vengono trasmessi al database essi devono rimanere in uno stato consistente e mantenere la propria integrit. Isolamento Ogni modifica fatta allinterno di una transazione deve essere isolata da modifiche apportate da altre transazioni contemporanee. Permanenza Se la transazione va a buon fine le modifiche vengono trasmesse al database e qualsiasi malfunzionamento di sistema (hardware o software) non deve comportare la perdita di tali modifiche.

Una transazione pu essere utilizzata per gestire qualsiasi manipolazione sui dati che si basi su istruzioni UPDATE, DELETE e INSERT o combinazioni di esse. Ovviamente non ha senso utilizzare una transazione se si effettuano soltanto delle SELECT. Un concetto molto importante legato alle transazioni quello di deadlock. Con questo termine si indica la situazione in cui due differenti manipolazioni sugli stessi dati, in transazioni differenti, vengono effettuate contemporaneamente. In questa situazione ogni transazione attende che laltra finisca le sue manipolazioni e quindi si viene a creare una sorta di blocco (il deadlock appunto) che pu interessare solo le tabelle coinvolte o in alcuni casi lintero database. Una transazione viene avviata e alla fine della stessa possibile rendere definitive le manipolazioni sui dati (si parla di commit della transazione) o annullarle interamente (rollback dell atransazione). Lavvio di una transazione avviene tramite il comando BEGIN TRAN. Dopo tale comando (e prima di un eventuale comando COMMIT TRAN o ROLLBACK TRAN) tutte le operazioni che si effettuano sui dati sono incluse in una transazione. Il comando COMMIT TRAN rende le modifiche effettuate sui dati definitive sul database e una volta eseguito non consente di ritornare alla situazione precedente alla transazione. Se invece si vogliono annullare tutte le modifiche fatte sui dati a partire dallinizio di una transazione occorre utilizzare il comando ROLLBACK TRAN.

53

Vediamo adesso alcuni esempi di utilizzo di tali comandi. Utilizziamo la tabella AnagraficaDipendenti e supponiamo di volere cambiare il nome del dipendente con IdDipendente = 3, quindi scriviamo il seguente codiceSELECT 'Prima',IdDipendente,Cognome,Nome FROM dbo.AnagraficaDipendenti WHERE IdDipendente = 3 BEGIN TRAN Modifica UPDATE dbo.AnagraficaDipendenti SET Nome = 'Augusto' WHERE IdDipendente = 3 COMMIT TRAN SELECT 'Dopo',IdDipendente,Cognome,Nome FROM dbo.AnagraficaDipendenti WHERE IdDipendente = 3

Il risultato della nostra istruzione sar

Vediamo adesso lapplicazione del comando ROLBACK TRAN cercando di modificare ancora il nome dello stesso dipendente in Giulio SELECT 'Prima',IdDipendente,Cognome,Nome FROM dbo.AnagraficaDipendenti WHERE IdDipendente = 3 BEGIN TRAN Modifica UPDATE dbo.AnagraficaDipendenti SET Nome = Giulio WHERE IdDipendente = 3 SELECT 'Durante',IdDipendente,Cognome,Nome FROM dbo.AnagraficaDipendenti WHERE IdDipendente = 3 ROLLBACK TRAN SELECT 'Dopo',IdDipendente,Cognome,Nome FROM dbo.AnagraficaDipendenti WHERE IdDipendente = 3 Il risultato della nuova istruzione sar

Come potete notare allinterno della transazione viene eseguito lupdate del record ma successivamente al rollback la situazione viene riportata allo stato originale. Chiaramente non ha senso utilizzare una transazione per un solo UPDATE ma questo esempio molto utile per capire la modalit di funzionamento di tale meccanismo.

54

19. Stored procedure e funzioniStrored procedure e funzioni sono due diversi tipi di oggetti che forniscono funzionalit similari. La differenza principale che una stored procedure un insieme di codice che viene eseguito in una unit di lavoro, mentre una funzione un insieme di codice che viene eseguito in una unit di lavoro ma che contenuta in unaltra unit di lavoro. Quando si scrive una query in una finestra Query Editor possibile salvarne il codice sul disco ma non possibile memorizzarlo su SQL Server. Spesso tuttavia ci si trova nella situazione di dover eseguire interrogazioni multiple in SQL Server e memorizzare tale sequenza di query sul server. Per fare ci possibile utilizzare appunto stored procedure e funzioni. SQL Server assume che ognuno di questi oggetti verr eseguito pi volte e per tale motivo quando viene eseguito per la prima volta viene creato il cosiddetto query plan, con i dettagli su come eseguire al meglio le istruzioni. Come per gli altri oggetti di un database possibile assegnare un determinato livello di sicurezza a stored procedure e funzioni, in modo che soltanto certi utenti possano eseguirle.

Stored ProcedureUna stored procedure (SP) dunque un insieme di comandi T-SQL compilati, direttamente accessibili da SQL Server. Tali comandi vengono eseguiti come ununica unita di lavoro (batch) sul server e il beneficio che il traffico di rete viene ridotto limitando la congestione della rete stessa. Oltre a istruzioni SELECT, UPDATE e DELETE una SP possono richiamare altre SP, utilizzare istruzioni che controllano il flusso di esecuzione e funzioni di aggregazione. E importante sottol