SQL Server - Aspetti Avanzati PDF

Copertina:IV-I copertina
29-10-2007
16:01
Pagina 1
DA T-SQL ALLINTEGRAZIONE CON IL FRAMEWORK.NET.

ECCO COME SFRUTTARE AL MASSIMO IL DATABASE DI MICROSOFT
Programmazione con T-SQL

Integrazione con il framework .NET
Catalogo e funzioni di sistema
SQL SERVER
ASPETTI AVANZATI
icrosoft SQL Server per molti versi
non solo un database. Lo si pu
considerare come un ambiente
completo che espone al programmatore
funzionalit complesse che abbracciano a 360
gradi la gestione dei dati. Come si possono
creare programmi in T-SQL? Quali sono le
modalit con cui SQL server interagisce con il
.NET framework? Come posso ottimizzare al
massimo le query verso il database? e ancora
quali sono gli strumenti di programmazione
diretta che SQL server ci mette a
disposizione? A queste e a tante altre
domande risponde questo Handbook.
Essenziale quanto diretto, Vito Vessia ci
mostra una serie di esempi concreti su come
utilizzare al meglio tutte le tecniche pi
avanzate legate al gigantesco database
server di casa Microsoft
SQL SERVER ASPETTI AVANZATI
2007 Edizioni Master

Tutti i diritti riservati
SQL SERVER
ASPETTI AVANZATI
Vito Vessia
Frontespizio
31-08-2005
17:26
Pagina 2
Frontespizio:Frontespizio
29-10-2007
15:59
Pagina 1
SQL SERVER
ASPETTI AVANZATI
di Vito Vessia
Frontespizio:Frontespizio
29-10-2007
15:59
Pagina 2
003-012:003-006
29-10-2007
16:00
Pagina 3
SQL SERVER
ASPETTI
AVANZATI
Indice
introduzione
Scopo del volume . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8
Prerequisiti per la corretta fruizione del volume . . . . . . . . . . . . . .9

1.1 I blocchi condizionali . . . . . . . . . . . . . . . . . . . . . . . . . . . . .14
1.1.1 IF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .14
1.1.2 WHILE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .15
1.2 Gestione degli errori . . . . . . . . . . . . . . . . . . . . . . . . . . . . .17
1.3 Le stored procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .20
1.4 Le funzioni . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .22
1.4.1 Le funzioni che ritornano valori scalari . . . . . . . . . . . .23
1.4.2 Le funzioni che ritornano resultset . . . . . . . . . . . . . . .25
1.5 Cursori . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .27
1.6 Tabelle temporanee . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .29
1.7 Le funzioni e le stored procedure di sistema . . . . . . . . . . . .31
1.8 I trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .32
Programmare SQL Server in .NET

2.1 Perch scrivere codice SQL Server in .NET . . . . . . . . . . . . . .38
2.2 Metodologia alternative per estendere SQL Server . . . . . . . .42
2.2.1 Chiamate ad oggetti COM da T-SQL . . . . . . . . . . . . . .46
2.2.2 Vantaggi dell'approccio .NET rispetto alle due
precedenti tecnologie . . . . . . . . . . . . . . . . . . . . . . . .54
2.3 Il supporto .NET 2.0 per SQL Server 2005 . . . . . . . . . . . . . .55
2.3.1 Preparazione del database di esempio . . . . . . . . . . . .55
2.3.2 Il progetto in Visual Studio 2005 . . . . . . . . . . . . . . . .59
2.4 Funzioni in CLR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .61
2.4.1 Registrazione dell'assembly e della funzione senza
Visual Studio 2005 Professional . . . . . . . . . . . . . . . . . . . . .68
2.4.2 Test della funzione . . . . . . . . . . . . . . . . . . . . . . . . . . .68
2.4.3 Debug della funzione . . . . . . . . . . . . . . . . . . . . . . . . .69
2.5 Stored procedure in CLR . . . . . . . . . . . . . . . . . . . . . . . . . . .71
I libri di ioPROGRAMMO/SQL Server aspetti avanzati
003-012:003-006
29-10-2007
16:00
Pagina 4
SQL SERVER
ASPETTI
AVANZATI
Indice
2.5.1 Il codice . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .72

2.5.2 Registrazione, test e debug della stored procedure . . .80
2.6 Trigger in CLR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .81
2.6.1 Registrazione, test e debug del trigger . . . . . . . . . . . .84
2.7 Aggregati personalizzati . . . . . . . . . . . . . . . . . . . . . . . . . . .85
2.7.1 Registrazione, test e debug dell'aggregato . . . . . . . . .91
2.8 Tipo definito dall'utente . . . . . . . . . . . . . . . . . . . . . . . . . . .92
2.8.1 Registrazione, test e debug del tipo utente . . . . . . . . .98

3.1 Tabelle del Catalogo di database . . . . . . . . . . . . . . . . . . .104
3.1.1 sysobjects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .106
3.1.2 syscolumns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .107
3.1.3 sysindexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .108
3.1.4 sysusers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .108
3.1.5 sysdatabases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .109
3.1.6 sysdepends . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .111
3.1.7 sysconstraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . .112
3.2 Viste di Catalogo . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .113
3.2.1 sys.objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .113
3.2.2 sys.columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .114
3.3.3 sys.database_principals . . . . . . . . . . . . . . . . . . . . . .115
3.3 Stored procedure di sistema . . . . . . . . . . . . . . . . . . . . . . .116
3.3.1 sp_help . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .116
3.3.2 sp_depends . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .118
3.3.3 sp_helptext . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .119
3.4 Funzioni scalari predefinite . . . . . . . . . . . . . . . . . . . . . . . .120
3.4.1 Funzioni numeriche . . . . . . . . . . . . . . . . . . . . . . . . .122
3.4.2 Funzioni di data . . . . . . . . . . . . . . . . . . . . . . . . . . . .123
3.4.3 Funzioni di stringa . . . . . . . . . . . . . . . . . . . . . . . . . .124
3.4.4 Funzioni di testo/immagine . . . . . . . . . . . . . . . . . . .126
3.4.5 Funzioni di sistema . . . . . . . . . . . . . . . . . . . . . . . . .126
003-012:003-006
29-10-2007
16:00
Pagina 5
SQL SERVER
ASPETTI
AVANZATI
Indice
Nuove funzionalit in SQL Server 2005

4.1 Colonne calcolate persistenti . . . . . . . . . . . . . . . . . . . . . .129
4.2 M.A.R.S. (Multiple Active Result Set) . . . . . . . . . . . . . . . . .130
4.3 Supporto migliorato al formato XML . . . . . . . . . . . . . . . . .133
4.3.1 XPath e XQuery . . . . . . . . . . . . . . . . . . . . . . . . . . . .137
4.3.2 Modificare i dati dei campi XML . . . . . . . . . . . . . . . .140
4.4 Novit nella gestione della sicurezza . . . . . . . . . . . . . . . . .141
4.4.1 Ruoli di amministrazione e di database . . . . . . . . . . .146
4.4.2 Indicazione e ricerca Full Text . . . . . . . . . . . . . . . . . .147
4.4.3 Esempio di interrogazioni Full Text . . . . . . . . . . . . . .149
4.4.4 Attivazione delle funzionalit . . . . . . . . . . . . . . . . . .149
003-012:003-006
29-10-2007
16:00
Pagina 6
003-012:003-006
29-10-2007
16:00
Pagina 7
SQL SERVER
ASPETTI
AVANZATI
Introduzione
INTRODUZIONE
Microsoft SQL Server il sistema di database relazionale (RDBMS) prodotto e sviluppato da Microsoft. Affonda
le sue origini nel prodotto Sybase SQL Server 3 per Unix,
da cui per si fortemente evoluto e differenziato a partire dalla versione 7, che di fatto rappresenta una vera e
propria riscrittura del code base. L'obiettivo originario,
agli inizi degli anni 90, era quello di sviluppare una versione del prodotto per il sistema operativo OS/2.
Tuttavia questa decisione stata quasi da subito disattesa, seguendo la pi generale strategia di uscita adottata
da Microsoft nei confronti di OS/2, a favore del suo
Windows NT. Nel 1994 la collaborazione con Sybase non
viene rinnovata e da allora SQL Server subisce un fork di
codice notevole.
SQL Server 2005 il nome commerciale dell'ultima versione del prodotto RDBMS di Microsoft e durante tutta
la sua fase di sviluppo ha mantenuto il nome in codice
Yukon (un'area geografica del Canada). Come tutti i prodotti Microsoft, SQL Server 2005 gira solo su piattaforma
Windows e in particolare su Windows 2000 (Desktop e
Server), Windows XP e Windows Server 2003. Questo
consente al prodotto di trarre il massimo dalla piattaforma S.O. su cui si basa, permettendo una politica di fortissima integrazione (dal file system, alla gestione della
sicurezza e all'uso ottimizzato delle API di sistema), proprio perch non ha velleit di prodotto multipiattaforma. Inoltre gli consente di mantenere il look & feel dei
tipici prodotti per Windows oltre ad offrire una facilit di
installazione che non ha pari nelle versioni per Windows
di altri prodotti multipiattaforma. Ma probabilmente il
maggior vantaggio rispetto ad altri prodotto concepiti su
piattaforma Unix (praticamente tutti gli altri) la scalaI libri di ioPROGRAMMO/SQL Server aspetti avanzati
003-012:003-006
29-10-2007
16:00
Pagina 8
SQL SERVER
ASPETTI
AVANZATI
Introduzione
bilit: ci sono versioni di SQL Server 2005, come la

Express, che girano su portatili non particolarmente
dotati e, dall'altra parte, ci sono versioni come la
Enterprise che girano su potenti server in cluster. Con
Windows come unico collante.
SCOPO DEL VOLUME

Questo breve volume fornisce una panoramica completa delle funzionalit e delle caratteristiche di SQL Server
2005 specifiche per gli sviluppatori e quindi rappresenta
il secondo e conclusivo volume della serie sul prodotto.
Il primo capitolo affronta una trattazione ampia sulla
programmazione attraverso il linguaggio T-SQL per scrivere stored procedure, funzioni, trigger, funzioni ricorsive e quant'altro questo potente linguaggio consente per
realizzare complessi blocchi di logica applicativa da far
girare direttamente nel database, scritti nella sua lingua
nativa.
Il secondo capitolo propone una completa trattazione di
una delle pi rilevanti novit di SQL Server 2005: la possibilit di scrivere e far eseguire codice .NET all'interno
di SQL Server, rendendolo cos di fatto un potente application server che consente di superare il modello tradizionale client/server. Con SQL Server 2005, infatti, possibile scrivere direttamente stored procedure, funzioni e
trigger in C# o nei linguaggi .NET. Questo codice gira
direttamente in hosting in SQL Server 2005, che dispone
di una sua completa macchina virtuale CLR, e questi
oggetti possono essere richiamati direttamente da TSQL come se fossero funzioni native o di sistema.
Il terzo capitolo descrive la struttura dei metadati di SQL
Server e cio le metodologie per interrogare la struttura
8
003-012:003-006
29-10-2007
16:00
Pagina 9
SQL SERVER
ASPETTI
AVANZATI
Introduzione
stessa degli oggetti del database usando un approccio

simile a quello che si usa per interrogare i dati di tabelle
definite dall'utente, dato che SQL Server usa speciali
tabelle relazionali, dette di sistema, per conservare
anche la sua struttura interna. Il capitolo si chiude con
una carrellata sulle principali funzioni scalari integrate
nel motore e utilizzabili dall'utente e dallo sviluppatore.
Infine, l'ultimo capitolo una miscellanea di diversi
aspetti avanzati di SQL Server 2005: dal supporto al formato XML, al supporto alla tecnolgia M.A.R.S. e altre
funzionalit innovative dell'ultima versione del prodotto.
REQUISITI PER LA CORRETTA

FRUIZIONE DEL VOLUME
Questa guida fa riferimento alla versione standard di
SQL Server 2005, dotata di versione completa del tool
SQL Management Studio. Per la gran parte delle informazioni applicabile anche alla versione Express, distribuita gratuitamente da Microsoft insieme ad una versione leggera dello strumento di amministrazione, SQL
Mangement Studio Express. Pertanto, coloro che si avvicinano per la prima volta a questo prodotto o per gli altri
utilizzatori di SQL Server che non hanno ancora fatto la
migrazione alla versione 2005, la versione gratuita
Express offre un'ottima possibilit di studiare e scoprire
il prodotto a costo zero. Peraltro, la licenza che accompagna la versione Express la rende di fatto un prodotto
vero e non solo una versione vetrina-giocattolo perch
con la Express possibile sviluppare vendere applicazioni che si basano su questa versione. Senza costi per lo
sviluppatore e per l'utente finale, garantendo cos un
003-012:003-006
29-10-2007
16:00
Pagina 10
SQL SERVER
ASPETTI
AVANZATI
Introduzione
percorso di migrazione molto scalabile verso le versioni

successive a pagamento e rappresentando una valida
alternativa a prodotti come Microsoft Access o a soluzioni pi avanzate open source come MySQL o Firebird.
SQL Server 2005 Express scaricabile gratuitamente dal
seguente indirizzzo:
http://www.microsoft.com/downloads/details.aspx?Fami
lyID=220549B5-0B07-4448-8848-DCC397514
B41&displaylang=it
La versione leggera dello strumento di amministrazione,
SQL Management Studio Express, disponibile in
download gratuito dal seguente indirizzo:
http://www.microsoft.com/downloads/details.aspx?famil
yid=C243A5AE-4BD1-4E3D-94B8-5A0F62
BF7796&displaylang=it
Entrambi prodotti sono disponibili in lingua italiana.
Nel volume si fatto riferimento a tre database di esempio: AdventureWorks, il vero database di riferimento
della versione 2005, Northwind, il database che ha
accompagnato migliaia di programmatori nello studio
SQL Server 7.0 e SQL Server 2000 e l'antico e un po'
superato anche se sempre valido pubs.
Il primo fornito in dotazione con SQL Server 2005 nelle
versioni a pagamento. Gli utilizzatori della versione
Express possono scaricare lo script T-SQL di creazione di
AdventureWorks da questo indirizzo:
http://www.microsoft.com/downloads/details.aspx?Fami
lyID=e719ecf7-9f46-4312-af89-6ad8702e4e6
e&DisplayLang=en
Northwind e Pubs sono invece disponibili per tutti
(infatti non vengono distribuiti pi con SQL Server 2005,
nemmeno nelle versioni commerciali), al seguente indirizzo:
10
003-012:003-006
29-10-2007
16:00
Pagina 11
SQL SERVER
ASPETTI
AVANZATI
Introduzione
http://www.microsoft.com/downloads/details.aspx?famil
yid=06616212-0356-46a0-8da2-eebc53a68034&displaylang=en
Se vi risulta troppo complicato riportare questi indirizzi,
usata il buon vecchio Google usando le keyword SQL
Server 2005 Samples... Una volta scaricati i setup ed
installati, sufficiente aprire i file .Sql da SQL
Management Studio (o Express) ed eseguirli.
Genereranno l'intero database compresi i dati di esempio preziosi per i nostri esempi e per il vostro studio.
11
003-012:003-006
29-10-2007
16:00
Pagina 12
Capitolo 1 (013-036):007-050
29-10-2007
Capitolo 1
16:02
Pagina 13
SQL SERVER
ASPETTI
AVANZATI
PROGRAMMAZIONE CON T-SQL

T-SQL offre numerosi costrutti del linguaggio che consentono di realizzare veri e proprio programmi e di gestire i flusso di esecuzione. Sono necessari quando non ci si deve limitare ad effettuare query, a
manipolare o modificare dati o a modificare la strutture degli oggetti del database, ma quando si implementa della vera e propria
logica in T-SQL.
Una prima principale estensione costituita dai blocchi di dichiarazione; infatti possibile delimitare blocchi di codice con una sequenza del tipo:
BEGIN
--codice T-SQL
--altro codice
END
Inoltre, come in ogni linguaggio di programmazione che si rispetti,

possibile definire variabili locali da usare all'interno di query, di
blocchi di logica e in generale da codice T-SQL. Vediamone un esempio sul solito Northwind:
--dichiarazione delle variabili locali
DECLARE @MINBIRTHDATE AS DATETIME,
@COUNTRY AS NVARCHAR(15)
--assegnazione di una variabile
SET @COUNTRY = 'UK'
--uso di una variabile nella clausola WHERE
--assegnazione di una variabile attraverso una query
SELECT @MINBIRTHDATE = MIN(BirthDate)
FROM Employees
WHERE Country = @COUNTRY
13
Capitolo 1 (013-036):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:02
Pagina 14
Capitolo 1
--uso del contenuto di una variabile

PRINT @MINBIRTHDATE
Le variabili vengono dichiarate nel blocco DECLARE, ciascuna variabile locale preceduta da una @ e dopo il nome segue il tipo della
variabile (secondo il sistema di tipi di SQL Server), preceduto dalla parola chiave AS. Se nella DECLARE vi sono pi variabili queste vanno
separate da virgole. Naturalmente possibile avere diversi blocchi DECLARE nello stesso blocco di codice. Le variabili possono essere usate all'interno di query (in tutte le clausole), all'interno i istruzioni INSERT, UPDATE e DELETE oltre che in blocchi di codice T-SQL di ogni
genere, come si pu intuire dall'esempio.
1.1 I BLOCCHI CONDIZIONALI

T-SQL offre un set di costrutti condizionali che lo rendono un completo linguaggio di programmazione procedurale consentendo, cos, di
costruire codice di logica che vada al di l delle possibilit offerte
dalle query e dai comandi DML.
1.1.1 IF
La parola chiave IF consente di introdurre blocchi condizionali, come
mostrato nel seguente esempio commentato:
DECLARE
@Country AS NVARCHAR(15),
@RefEmployeeId AS INT
SET @Country = 'Italy'
--se vi sono almeno due dipendenti provenienti dal paese contenuto
--nella variabile locale @Country
IF (SELECT COUNT(*) FROM Employees WHERE Country = @Country) > 2
BEGIN
--allora viene recuperata la chiave (EmployeeId) del pi
SELECT TOP 1 @RefEmployeeId = EmployeeID
14
Capitolo 1 (013-036):007-050
29-10-2007
Capitolo 1
16:02
Pagina 15
SQL SERVER
ASPETTI
AVANZATI
FROM Employees
WHERE Country = @Country
ORDER BY HireDate DESC
END
ELSE
BEGIN
--altrimenti se ne provvede ad inserire uno nuovo
INSERT INTO Employees
(LastName, FirstName, BirthDate, Country)
VALUES
('Vessia', 'Vito', '19740830', @Country)
--e a conservarne la chiave identity attribuita dal database attraverso
la variabile
--globale di sistema @@identity che contiene sempre l'ultima identity
assegnata
--ad una tabella del database contenente campi identity
SELECT @RefEmployeeId = @@identity
END
--stampa del risultato finale
PRINT @RefEmployeeId
1.1.2 WHILE
Si pu gestire, inoltre, il tipo costrutto condizionale WHILE che consente di ripetere un determinato blocco di codice fino a quando resta vera una certa condizione. Osserviamo il seguente esempio commentato:
DECLARE @Country AS NVARCHAR(15),
@RefEmployeeId AS INT
SET @Country = 'Italy'
--viene ripetuto il blocco nella WHILE fino a che non vi sono almeno quat
tro
--dipendenti provenienti dal paese contenuto nella variabile locale
15
Capitolo 1 (013-036):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:02
Pagina 16
Capitolo 1
@Country
WHILE (SELECT COUNT(*) FROM Employees WHERE Country = @Country)
<4
BEGIN
VALUES
('Vessia', 'Vito', '19740830', @Country)
END
--allora viene recuperata la chiave (EmployeeId) pi recente
FROM Employees
ORDER BY EmployeeID DESC
--stampa del risultato finale
PRINT @RefEmployeeId
Una WHILE pu essere interrotta prima che la condizione di valutazione smetta di essere vera introducendo un'istruzione BREAK all'interno del codice stesso.
Infine SQL Server 2005 introduce una nuova e pi potente gestione
delle eccezioni. In passato era solo possibile sollevare un'eccezione
con l'istruzione RAISERROR e testando la variabile globale @@ERROR. Osserviamo il seguente esempio su Northwind:
IF (SELECT COUNT(*) FROM Employees WHERE Country = 'Italy') < 10
BEGIN
RAISERROR ('Non vi sono abbastanza dipendenti nella filiale',
--messaggio
1, --gravit
5) --stato
END
16
Capitolo 1 (013-036):007-050
29-10-2007
Capitolo 1
16:02
Pagina 17
SQL SERVER
ASPETTI
AVANZATI
1.2 GESTIONE DEGLI ERRORI

Se nella tabella Employees ci sono meno di dieci impiegati di origine italiana viene sollevata un'eccezione con l'istruzione RAISERROR.
Il primo parametro rappresenta la stringa del messaggio di errore, il
secondo la gravit e il terzo lo stato. I tre parametri hanno un significativo esclusivamente applicativo e pertanto ciascuno pu gestirlo a piacimento. SQL Server risponderebbe con un perentorio:
Non vi sono abbastanza dipendenti nella filiale
Msg 50000, Level 1, State 5
Esiste anche un'altra versione dell'istruzione che permette di non

specificare ogni volta il testo del messagio di errore, ma di registrare nel sistema un codice numerico di errore personalizzato e di associare ad esso un messaggio, magari configurato per le varie lingue.
Vediamo come fare con il seguente esempio:
EXEC sp_addmessage 50002, 1, 'Non vi sono abbastanza dipendenti nella
filiale'
IF (SELECT COUNT(*) FROM Employees WHERE Country = 'Italy') < 10
BEGIN
RAISERROR (50002, --messaggio
1, --gravit
5) --stato
END
In questo caso viene registrato nel sistema il codice di errore 50002

(i codici di errori applicativi devono necessariamente partire da 50000)
con il testo corrispondente. Questa operazione va fatta una sola volta e il nuovo messaggio viene registrato definitivamente fino a successiva variazione o rimozione. A questo punto possibile sollevare
l'errore semplicemente l'errore attraverso il suo codice, come moI libri di ioPROGRAMMO/SQL Server aspetti avanzati
17
Capitolo 1 (013-036):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:02
Pagina 18
Capitolo 1
strato, per ottenere il seguente messaggio dal sistema:

Non vi sono abbastanza dipendenti nella filiale
Msg 50002, Level 1, State 5
La gestione e l'intercettazione di errori sollevati direttamente dal sistema era un'operazione complicata basata sul vecchio approccio
alla BASIC e comunque non consentiva di annullare un errore ma
solo di intercettarlo. Osserviamo il seguente esempio:
--tentativo di inserimento di un record duplicato su Customers di Northwind
--l'operazione solleva l'errore 2627 di sistema che la violazione della
chiave primaria
INSERT INTO Customers
(CustomerId, CompanyName)
VALUES
('AROUT', 'Nuova Arout')
--viene riletto il valore di errore corrente dalla variabile globale @@ERROR
-- importante ricordare che essa viene azzerata ad ogni istruzione
e quindi
--il suo valore va riletto immediatamente dopo l'istruzione che pu causare
l'errore
IF @@ERROR = 0
BEGIN
--in caso di errore si salta alla label Fine
GOTO Fine
END
--esempio di label applicativa che gestisce il blocco di errore
Errore:
PRINT 'Si verificato un errore di inserimento'
--esempio di label applicativa posta alla fine del blocco di codice
Fine:
PRINT 'Fine operazione'
18
Capitolo 1 (013-036):007-050
29-10-2007
Capitolo 1
16:02
Pagina 19
SQL SERVER
ASPETTI
AVANZATI
L'output di questo blocco di codice il seguente:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Customers'. Cannot insert
duplicate key in object 'dbo.Customers'.
The statement has been terminated.
Si verificato un errore di inserimento
Fine operazione
La gestione di errori rudimentali comunque riuscita ad intercettare e a gestire l'errore, ma non ad annullarne l'effetto nel blocco di codice. Proviamo a riscrivere l'esempio usando la nuova e potente gestione degli errori di SQL Server 2005 basata sul costrutto TRY CATCH:
BEGIN TRY
--blocco di codice che causa l'errore
INSERT INTO Customers
(CustomerId, CompanyName)
VALUES
('AROUT', 'Nuova Arout')
END TRY
BEGIN CATCH
--blocco di codice che gestisce l'errore
PRINT 'Si verificato un errore di inserimento'
END CATCH
PRINT 'Fine operazione'
Nel blocco TRY si inserisce il codice che si intende proteggere perch

foriero di eventuali errori. Nel blocco CATCH, invece, va gestito l'errore eventuale e viene saltato se non si verifica. A parte la notevole
pulizia e chiarezza del codice rispetto al codice precedente, proviamo ad osservare anche l'output:
19
Capitolo 1 (013-036):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:02
Pagina 20
Capitolo 1
Si verificato un errore di inserimento

Fine operazione
I messaggi applicativi sono gli stessi dell'esempio nella vecchia maniera, ma l'errore stato annullato e l'esecuzione del resto del codice fatta salva perch si gestita applicativamente la situazione
d'errore.
1.3 LE STORED PROCEDURE

Le stored procedure sono uno dei metodi pi efficaci per spostare
la logica di programmazione sul server. Esse sono invocabili dal codice T-SQL come normali procedure in un qualsiasi linguaggio di programmazione. Al loro interno posso effettuare scritture o manipolazioni sui dati delle tabelle del database e/o eseguire interrogazioni.
Eccone la sintassi in un esempio commentato che deriva estende
l'esempio precedente basato visto per l'istruzione IF:
--creazione di una stored procedura con la parola chiave
CREATE PROCEDURE <nome>
--la modifica di un'eventuale procedura gi esistente si effettua con la
ALTER PROCEDURE
CREATE PROCEDURE [dbo].[GetLastestClerk]
--dichiarazione dei paramatri passati come argomento; i parametri di
default vengono passati
--con la sintassti @parametro AS tipo = valore_default
@LastName AS NVARCHAR(20),
@FirstName AS NVARCHAR(10),
@BirthDate AS DATETIME = NULL
--fine degli argomenti passati
AS
--corpo della stored procedure; nell'esempio: se vi sono almeno due
20
Capitolo 1 (013-036):007-050
29-10-2007
Capitolo 1
16:02
Pagina 21
SQL SERVER
ASPETTI
AVANZATI
dipendenti
--provenienti dal paese contenuto nella variabile locale @Country
IF (SELECT COUNT(*) FROM Employees WHERE Country = @Country) <= 2
BEGIN
VALUES
(@LastName, @FirstName, @BirthDate, @Country)
END
SELECT TOP 1 EmployeeId, FirstName, LastName, Country
FROM Employees
ORDER BY EmployeeID DESC
In pratica la procedura accetta il parametro @Country, cio il paese

di riferimento su cui effettuare le ricerche sul numero di dipendenti
per quel paese e i dati anagrafici del nuovo dipendente da inserire nella tabella Employees qualora i dipendenti per il paese indicato come
parametro siano in numero inferiore a 2. Le stored procedure posso
ritornare un resultset, in tal caso l'ultima operazine SQL da eseguire nel corpo della procedura deve ritornare un resultset oppure pu
eseguire DML. altres possibile passare parametri per referenza,
che quindi potranno essere rivalorizzati all'interno della procedura.
Ecco come dovrebbe la definizione di una stored procedure un parametro passato come argomento:
ALTER PROCEDURE [dbo].[GetLastestClerkWithOut]
@LastName AS NVARCHAR(20),
@FirstName AS NVARCHAR(10),
@BirthDate AS DATETIME = NULL,
21
Capitolo 1 (013-036):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:02
Pagina 22
Capitolo 1
@IsNew AS BIT OUT

AS
--assegnazione del parametro di output
SET @IsNew = 0
Richiamare una stored procedura da codice SQL molto semplice. Ecco come richiamare la procedura appena scritta (in Figura 1.1 mostrato l'esempio in esecuzione):
EXEC GetLastestClerk 'Italy', 'Vessia', 'Vito'
Se invece volessimo chiamare la stored procedure con parametro di

output, dovremmo:
DECLARE @isnew AS BIT
EXEC [GetLastestClerkWithOut] 'Italy1', 'Vessia', 'Vito', '19740830',
@isnew OUT
SELECT @isnew
Figura 1.1: Esecuzione di una stored procedura che restituisce resultset
1.4 LE FUNZIONI
Le funzioni hanno in SQL lo stesso significato dell'analogo termine
nella programmazione procedurale: si tratta di procedure a cui si
22
Capitolo 1 (013-036):007-050
29-10-2007
Capitolo 1
16:02
Pagina 23
SQL SERVER
ASPETTI
AVANZATI
passano argomenti e che restituiscono risultati.
1.4.1 Le funzioni che ritornano valori

scalari
La prima e pi semplice forma rappresentata dalle funzioni che restituiscono valori scalari. Osserviamo un esempio basato sulla stored
procedure dellesempio precedente che, questa a volta, a fronte del
parametro @Country, si limita a restituire l'EmployeeId del dipendente assunto pi di recente e proveniente dal paese indicato dall'argomento:
--creazione di una funzione con la parola chiave CREATE FUNCTION
<nome>
ALTER FUNCTION
CREATE FUNCTION [dbo].[GetLatestClerkScalarFunction]
(
@Country AS NVARCHAR(15)
)
--tipo del valore di ritorno scalare della funzione (nell'esempio sar
un intero)
RETURNS INT
AS
--corpo della funzione racchiuso in un blocco BEGIN END
BEGIN
DECLARE @RefEmployeeId AS INT
FROM Employees
23
Capitolo 1 (013-036):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:02
Pagina 24
Capitolo 1

--valore di ritorno della funzione: la RETURN deve essere sempre presente
nella funzione
--ma pu ripetersi pi a volte a seconda del flusso di esecuzione
della funzione e non deve
--necessariamente stare alla fine
RETURN @RefEmployeeId
--istruzione di chiusura del corpo della funzione
END
L'uso di una funzione semplice come l'uso di una stored procedure; dato che restituisce un valore scalare, pu essere adoperato direttamente con l'istruzione T-SQL PRINT che stampa un valore:
PRINT dbo.GetLatestClerkScalarFunction('Italy')
Oppure pu essere utilizzato all'interno di una query e, pi in generale, all'interno di qualsiasi blocco di codice T-SQL. Nel seguente
esempio viene usato per restituire la colonna di una query e quindi
usato nella clausola SELECT, inoltre compare anche nella clausola
WHERE come condizione:
Figura 1.2: Una funzione scalare in azione
24
Capitolo 1 (013-036):007-050
Capitolo 1
29-10-2007
16:02
Pagina 25
SQL SERVER
ASPETTI
AVANZATI
SELECT DISTINCT Country,

dbo.GetLatestClerkScalarFunction(Country) AS LastEmployeeId
FROM Employees
WHERE dbo.GetLatestClerkScalarFunction(Country) > 3
In Figura 1.2 ne possiamo osservare il risultato.
1.4.2 Le funzioni che ritornano resultset

Esiste un'interessante variante delle funzioni che restituisce un resultset (un oggetto TABLE) anzich un valore scalare, analogamente a quanto farebbe una query o una stored procedure. Osserviamone una definizione derivata dal precedente esempio che, questa volta, non si
limita a restituire l'Id del dipendente assunto pi di recente proveniente dal paese passato come argomento, ma un resultset corrispondente ai due
--creazione di una funzione con la parola chiave CREATE FUNCTION
<nome>
ALTER FUNCTION
CREATE FUNCTION [dbo].[GetLatestClerkTableFunction]
(
@Country AS NVARCHAR(15)
)
--questa volta la RETURNS riporta la parola chiave TABLE ad indicare la
restituizione di un resultset
RETURNS TABLE
AS
RETURN
(
25
Capitolo 1 (013-036):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:02
Pagina 26
Capitolo 1
SELECT TOP 2 *
FROM Employees
)
L'uso di queste funzioni molto potente perch possono essere adoperate nelle query come normali oggetti tabella o viste, come nell'esempio che segue.
SELECT *
FROM dbo.GetLatestClerkTableFunction('Italy')
Fino a SQL Server 2000, per, questa modalit d'uso era fortemente limitata dall'impossibilit di correlare in una query l'argomento
della funzione di tipo TABLE con i campi delle altre tabelle o viste in
JOIN. Era infatti possibile semplicemente possibile valori scalari dichiarati esternamente alla query o valori costanti, ma non valori di correlazione. SQL Server 2005 introduce una potente novit: l'istruzione APPLY che supera questo limite. Osserviamo il seguente esempio:
Figura 1.3: Una funzione scalare in azione
26
Capitolo 1 (013-036):007-050
29-10-2007
Capitolo 1
16:02
Pagina 27
SQL SERVER
ASPETTI
AVANZATI
SELECT DISTINCT e.Country, c.EmployeeId, c.LastName, c.FirstName

FROM Customers e
CROSS APPLY dbo.GetLatestClerkTableFunction(e.Country) AS c
Esso va interpretato come una INNER JOIN tra Customers e la funzione GetLatestClerkTableFunction in cui la correlazione a livello
di argomento passato alla funzione. In Figura 1.3 se ne pu osservare il risultato.
1.5 CURSORI
SQL Server offre il supporto ai cursori, cio alla possibilit di non ottenere il risultato di una query come un blocco di dati unico e continuo, ma come un flusso su cui effettuare l'enumerazione, analogamente ad una for each di un linguaggio moderno ad alto livello. Piuttosto che procedere con lunghe e noiose spiegazioni teoriche, procediamo ad analizzare e studiare un esempio completo
DECLARE @Country as NVARCHAR(15)
--dichiarazione di un cursore
DECLARE EmployeesCountriesCursor CURSOR FOR
--la query su cui agir il cursore
SELECT Country
FROM Employees
GROUP BY Country
--apertura del cursore
OPEN EmployeesCountriesCursor
--recupero della prima riga del cursore
FETCH NEXT FROM EmployeesCountriesCursor
--il campo o i campi della query vanno a finire in altrettante variabili locali
dello stesso tipo;
--tali variabili vanno precedentemente dichiarate e nella INTO vanno
27
Capitolo 1 (013-036):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:02
Pagina 28
Capitolo 1
indicate tutte queste variabili

--nella stessa posizione in cui vengono restituite dal loro corrispondente
campo nella query
INTO @Country
--il cursore viene testato con una WHILE fino non si giunge all'EOF
WHILE @@FETCH_STATUS = 0
BEGIN
--corpo di operazioni eseguibili all'interno del ciclo del cursore
--nell'esempio viene invocata una stored procedure a partire dai valori
della
--riga corrente del cursore
EXEC GetLastestClerk @Country, 'Nuovo Dipendente', 'Nuovo'
--viene recuperata la riga successiva del cursore, se c'
INTO @Country
END
Figura 1.4: La APPLY in azione
28
Capitolo 1 (013-036):007-050
29-10-2007
Capitolo 1
16:02
Pagina 29
SQL SERVER
ASPETTI
AVANZATI
--viene chiuso il canale dati col cursore

CLOSE EmployeesCountriesCursor
--viene deallocato il cursore per liberare risorse
DEALLOCATE EmployeesCountriesCursor
In Figura 1.4 possibile osservare l'esempio in esecuzione.
1.6 TABELLE TEMPORANEE

Le tabelle temporanee sono delle normali tabelle di SQL Server, definite allo stesso modo delle tabella tradizionali, ma che hanno la
particolarit di avere uno scope temporaneo, cio nascono e muoiono nell'ambito della singola sessione di connessione e database, non
lasciando alcuna traccia sul database dopo la loro scomparsa. Per
la loro stessa natura transitoria non risiedono fisicamente sul database correntemente in uso ma sul database Temp di SQL Server che
un database di sistema adoperato dall'engine proprio per eseguire operazioni temporanee. Osserviamo la definizione di una tabella
temporanea. L'unico elemento che la distingue dall'analoga creazione di una tabella fisica l'obbligo del prefisso # davanti al nome
della tabella. Questa condizione necessaria e sufficiente perch la
tabella venga considerata e gestita come temporanea.
CREATE TABLE #NewClerks
(
EmployeeId INT NOT NULL,
LastName NVARCHAR(20),
FirstName NVARCHAR(10),
Country NVARCHAR(20)
)
Osserviamo il seguente esempio d'uso della tabella temporanea apI libri di ioPROGRAMMO/SQL Server aspetti avanzati
29
Capitolo 1 (013-036):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:02
Pagina 30
Capitolo 1
pena creata. Si tratta di una versione leggermente modificata del

codice di esempio del cursore introdotto nel paragrafo precedente,
ma che questa volta non si limita a chiamare la stored procedure,
ma a inserirne i valori del resultset di ritorno nella tabella temporanea d'esempio, adoperando una versione particolare dell'istruzione
INSERT che consente di inserire direttamente i valori di una stored procedure in una tabella:
DECLARE @Country as NVARCHAR(15)
DECLARE EmployeesCountriesCursor CURSOR FOR
SELECT Country
FROM Employees
GROUP BY Country
OPEN EmployeesCountriesCursor
INTO @Country
WHILE @@FETCH_STATUS = 0
BEGIN
--viene invocata la stored procedure GetLastestClerk, come
nell'esempio precedente,
--essa restituisce i campi EmployeeId, LastName, FirstName, Country
che vengono
--inseriti come nuova riga della tabella temporanea (questa modalit
della INSERT
-- adoperabile anche nelle tabelle normali)
INSERT INTO #NewClerks
EXEC GetLastestClerk @Country, 'Nuovo Dipendente', 'Nuovo'
30
Capitolo 1 (013-036):007-050
29-10-2007
Capitolo 1
16:02
Pagina 31
SQL SERVER
ASPETTI
AVANZATI
INTO @Country
END
CLOSE EmployeesCountriesCursor
DEALLOCATE EmployeesCountriesCursor
Eseguiamo una interrogazione sulla tabella temporanea, analogamente a quanto faremmo con le tabelle fisiche. In Figura 1.5 possiamo osservarne il risultato.
SELECT *
FROM #NewClerks
Figura 1.5: Le tabelle temporanee
1.7 LE FUNZIONI E LE STORED

PROCEDURE DI SISTEMA
SQL Server offre una quantit impressionante di funzioni e stored
procedure di sistema. Dalla funzione GetDate() per ottenere la data
corrente di sistema, alle stored procedure per impostare parametri ed
31
Capitolo 1 (013-036):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:02
Pagina 32
Capitolo 1
impostazioni dei vari oggetti di sistema. Riportarli tutti sarebbe impossibile, ma anche riportarne una parte avrebbe poco senso vista l'eseguit dello spazio e dunque si rimanda alla documentazione ufficiale del produttore per consultare una lista esaustiva di queste informazioni. Esse verranno affrontate pi diffusamente in uno dei prossimi capitoli.
1.8 I TRIGGER
I trigger sono l'equivalente degli eventi nella programmazione tradizionale. In pratica sono porzioni di codice T-SQL che possono essere fatti eseguire in automatico dal database engine quando su una
tabella si verifica un'operazione di INSERT, UPDATE e/o DELETE. Osserviamone la sintassi:
CREATE TRIGGER [ schema.]nome_trigger
ON { nome_tabella | nome_vista }
{ FOR|AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS { sql_statement [ ; ] [ ...n ] }
Il trigger pu agire sul tabella o sulla vista il cui nome segue la clausola ON, pu agire su operazioni di INSERT, UPDATE e/o DELETE (lo
stesso trigger pu servire anche pi operazioni DML sulla stessa tabella o vista) e pu essere di due tipi:
G
32
" FOR, agisce prima che venga effettuata l'operazione di scrittura sulla tabella e pertanto pu essere usato per impedirne il
completamento in caso di inconsistenza del dato da scrivere;
" AFTER, agisce solo dopo che l'operazione di scrittura sulla tabella stata completata senza errori e non applicabile sulle viste;
" INSTEAD OF, agisce in sostituzione dell'operazione DML, signi-
Capitolo 1 (013-036):007-050
29-10-2007
Capitolo 1
16:02
Pagina 33
SQL SERVER
ASPETTI
AVANZATI
fica che se, ad esempio, viene impostato un trigger INSTEAD OF

DELETE su una determinata vista o tabella, la DELETE non verr
mai realmente eseguita ma al suo posto verr eseguito il trigger.
Osserviamo il seguente esempio di trigger di tipo AFTER tratto dal AdventureWorks che agisce sulla tabella Department contenuta nello
schema HumanResources:
--creazione del trigger con definizione della tabella di riferimento
CREATE TRIGGER [HumanResources].[uDepartment] ON
[HumanResources].[Department]
--il trigger di tipo AFTER UPDATE
AFTER UPDATE NOT FOR REPLICATION AS
BEGIN
SET NOCOUNT ON;
--dopo un'operazione di update di una riga di Department, il trigger
modifica il campo ModifiedDate
--della tabella stessa impostando la data corrente come suo valore
UPDATE [HumanResources].[Department]
SET [HumanResources].[Department].[ModifiedDate] = GETDATE()
--la parola chiave "inserted" rappresenta la tabella coinvolta dal trigger,
ne in pratica il this
FROM inserted
WHERE inserted.[DepartmentID] =
[HumanResources].[Department].[DepartmentID];
END;
I trigger sono certamente molto comodi e potenti anche se a volte rendono meno comprensibile il flusso di modifica dei dati proprio perch sono asincroni e non vengono mai esplicitamente invocati dal chiamante. Inoltre rappresentano evidentemente un overhead per il sistema e quindi vanno adoperati con moderazione. Microsoft, per,
da SQL Server 2000 introduce una variante di trigger di tipo INSTEAD
33
Capitolo 1 (013-036):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:02
Pagina 34
Capitolo 1
OF che quindi non segue l'operazione di modifica della tabella o della vista, ma la sostituisce del tutto. Il tipico esempio d'uso costituito dalle applicazioni che non cancellano mai veramente i record, ma
si limitano ad operare cancellazioni logiche in modo da salvaguardare lo storico e poter facilmente recuperare eventuali situazione di inconsistenza dei dati a seguito di cancellazioni maldestre. L'approccio tipico di aggiungere un campo BIT Deleted nella tabella interessata dalla cancellazione logica, facendogli assumere il valore 0 quando il record valido e 1 quando il record cancellato. L'approccio tradizionale alle cancellazioni prevedeva l'uso di UPDATE (sul campo Deleted) per operare le cancellazioni logiche. Con il nuovo approccio basta introdurre un trigger INSTEAD OF DELETE che, a fronte di un'operazione di DELETE sulla riga, effettui l'UPDATE sul campo di scope Deleted.
Osserviamo, infine, un altro esempio di trigger INSTEAD OF DELETE
tratto da AdventureWorks:
ALTER TRIGGER [HumanResources].[dEmployee] ON
[HumanResources].[Employee]
INSTEAD OF DELETE NOT FOR REPLICATION AS
BEGIN
SET NOCOUNT ON;
DECLARE @DeleteCount int;
SELECT @DeleteCount = COUNT(*) FROM deleted;
IF @DeleteCount > 0
BEGIN
RAISERROR
(N'Employees cannot be deleted. They can only be marked as not
current.', -- Message
10, -- Severity.
1); -- State.
34
Capitolo 1 (013-036):007-050
Capitolo 1
29-10-2007
16:02
Pagina 35
SQL SERVER
ASPETTI
AVANZATI
-- Rollback any active or uncommittable transactions

IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
END;
END;
35
Capitolo 1 (013-036):007-050
29-10-2007
16:02
Pagina 36
Capitolo 2 (037-102):007-050
Capitolo 2
29-10-2007
16:03
Pagina 37
SQL SERVER
ASPETTI
AVANZATI
PROGRAMMARE
SQL SERVER IN .NET
SQL Server 2005 introduce il supporto alla scrittura di stored procedure, funzioni, trigger e tipi definiti dall'utente in un qualsiasi linguaggio
del CLR Common Language Runtime (C#, Visual Basic .NET ecc...). Questi oggetti verranno ospitati ed eseguiti all'interno di una istanza della
macchina virtuale integrata in SQL Server.
Le stored procedure, le funzioni e i trigger vengono scritti come metodi statici di classi .NET, invece i tipi definiti dall'utente sono scritti come
intere classi. Il codice cos definito viene compilato in un assembly che
viene caricato in SQL Server 2005 usando l'istruzione T-SQL CREATE
ASSEMBLY. Ogni stored procedure, funzioni, trigger e tipo viene registrato nel database con la corrispondente CREATE PROCEDURE, CREATE FUNCTION, CREATE TRIGGER, CREATE TYPE e CREATE AGGREGATE. Da quel momento essere possono essere impostate ed utilizzate
come se fossero oggetti nativi T-SQL e dunque richiamabili anche da TSQL.
La versione di .NET supportata da SQL Server 2005 la 2.0 e pertanto
Visual Studio 2005 ne supporta lo sviluppo, il caricamento e il debugging nel database. interessante rilevare che i progetti Visual Studio
2005 che producono codice .NET per SQL Server 2005 possono riferire assembly esterni, come ogni altra applicazione .NET, e che sia Visual
Studio che SQL Server offrono il supporto a questa funzionalit.
Come vedremo nel proseguo del capitolo, tutte le operazioni di caricamento degli assembly e di registrazione degli oggetti .NET in SQL Server 2005 sono effettuabili attraverso comandi T-SQL predisposti ad hoc,
pertanto possibile a costo zero costruire applicazioni .NET (il runtime
scaricabile gratuitamente) con il notepad o con qualsiasi editor di testo o con IDE gratuiti di sviluppo per .NET quali SharpDevelop o lo stesso Visual Studio 2005 Express, anch'esso gratuito. L'applicazione pu far
uso di SQL Server 2005 Express, che come abbiamo visto gratuito, e
gi in questa versione possiamo scrivere le nostre estensioni .NET per
37
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:03
Pagina 38
Capitolo 2
SQL Server e farle girare nella versione Express di SQL Server 2005. Useremo, poi, i comandi T-SQL di registrazione di questi oggetti per caricarli e farli eseguire nel database della nostra applicazione. E, a costo di
risultare ripetitivo, tutto ci gratuito e quindi non dovrete spendere
nemmeno un euro per avere il tutto sul vostro PC e, poi, sul PC su cui dovr girare l'applicazione. E questo anche per utilizzi commerciali: dunque cio potrete vendere regolarmente le vostre applicazioni scritte in
C# con Visual Studio C# Express e SQL Server 2005 Express.
Tuttavia, acquistando Visual Studio 2005, almeno nella versione Professional, viene offerto il supporto alla scrittura, al debugging e al deployment automatico ed integrato di oggetti .NET all'interno di SQL
Server 2005 (qualsiasi versione, anche la versione gratuita Express). In
tal caso non avrete quasi per nulla a che fare con il codice T-SQL di installazione e manutenzione dei vostri assembly e oggetti .NET in SQL Server, ma Visual Studio far tutto il lavoro. E, soprattutto, vi offrir il supporto al debugging di questo codice.
2.1 PERCH SCRIVERE

CODICE SQL SERVER IN .NET
L'approccio tradizionale allo sviluppo di applicazioni gestionali, meglio
se di classe enterprise, prevedeva il classico modello client/server, con un'applicazione client scritta, in questo caso in C# ma in generale con qualsiasi linguaggio di programmazione di alto livello dotato di un buon
IDE, e la parte server costituita dal database server. Il modello successivo che si imposto negli ultimi anni il multi-tier che prevede uno o pi
strati intermedi tra il client, che cos si snellisce e smette di mantenere
della logica applicativa che tipicamente lato-server, e il database server. In mezzo c' il middle-tier, tipicamente costituito da un application
server ospitato da un object broker (COM+), da soluzioni personalizzate (ad esempio application server auto-costruiti ospitati su servizi o applicazioni host o sul web server e raggiungibili attraverso protocollo
proprietari come il buon vecchio .NET Remoting, i web service SOAP o
38
Capitolo 2 (037-102):007-050
Capitolo 2
29-10-2007
16:03
Pagina 39
SQL SERVER
ASPETTI
AVANZATI
l'attuale WCF) o da veri e proprio ambienti server potenti, robusti ed in

grado di offrire il supporto all'hosting di applicazioni lato server. Quest'ultimo approccio tipicamente usato dall'altra met del cielo, cio dalla piattaforma Java e dalla sua estensione server J2EE. (IBM WebSphere, BEA WebLogic e ogni altra implementazione, anche gratuita degli application server J2EE).
In Microsoft mancava un approccio di quest'ultimo tipo o comunque
non era cos sviluppato. In realt IIS negli ultimi anni si evoluto per
diventare un vero e proprio host di applicazioni lato server, ma di certo
i servizi offerti da questo alle applicazioni ospitate al suo interno non sono assimilabili alla ricchezza di supporto offerto da prodotti come WebSphere o Jboss. Qualcosa del genere offerto da Sharepoint ed Exchange, due prodotti server di Microsoft, ma essi risolvono nicchie applicative specifiche e non sono prodotti generalisti.
Con SQL Server 2005 Microsoft va finalmente a colmare questa lacuna o quanto meno a gettare le basi per farlo. SQL Server 2005, ospitando pezzi di codice e di logica scritti in linguaggio non T-SQL, si pone come una prima robusto e significativa alternativa agli application server
J2EE.
fondamentale comprendere il corretto uso di questa estensione nello sviluppo delle proprie applicazioni. .NET non sostituisce T-SQL in SQL
Server, ma va a coprire ambiti applicativi differenti. Infatti scrivere query
SQL e probabilmente rester sempre il modo migliore per effettuare
interrogazioni sul database perch l'unico modo per sfruttare tutte
le ottimizzazioni previste dall'engine nelle interrogazioni. Paradossalmente, molte delle ragioni che avrebbero fatto desiderare l'uso di codice
.NET in SQL Server 2000, come l'assenza del supporto alle query ricorsive, l'accesso alle righe in una query per indice posizionale (in ADO.NET,
se vogliamo accedere alla quarta riga di un result set dobbiamo semplicemente accedere all'indice myDataTable.Rows[3]) e alcune altre limitazioni, in SQL Server 2005 sono venute meno grazie alla significativa estensione del linguaggio T-SQL della nuova versione.
Tuttavia esistono degli ambiti applicativi in cui l'uso di codice .NET alI libri di ioPROGRAMMO/SQL Server aspetti avanzati
39
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:03
Pagina 40
Capitolo 2
l'interno del database risulta molto utile. Un primo esempio banale potrebbe essere la validazione del codice fiscale in una query su una tabella di anagrafiche. Ad esempio:
SELECT CodSoggetto, Cognome, Nome, CodiceFiscale,
CodiceFiscaleOK(CodiceFiscale)
FROM Soggetti
In questo caso la funzione CodiceFiscaleOK accetta come argomento una

stringa contenente un codice fiscale e restuisce un BIT (true o false):
CREATE FUNCTION [dbo].[CodiceFiscaleOK
(
@codiceFiscale [varchar(16)
)
RETURNS [dbo].[BIT]
AS
BEGIN
--codice di validazione T-SQL
END
Scrive il codice di validazione del codice fiscale un'operazione possibile in T-SQL ma certamente non proprio banale e comunque e pi agevole se fatta, magari, in C#. SQL Server 2005 ci offre proprio quest'ultima possibilit. Ma esistono altri esempi in cui in T-SQL non proprio
possibile risolvere alcune classi di problemi. Si consideri, ad esempio,
la possibilit in una query di filtrare le righe da restituire in base al livello di autorizzazione dell'utente che invoca la query, magari attraverso
una maschera QBE dell'applicazione, e la verifica di autorizzazione riga per riga non pu essere effettuata direttamente da SQL, magari scrivendo la query in JOIN con una tabella autorizzativa del database, ma
debba essere un server esterno a validare riga per riga la tupla CodiceRiga, CodiceUtente. Vediamo un esempio semplice:
40
Capitolo 2 (037-102):007-050
Capitolo 2
29-10-2007
16:03
Pagina 41
SQL SERVER
ASPETTI
AVANZATI
SELECT CodiceRiga, DescrizioneRiga, Quantita, CodiceGruppo

FROM DatiSecretati
INNER JOIN LogCronologico
ON LogCronologico.CodiceCronologico = DatiSecretati.
CodiceCronologico
AND LogCronologico.Data <= GetDate() - 10
WHERE VerificaAutorizzazione(CodiceRiga, @CodiceUtente) = TRUE
La query presenta due condizioni di filtro. Una di essa il vincolo a non

restituire le informazioni pi vecchie di 10 giorni. Questo filtro risolto
direttamente da SQL Server attraveso una banale INNER JOIN.
Invece, l'altro filtro gestito dalla funzione VerificaAutorizzazione che,
se per il suo lavoro, avesse bisogno di accedere ad un webservice remoto o eseguire del codice .NET specifico, questa query non potrebbe essere scritta. In questo caso l'approccio tradizionale sarebbe quello di
far ritornare tutte le righe disponibili, magari gestendo nella clausola
WHERE lo scarto delle solo condizioni verificabili da database, e poi verificare applicative tutte le righe nel middle-tier o nel client scartando quelle non valide.
Questo approccio non certamente sbagliato, ma presenta degli indubbi svantaggi:
G
G
" la logica di autorizzazione e di filtro non centralizzata ma suddivisa tra database e codice applicativo;
" la restituzione dal database di righe che poi verranno comunque
scartate produce un inutile overhead prestazionale sia come occupazione di memoria per SQL Server che come occupazione di banda per la trasmissione del result set;
" potenzialmente presenta problemi di sicurezza perch eventuali dati non disponibili viaggiano comunque sulla rete.
Dal punto di vista del disegno dell'applicazione, poi, ne fa perdere compattezza e rende a pi fasi un'operazione come la validazione del dato
41
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:03
Pagina 42
Capitolo 2
che sarebbe idealmente atomica. In generale, al di la dei casi come

quello appena mostrato in cui l'uso di .NET indispensabile a meno di
non cambiare l'approccio al problema, si pu dire che, nell'ambito delle operazioni eseguibili sia con T-SQL che con .NET, le operazioni di accesso intensivo ai dati sono da preferire se sviluppati direttamente in TSQL, invece le operazioni di elaborazione intensiva sono da preferire se
sviluppati in .NET che in questo ha un indubbio vantaggio prestazionale.
In effetti scrivere stored procedure in .NET che si limitano a far eseguire query SQL al database engine non ha molto senso perch il supporto .NET in SQL Server non fornisce un modello alternativo di accesso
ai dati ma usa T-SQL per recuperarli, analogamente a quanto accade
quando di eseguire direttamente il relativo codice T-SQL. Ed per questo che nell'accesso puro ai dati T-SQL pi veloce, perch meno mediato.
altres evidente che se il codice T-SQL che viene eseguito da una singola stored procedure .NET molto intensivo e richiede molto tempo,
le differenze di prestazione rispetto alla chiamata diretta dello stesso codice T-SQL quasi si annullano perch il tempo di overhead derivante da
.NET minoritario rispetto al tempo complessivo di esecuzione. Tuttavia in questo caso l'approccio .NET resta sconsigliato perch comunque inutile usare .NET come mero passante di query T-SQL.
2.2 METODOLOGIA ALTERNATIVE

PER ESTENDERE SQL SERVER
Prima di addentrarci nei dettagli pratici dell'estensione di SQL Server
2005 con la piattaforma . NET, opportuno fare una breve digressione
nelle due metodologia alternative che permettevano di estendere il prodotto attraverso codice con T-SQL. La prima supportata direttamente
dal produttore e nasce per fare esattamente questo lavoro: stiamo parlando della possibilit di scrivere extended stored procedure in C++.
Esisteva per anche un'altra possibilit, meno ortodossa, per fare lo
42
Capitolo 2 (037-102):007-050
Capitolo 2
29-10-2007
16:03
Pagina 43
SQL SERVER
ASPETTI
AVANZATI
stesso lavoro: sfruttare la possibilit di invocare oggetti COM di automazione da T-SQL. In questo modo, costruendo un certo pattern basato su funzioni e stored procedure SQL che fungevano da proxy verso
l'oggetto COM stesso, era possibilit invocare questi oggetti esterni da
codice SQL come normali funzioni o stored procedure.
Le extended stored procedure in C++
Microsoft, gi dalle precedenti versioni SQL Server 7 e 2000, ha fornito un meccanismo per estendere le stored procedure attraverso la scrittura di nuove stored procedure non in linguaggio T-SQL, ma bens in
forma di dll binarie scritte in C++. Questa tecnologia detta Extended
Stored Procedure ed era supportata da un comodo wizard fornito dapprima con Visual C++ 6.0 e in seguito con il Visual C++ di Visual Studio .NET 2002 e 2003, dal quale possibile realizzare un esempio funzionante di extended stored procedure in pochi istanti (Figura 2.1).
Figura 2.1: Wizard di Visual Studio .NET 2003 per la creazione di

extended stored procedure in C++
Il wizard fa tutto il lavoro: prepara lo scheletro di una dll Win32, prediI libri di ioPROGRAMMO/SQL Server aspetti avanzati
43
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:03
Pagina 44
Capitolo 2
spone la funzione di esportazione richiesta dalle extended sp e prepara gi un esempio completo del metodo che implementer la logica
della stored procedure. Osserviamone lesempio basato su quello generato dal wizard e presente nei sorgenti allegati nella cartella \extended_sp_cpp:
RETCODE __declspec(dllexport) xp_proc(SRV_PROC *srvproc)
{
DBSMALLINT i = 0;
DBCHAR colname[MAXCOLNAME];
DBCHAR spName[MAXNAME];
DBCHAR spText[MAXTEXT];
// Name of this procedure
_snprintf(spName, MAXNAME, "xp_proc");
//Set up the column names
_snprintf(colname, MAXCOLNAME, "ID");
srv_describe(srvproc, 1, colname, SRV_NULLTERM, SRVINT2,
sizeof(DBSMALLINT), SRVINT2, sizeof(DBSMALLINT), 0);
_snprintf(colname, MAXCOLNAME, "spName");
// Update field 2 "spName", same value for all rows
srv_setcoldata(srvproc, 2, spName);
srv_setcollen(srvproc, 2, static_cast<int>(strlen(spName)));
// Send multiple rows of data
for (i = 0; i < 3; i++) {
// Update field 1 "ID"
srv_setcoldata(srvproc, 1, &i);
srv_setcoldata(srvproc, 3, spText);
srv_setcollen(srvproc, 3, static_cast<int>(strlen(spText)));
44
Capitolo 2 (037-102):007-050
Capitolo 2
29-10-2007
16:03
Pagina 45
SQL SERVER
ASPETTI
AVANZATI
// Send the entire row

srv_sendrow(srvproc);
}
// Now return the number of rows processed
srv_senddone(srvproc, SRV_DONE_MORE | SRV_DONE_COUNT,
(DBUSMALLINT)0, (DBINT)i);
return XP_NOERROR ;
}
Certo, con pochi clic un esempio funzionante gi pronto ed sufficiente copiare la dll generata nella directory \Binn di SQL Server ed eseguire la registrazione della nuova stored procedure in SQL Server con il comando:
sp_addextendedproc 'nome_storedprocedure', 'nomelibreria.DLL'
Si cos finalmente pronti ad utilizzare la nuova stored procedure, come mostrato in Figura 2.2.
Figura 2.2: Lextended stored procedure in azione

45
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:03
Pagina 46
Capitolo 2
Daltro canto, dalla produzione dellesempio funzionante proposto in automatico dal template del wizard alla realizzazione di una propria stored procedure da zero in C++ il passo non cos breve. Se poi si considera che non tutti conoscono il C++ o vogliono avere a che fare con
il livello di complessit di una soluzione del genere, questa soluzione presenta una complessit elevata.
2.2.1 Chiamate ad oggetti COM da T-SQL

Il database Microsoft SQL Server, gi dalle versioni 7.0 e 2000 offre la
possibilit di effettuare chiamate COM ad oggetti che supportano linterfaccia di automazione. A queste funzioni COM, via T-SQL, possibile passare valori scalari (numeri, stringhe, date e quantaltro ma non
interi record) ed possibile ottenere come risultato un valore scalare.
sostanzialmente quanto si pu fare anche con le extended stored procedure, ma da queste possibile ottenere al ritorno anche record come
per le stored procedure tradizionali. Questa limitazione delle chiamate
COM potrebbe sembrare rilevante, ma non poi cos grave. Infatti abbiamo osservato in precedenza in questo capitolo quali siano gli ambiti migliori di utilizzo delle estensioni non SQL.
Figura 2.3: La nostra classe Visual Basic 6
46
Capitolo 2 (037-102):007-050
29-10-2007
Capitolo 2
16:03
Pagina 47
SQL SERVER
ASPETTI
AVANZATI
Ma arrivato il momento di procedere con un esempio reale. In T-SQL

certamente possibile effettuare chiamate COM ad oggetti gi presenti nel sistema, ma altres vero che si pu pensare di scrivere propri oggetti COM che verranno richiamati da T-SQL. Ed quello che faremo scrivendo una semplice DLL COM in Visual Basic 6 (Figura 2.3).
Si tratta della complessissima classe SampleClass definita nella libreria SQLServerDemo e che pertanto definita dal ProgId
SQLServerDemo.SampleClass. Essa espone il metodo PrintName che
accetta in ingresso due parametri number (di tipo intero) e prefix di tipo stringa e restituisce una stringa cos composta: prefix & " " & number & " ss ":
Public Function PrintName(ByVal number As Integer, _
ByVal prefix As String) As String
PrintName = prefix & " " & number & " ss "
End Function
Gli esempi di codice possono essere pi o meno intelligenti e questo

lo di sicuro ma sicuramente sufficiente al nostro scopo: mostrare linterazione da T-SQL. La prima operazione da effetture la creazione dellistanza delloggetto COM. Eccone la sintassi:
sp_OACreate progid, | clsid,
token_oggetto OUTPUT [ , contesto

( 1= inprocess, 2=locale(.exe) ]
Osserviamo un estratto di codice T-SQL:

declare @ServerID INT
--identificativo dellistanza
declare @OLEResult int
--Hresult della chiamata
declare @errorSource INT --numero di errore

4
www.ws-i.org
http://www.ws-i.org/Profiles/BasicProfile-1.1.html
47
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:03
Pagina 48
Capitolo 2
declare @errorDescription varchar(220) --descrizione dellerrore

EXEC @OLEResult = sp_OACreate 'SQLServerDemo.SampleClass',
@ServerID OUT
if @OLEResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @ServerID, @errorSource OUTPUT,
@errorDescription OUTPUT
END
In @ServerID finir il riferimento allistanza appena creata con la funzione di sistema sp_OACreate e verr utilizzato per linvocazione dei
metodi dellistanza. @OLEResult, invece, conterr il valore HResult della chiamata, infatti, in caso di valore diverso da 0, e quindi di errore,
sar possibile chiamare la funzione sp_OAGetErrorInfo che restituisce
proprio il numero e la descrizione precisa dellerrore:
sp_OAGetErrorInfo [ token_oggetto ]
[ , source OUTPUT ]
[,
description OUTPUT ]
[ , helpfile OUTPUT ]
[ , helpid OUTPUT ]
Queste informazioni finiranno nelle variabili @erroreSource ed @errorDescription Naturalmente questa eccezione pu anche essere prodotta applicativamente allinterno del nostro codice Visual Basic 6. Se listanziazione andata a buon fine, possiamo effettuare la chiamata al
metodo PrintName della nostra dll con la seguente sintassi:
sp_OAMethod token_oggetto,
nome_metodo
[ , valore_ritorno
OUTPUT ]
[ , [ @nome_parametro = ] valore_parametro [ OUTPUT ]
[ ...n ] ]
Ed ecco come effettuare la chiamata al nostro metodo PrintName:

declare @prefixToPrint INT --prefisso da passare alla funzione Visual Basic
declare @number int
--numero da passare alla funzione Visual Basic
declare @retString varchar(8000 --variabile che conterr il valore
48
Capitolo 2 (037-102):007-050
Capitolo 2
29-10-2007
16:03
Pagina 49
SQL SERVER
ASPETTI
AVANZATI
di ritorno della funzione

set @prefixToPrint = prova
set @number = 15
--esecuzione della chiamata al metodo PrintName della classe Visual Basic
di esempio
EXEC @OLEResult = sp_OAMethod @ServerID, 'PrintName', @retString
OUTPUT, @number = @numToPrint, @prefix = @prefixToPrint
print @retString --il risultato atteso sar: prova 15 ss
Abbiamo dichiarato le due variabili che conterranno i valori da passare al metodo PrintName, essere veranno passate con la sintassi @nomeParametro = valore, dove @nomeParametro proprio il nome del parametro cos come definito nella classe Visual Basic 6. Per il valore di
ritorno, invece, sufficiente aggiungere il postfisso OUTPUT. Si noti che
non necessario passare i parametri nellordine previsto dalla funzione da chiamare perch la convenzione di passaggio basata sui nomi
e non sulla posizione. lequivante di usare la seguente sintassi cara ai
programmatori Visual Basic:
Dim o As Object
Set o = CreateObject("SQLServerDemo.SampleClass")
Dim value as String
value = o.PrintName( prefix:= "ciro", number:= 544 )
Tornando alla nostra chiamata da T-SQL, se non si sono verificati errori, comunque tracciabili da una successiva chiamata a sp_OAGetErrorInfo, possibile leggere o stampare il valore di ritorno della funzione
VB6. Terminata la chiamata alla classe, non ci resta che rilasciare correttamente le risorse (listanza stessa della classe) al fine di evitare fastidiosi memory leak. Di questo si occupa unaltra stored procedure di
49
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:03
Pagina 50
Capitolo 2
sistema, la sp_OADestroy:
sp_OADestroy objecttoken
Ed eccone luso nel nostro esempio reale:

EXEC @OLEResult = sp_OADestroy @ServerID
Per effetto della location transparency, possiamo senza nessun problema effettura il debug della classe Visual Basic 6 direttamente dalla chiamata T-SQL. Sar suffciente aprire il sorgente del progetto della DLL
dallIDE di Visual Basic 6, mettere lIDE in run con lopzione Wait for
components to be created, impostare un breakpoint allinizio del metodo PrintName, effettuare la chiamata della funzione da T-SQL e attendere che Visual Basic 6 si attivi con listruzione col brealpoint i bella mostra...
Sebbene il meccanismo fin qui mostrato sia realmente molto potente e
permetta di ottenere risultati straordinari con poco sforzo, potrebbe risultare un po macchinoso data la complessit della sintassi prevista. Con
lapproccio alla C++ la chiamata ad una extended stored procedure
indistinguibile dalla chiamata ad una stored procedure tradizionale. E
allora perch non provare a semplificarsi la vita?
In SQL Server prevista la possibilit di scrivere delle proprie funzioni richiamabili da query, stored procedure e, pi in generale, da T-SQL. Ecco un esempio di user function che esegue la somma di due numeri:
CREATE FUNCTION dbo.MySum
(
@num1 float,
@num2 float
)
RETURNS float
AS
50
Capitolo 2 (037-102):007-050
Capitolo 2
29-10-2007
16:03
Pagina 51
SQL SERVER
ASPETTI
AVANZATI
BEGIN
return @num1 + @num2
END
Dovremo creare questa funzione allinterno di un database SQL Server,

dopo di che ne sar possibile la chiamata:
print dbo.MySum(2.3, 5.9)
--il risultato atteso : 8.2
Potremo anche chiamare la funzione da una query:

select Campo1, dbo.MySum(Campo2, Campo3) as Somma from Tabella
E allora perch non creare una funzione che incapsuli la chiamata alla nostra PrintName? Detto fatto:
CREATE FUNCTION dbo.GetNumber
(
@numToPrint int,
@prefixToPrint varchar(255)
)
RETURNS varchar(255)
AS
BEGIN
declare @OLEResult int,
@ServerID INT,
@retString varchar(8000),
@errorSource INT,
@errorDescription varchar(220)
EXEC @OLEResult = sp_OACreate 'SQLServerDemo.SampleClass',

51
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:03
Pagina 52
Capitolo 2
@ServerID OUT
if @OLEResult<>0
BEGIN
RETURN -1
END
EXEC @OLEResult = sp_OAMethod @ServerID, 'PrintName',
@retString OUTPUT, @number = @numToPrint, @prefix = @prefixToPrint
IF (@OLEResult <> 0)
BEGIN
RETURN @errorDescription
END
ELSE
BEGIN
RETURN @retString
END
EXEC @OLEResult = sp_OADestroy @ServerID
IF @OLEResult <> 0
BEGIN
RETURN @errorDescription
END
RETURN @retString
END
52
Capitolo 2 (037-102):007-050
29-10-2007
Capitolo 2
16:03
Pagina 53
SQL SERVER
ASPETTI
AVANZATI
Questa funzione cos come tutti gli altri esempi fin qui mostrati, stata creata allinterno di una versione modificata del database Northwind
di SQL Server. Un backup di questa versione accompagna i sorgenti allegati. Pertanto tutte le prove verranno effettuate puntando a questo database. Ed ecco, infatti, unesempio di chiamata alla nostra funzione in
una query:
select OrderID, CustomerID, dbo.GetNumber(OrderID, 's') as CampoCOM
from orders
Beh, tuttaltra cosa rispetto alla complessit vista in precedenza. Naturalmente la chiamata a questa funzione potr essere incapsulata in
unaltra funzione:
CREATE FUNCTION dbo.GetNumberProxy
(
@numToPrint int,
@prefixToPrint varchar(255)
)
RETURNS varchar(255)
AS
BEGIN
RETURN dbo.GetNumber(@numToPrint, 'Proxy ' + @prefixToPrint)
END
O in una stored procedure, colmando finalmente il gap rispetto alle
estendend sp:
CREATE PROCEDURE GetOrdersEx
@MinimunFreight int = 0
AS
select orderid, shipname, freight, dbo.GetNumberProxy(orderid, shipname)
53
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:03
Pagina 54
Capitolo 2
as CampoFunzione
from orders where freight >= @MinimunFreight
GO
2.2.2 Vantaggi dell'approccio .NET

rispetto alle due precedenti tecnologie
Il primo e pi importante vantaggio dell'approccio .NET rispetto alle
due soluzioni che abbiamo appena visto, oltre alla maggiore semplicit, almeno rispetto all'approccio C++ e alla minore macchinosit rispetto alla soluzione Visual Basic 6, certamente la sicurezza: il codice .NET codice gestito e, come vedremo in seguito, quando si registrata la stored procedure o la funzione .NET in SQL Server possibile
autorizzarne il livello di sicurezza, che pu esser SAFE, EXTERNAL_ACCESS, o UNSAFE. Impostando la massima restrizione (SAFE), sar impossibile che il codice .NET chiamato da SQL acceda a risorse esterne (ad
esempio il file system o le connessioni di rete) o che faccia altre operazioni extra database, se non siamo noi ad autorizzarle assegnando all'oggetto .NET un livello pi alto. N l'approccio C++, n l'approccio VB6
offrono questo indubbio vantaggio. Con EXTERNAL_ACCESS si pu sostanzialmente accedere a qualsiasi risorse interna ed esterna al database, ma non consentita l'esecuzione di codice unmanaged, opzione
invece consentita al livello UNSAFE.
In C++ poi necessario aprire una connessione esplicita al database per
accedere ai dati, nonostante la procedura giri all'interno del database
stesso e questo porta ad un evidente overhead prestazionale, oltre che
ad una robustezza complessiva maggiore. Inoltre SQL Server in grado di controllare il livello di utilizzo delle risorse (memoria e CPU) da
parte di una stored procedure .NET invocata, ed eventualmente ridurne o bloccarne l'utilizzo, ma non pu fare lo stesso con le XP C++ o
con le chiamate COM.
Con .NET, poi, possibile definire tipi di dati aggiuntivi che sono implementabili direttamente come classi . NET.
Anche il presunto vantaggio prestazionale del codice compilato C++,
54
Capitolo 2 (037-102):007-050
Capitolo 2
29-10-2007
16:03
Pagina 55
SQL SERVER
ASPETTI
AVANZATI
o persino del compilato VB6, che produce un codice binario X86 Win32
di buon livello, rispetto al codice eseguito dalla macchina virtuale .NET,
nei fatti si dimostra non vero, sia perch il Jitter (Just In Time Compiler)
di .NET fa un ottimo lavoro e sia perch l'eventuale vantaggio residuale azzerato completamente dall'overhead di accesso a SQL Server all'interno di codice XP C++ e COM rispetto alla modalit meno mediata possibile con .NET.
2.3 IL SUPPORTO .NET 2.0

PER SQL SERVER 2005
Visual Studio 2005 offre il supporto integrato alla scrittura, al debugging
e al test di stored procedure, funzioni e trigger .NET in SQL Server 2005.
In realt possibile anche scrivere componenti CLR per SQL Server 2005
munendosi di SQL Server 2005 Express, Microsoft .NET Framework SDK
e, opzionalmente, di Microsoft Visual C# Express 2005, il tutto a costo
zero, ma perdendo la possibilit di effettuare un deployment ultra semplificato e, soprattutto, la possibilit di eseguire il debugging del codice CLR in esecuzione su SQL Server.
Pertanto accenneremo soltanto a questa possibilit ma ci concentreremo sul comodo ambiente a pagamento di Visual Studio 2005 Professional.
2.3.1 Preparazione del database

di esempio
Piuttosto che descrivere teoricamente il tutto, procederemo con la creazione di un piccolo database di anagrafiche soggetti in cui applicare il
supporto CLR per effettuare la verifica e il calcolo del Codice Fiscale,
che sempre un cavallo di battaglia degli articolisti sviluppatori italiani.
Innanzitutto predisponiamo la creazione di un nuovo database Libro
e di un paio di tabelle Soggetti e Localita in relazione tra loro (la localit di nascita del soggetto in foreign key con la tabella delle localit).
55
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:03
Pagina 56
Capitolo 2
Ecco dunque gli script di creazione commentati:

USE master
GO
--creazione del database di esempio
CREATE DATABASE [Libro]
GO
--creazione della tabelle delle localit: conterr una coppia di valori
NomeLocalit, CodiceBelfiore
--necessaria al calcolo del Codice Fiscale
CREATE TABLE [dbo].[Localita](
[Localita] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[CodiceBelfiore] [nchar](4) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_Localita_1] PRIMARY KEY CLUSTERED
(
[Localita] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--creazione della tabella dei soggetti: contiene le informazioni di base per il
calcolo del codice fiscale e
--quindi Cognome, Nome, Sesso, Data di nascita, Localit di nascita
CREATE TABLE [dbo].[Soggetti](
[IdSoggetto] [int] IDENTITY(1,1) NOT NULL,
[Cognome] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[Nome] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[LocalitaNascita] [nvarchar](50) COLLATE Latin1_General_CI_AS
NULL,
[CodiceFiscale] [varchar](16) COLLATE Latin1_General_CI_AS NULL,
[Sesso] [nchar](1) COLLATE Latin1_General_CI_AS NULL,
[DataNascita] [datetime] NULL,
56
Capitolo 2 (037-102):007-050
29-10-2007
Capitolo 2
16:03
Pagina 57
SQL SERVER
ASPETTI
AVANZATI
CONSTRAINT [PK_Soggetti] PRIMARY KEY CLUSTERED

(
[IdSoggetto] ASC
) ON [PRIMARY]
GO
--la localit di nascita in foreign key con la tabella delle localit
ALTER TABLE [dbo].[Soggetti] WITH CHECK ADD CONSTRAINT
[FK_Soggetti_Localita] FOREIGN KEY([LocalitaNascita])
REFERENCES [dbo].[Localita] ([Localita])
GO
ALTER TABLE [dbo].[Soggetti] CHECK CONSTRAINT [FK_Soggetti_Localita]
GO
--il campo Sesso, di tipo NCHAR(1), pu contenere solo i valori 'M' o 'S' e
pertanto viene
--introdotto un vincolo Check Constraint per accertarsi della consistenza dei
dati inseriti
ALTER TABLE [dbo].[Soggetti] WITH CHECK ADD CONSTRAINT
[CK_Soggetti] CHECK (([Sesso]='F' OR [Sesso]='M'))
GO
ALTER TABLE [dbo].[Soggetti] CHECK CONSTRAINT [CK_Soggetti]
GO
Al fine di garantirci la possibilit di effettuare alcune prove, inseriamo

alcune localit di esempio nella tabella Localita con il relativo Codice Belfiore:
INSERT INTO Localita
VALUES (N'Bari', N'A662')
VALUES (N'Foligno', N'D653')
57
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:03
Pagina 58
Capitolo 2

VALUES (N'Verona', N'A737')
GO
Facciamo altrettanto con i soggetti inserendone alcuni. Siccome lo scopo dell'esempio e mostrare un esempio d'uso del CLR .NET all'interno
di SQL Server 2005 per verificare e calcolare il codice fiscale, operazione tipicamente effettuata nel codice di backend o di frontend dell'applicazione, ma difficilmente realizzabile direttamente in codice T-SQL, alcuni dei soggetti che inseriremo avranno il codice fiscale errato:
INSERT INTO Soggetti
(Cognome, Nome, LocalitaNascita, CodiceFiscale, Sesso, DataNascita)
VALUES ('Vessia', 'Vito', N'Bari', 'VSSVSI74M30A662W', N'M', '1974-0830 00:00:00.000')
VALUES ('Rallo', 'Daniela', N'Verona', 'RLLDNL74D49L781Q', N'F', '197404-09 00:00:00.000')
VALUES ('Rossi', 'Mario', N'Bari', 'RSSMRA35A01A662T', N'M', '1935-0101 00:00:00.000')
VALUES ('Esposito', 'Cira', N'Foligno', 'SPSCRI70E59D653J', N'F', '197005-19 00:00:00.000')
INSERT INTO dbo.Soggetti
VALUES ('Vessia', 'Antonio', N'Bari', 'VSSNTN79S23A662X', N'M', '197911-23 00:00:00.000')
INSERT INTO dbo.Soggetti
VALUES ('Armani', 'Federica', N'Verona', 'RMNFRC79S50L781F', N'F',
58
Capitolo 2 (037-102):007-050
Capitolo 2
29-10-2007
16:03
Pagina 59
SQL SERVER
ASPETTI
AVANZATI
'1979-11-10 00:00:00.000')
GO
L'ultima operazione da effettuare in preparazione all'esempio, proprio l'attivazione del supporto al CLR. Di default i database SQL Server
2005 hanno questo supporto disattivato e quindi va attivato esplicitamente con la seguente sequenza di stored procedure di sistema:
sp_configure "CLR ENABLED", 1
GO
reconfigure
GO
L'eventuale disattivazione si effettua lanciando la stessa sequenza di comandi ma con il parametro CLR ENABLED impostato a 0.
2.3.2 Il progetto in Visual Studio 2005

Tutti i nostri esempi saranno contenuti in un unico progetto Visual Stu-
Figura 2.4: Il nuovo progetto CLR SQL Server 2005 da Visual Studio 2005
59
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:03
Pagina 60
Capitolo 2
dio 2005 scritto in C#. Dunque procediamo alla sua creazione. Dopo
aver aperto l'IDE, creiamo un nuovo progetto Visual C# > Database
> SQL Server Project (Figura 2.4) che chiameremo CLREsempio.
A questo punto, se non presente nessun'altra connessione al database tra quelle conservate nell'IDE, verr proposta la creazione di una
Figura 2.5: La connessione a SQL Server 2005
nuova connessione a SQL Server 2005, come mostrato in Figura 2.5.
Figura 2.6: Gestione del database da Visual Studio 2005
60
Capitolo 2 (037-102):007-050
29-10-2007
Capitolo 2
16:04
Pagina 61
SQL SERVER
ASPETTI
AVANZATI
Da questo momento, nel pannello Esplora server di Visual Studio 2005

(Figura 2.6) potremo visualizzare, gestire e modificare il nostro database di esempio quasi analogamente a quanto facciamo da SQL Server
Management Studio.
Il nuovo progetto si presenter vuoto e senza file di codice sorgente
C#. Sar presente la sola cartella Test Scripts, contenente un file .SQL di
script T-SQL di esempio, che esamineremo nel proseguo. Sono gi presenti tutti i riferimenti necessari al progetto e probabilmente vi stupir
sapere che si compongono dei soli assembly mscorelib.dll, System.dll e
System.Data.Dll, cio esattamente gli stessi che usate nella quasi totalit di applicazione .NET che scrivete. Il trucco consiste nel fatto che l'intero supporto alle estensioni CLR per SQL Server 2005 contenuto nello stesso assembly di ADO.NET a cui stato aggiunto un intero nuovo
namespace Microsoft.SqlServer.Server, nella versione 2.0 del Microsoft
.NET Framework. Dunque avete gi tutto il supporto necessario senza
dover nemmeno installare SQL Server 2005!
2.4 FUNZIONI IN CLR

Siamo pronti ad aggiungere la nostra prima estensione CLR nel database di esempio. Selezionando il progetto appena creato dal pannello
Figura 2.7: Aggiunta di un oggetto al progetto

61
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
31-10-2007
12:49
Pagina 62
Capitolo 2
Solution Explorer e attivando il men contestuale con il tasto destro del

mouse, sceglieremo la voce Add > Funzione definita dall'utente. Visual Studio provveder ad aggiungere un nuovo file .CS al progetto e a
produrre lo scheletro di una funzione CLR richiamabile da SQL Server 2005
(Figura 2.7).
La nostra prima funzione si chiamer ControlloCodiceFiscale, accetter
come argomento una stringa codiceFiscale e ritorner un boolean per
affermare la bont o meno del codice fiscale; in pratica, per quei due o
tre lettori che hanno vissuto finora in Canada e che non sanno quale algoritmo si cela dietro il calcolo del codice fiscale italiano, cerchiamo di
riepilogarlo brevemente. Esso costituito da una stringa alfanumerica
di 16 caratteri, univoca per ciascun cittadino italiano (l'Agenzia delle
Entrate gestisce le omonime generando codici fiscali fuori algoritmo ed
questa la ragione per cui l'unica vero codice fiscale quello rilasciato
da loro e con quello auto-calcolato non vi la certezza dell'assoluta
bont). La descrizione dell'algoritmo verr omessa perch, come potrete ben figurarvi, non di certo argomento di questo volume. Per i pi curiosi, per, esiste sempre il metodo C# CalcolaCodiceFiscale lo implementa e che dunque un buon punto di partenza. La stringa si compone come:
CCC NNN AA M DD BBBB X
CCC contiene tre lettere del cognome, NNN contiene tre lettere del nome, AA l'anno di nascita, M la lettera corrisponde al mese di nascita, secondo una tabella di decodifica specifica dell'algoritmo, DD il
giorno di nascita (per le donne a DD si somma 40 e quindi DD ha la
doppia valenza di indicare il giorno di nascita e il sesso), BBBB il codice belfiore del comune di nascita, una codifica per tutti i comuni italiani e i principali stati mondiali per coloro che non sono nati in Italia, X
rappresenta il codice di controllo e sta ad indicare la corretta consistenza sintattica del codice per segnalare eventuali errori di trascrizione del
codice stesso. Ed proprio su questo codice di controllo che si baser la
62
Capitolo 2 (037-102):007-050
29-10-2007
Capitolo 2
16:04
Pagina 63
SQL SERVER
ASPETTI
AVANZATI
nostra prima funzione SQL Server 2005 CLR scritta in C#. Osserviamone il sorgente:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;
using System.Collections;
using Microsoft.SqlServer.Server;
namespace CLREsempio
{
public partial class Controllo
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean ControlloCodiceFiscale(string codiceFiscale)
{
return new
SqlBoolean(PartiComuni.VerificaCodiceFiscale(codiceFiscale));
}
}
}
La funzione statica ed inserita nella classe Controllo. Sulla funzione

definito l'attributo Microsoft.SqlServer.Server.SqlFunction che indica proprio
che si comporter come una funzione di SQL Server 2005. I tipi standard
di .NET sono comunque utilizzabili nella definizione della firma del metodo (argomenti e valore di ritorno), ma da preferire l'uso dei tipi specifici di SQL Server. Infatti il valore di ritorno boolean della funzione espresso con un SqlBoolean. Il corpo della funzione minimale perch contiene solo la chiamata ad un altro metodo statico (VerificaCodiceFiscale)
63
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:04
Pagina 64
Capitolo 2
contenuto in una classe costituita da parti di logica comune alle varie estensioni CLR di esempio che introdurremo in questo capitolo. Dunque riportiamo il codice di questa funzione statica:
using System;
using System.Data;
using System.Text.RegularExpressions;
using System.Collections;
using System.Collections.Generic;
{
class PartiComuni
{
private static readonly Hashtable pari = new Hashtable();
private static readonly Hashtable dispari = new Hashtable();
private static readonly Hashtable controllo = new Hashtable();
private static readonly Regex reConsonanti =
new Regex("b|c|d|f|g|h|j|k|l|m|n|p|q|r|s|t|v|w|x|y|z",
RegexOptions.IgnoreCase);
private static readonly Regex reVocali =
new Regex("a|e|i|o|u", RegexOptions.IgnoreCase);
private static readonly Regex reLettere =
new Regex("a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|w|x|y|z",
RegexOptions.IgnoreCase);
private static readonly Regex reCifre =
new Regex("\\d", RegexOptions.IgnoreCase);
private static readonly char[] mesi =
{ 'A', 'B', 'C', 'D', 'E', 'H', 'L', 'M', 'P', 'R', 'S', 'T' };
64
Capitolo 2 (037-102):007-050
29-10-2007
Capitolo 2
16:04
Pagina 65
SQL SERVER
ASPETTI
AVANZATI
internal static void initializeValues()

{
if (pari.Count > 0) return;
pari.Add('0', 0);
pari.Add('1', 1);
pari.Add('2', 2);
pari.Add('4', 4);
pari.Add('5', 5);
pari.Add('6', 6);
pari.Add('8', 8);
pari.Add('9', 9);
pari.Add('A', 0);
pari.Add('C', 2);
pari.Add('D', 3);
pari.Add('E', 4);
pari.Add('G', 6);
pari.Add('H', 7);
pari.Add('I', 8);
pari.Add('K', 10);
pari.Add('L', 11);
pari.Add('M', 12);
pari.Add('O', 14);
pari.Add('P', 15);
pari.Add('Q', 16);
pari.Add('S', 18);
pari.Add('T', 19);
pari.Add('U', 20);
pari.Add('W', 22);
pari.Add('X', 23);
pari.Add('Y', 24);
pari.Add('3', 3);
pari.Add('7', 7);
pari.Add('B', 1);
pari.Add('F', 5);
pari.Add('J', 9);
pari.Add('N', 13);
pari.Add('R', 17);
pari.Add('V', 21)
pari.Add('Z', 25);
dispari.Add('0', 1);
5);
15);
1);
dispari.Add('C', 5);
dispari.Add('D', 7);
9);
dispari.Add('G', 15);
dispari.Add('H', 17);
19);
dispari.Add('2',
dispari.Add('6',
dispari.Add('A',
dispari.Add('B', 0);
dispari.Add('E',
dispari.Add('F', 13);
dispari.Add('I',
dispari.Add('J', 21);
65
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:04
Pagina 66
Capitolo 2
dispari.Add('K', 2);
dispari.Add('L', 4);
dispari.Add('O', 11);
dispari.Add('P', 3);
dispari.Add('M',
18);
dispari.Add('N', 20);
dispari.Add('Q',
6);
dispari.Add('R', 8);
dispari.Add('S', 12);
dispari.Add('T', 14);
dispari.Add('U',
dispari.Add('W', 22);
dispari.Add('X', 25);
16);
24);
controllo.Add(0, 'A');
controllo.Add(19, 'T');
controllo.Add(21, 'V');
controllo.Add(22, 'W');
controllo.Add(24, 'Y');
controllo.Add(15, 'P');
controllo.Add(17, 'R');
controllo.Add(18, 'S');
controllo.Add(20, 'U');
controllo.Add(11, 'L');
controllo.Add(13, 'N');
controllo.Add(14, 'O');
controllo.Add(16, 'Q');
controllo.Add(7, 'H');
controllo.Add(9, 'J');
controllo.Add(10, 'K');
controllo.Add(12, 'M');
controllo.Add(3, 'D');
controllo.Add(5, 'F');
controllo.Add(6, 'G');
controllo.Add(8, 'I');
dispari.Add('Y',
dispari.Add('Z', 23);
controllo.Add(1, 'B');
controllo.Add(2, 'C');
controllo.Add(4, 'E');
dispari.Add('V', 10);
controllo.Add(23, 'X');
controllo.Add(25, 'Z');
}
internal static bool VerificaCodiceFiscale(string codFiscale)
{
initializeValues();
string codfisc = codFiscale.ToUpper();
int codicecontrollo = 0;
try
{
66
Capitolo 2 (037-102):007-050
29-10-2007
Capitolo 2
16:04
Pagina 67
SQL SERVER
ASPETTI
AVANZATI
if (codfisc.Length == 16)
{
for (int i = 0; i < 15; i++)
{
if ((i + 1) % 2 == 0)
codicecontrollo = codicecontrollo + (int)pari[codfisc[i]];
else
codicecontrollo = codicecontrollo + (int)dispari[codfisc[i]];
}
if ((char)controllo[(codicecontrollo % 26)] != codfisc[15])
return false;
else
return true;
}
else
return false;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
}
Siamo dunque pronti a compilare, fare l'upload dell'assembly su SQL

Server, registrare la funzione e testare il codice della stessa direttamente da codice T-SQL. Semplicemente selezionando il progetto dal
Solution Explorer, attivando il men contestuale e scegliendo la voce Distribuisci, effettueremo le prime tre operazioni (compilazione, upload
dell'assembly e registrazione della funzione). In realt le effettuer
in automatico per noi Visual Studio 2005 che dispone di una connesI libri di ioPROGRAMMO/SQL Server aspetti avanzati
67
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:04
Pagina 68
Capitolo 2
sione diretta al nostro database impostata nelle fasi iniziali, come

ricorderete.
2.4.1 Registrazione dell'assembly e della

funzione senza Visual Studio 2005
Professional
Se, invece, non si disponesse di Visual Studio 2005 Professional, bisognerebbe compilare il progetto (a riga di comando direttamente con il compilatore CSC oppure, magari, con Visual C# Express 2005) e, supponendo che l'assembly si chiami CLREsempio.dll e che si trovi in C:\, lanceremo il nuovo comando CREATE ASSEMBLY:
CREATE ASSEMBLY CLR2005
FROM 'C:\CLREsempio.dll'
WITH PERMISSION_SET=SAFE
Riconoscerete il PERMISSION_SET che possibile impostare a SAFE,

EXTERNAL_ACCESS e UNSAFE, come descritto in precedenza. A questo
punto possiamo registrare la nuova funzione in SQL Server definendo una
firma esterna in codice T-SQL in modo che possa essere usata da codice T-SQL:
CREATE FUNCTION [dbo].[ControlloCodiceFiscale](@codiceFiscale
[nvarchar](4000))
RETURNS [bit] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME
[CLREsempio].[CLREsempio.Controllo].[ControlloCodiceFiscale]
GO
2.4.2 Test della funzione

Possiamo utilizzare la funzione all'interno di normale codice T-SQL come quello della seguente query:
68
Capitolo 2 (037-102):007-050
Capitolo 2
29-10-2007
16:04
Pagina 69
SQL SERVER
ASPETTI
AVANZATI
SELECT IdSoggetto, Cognome, Nome, CodiceFiscale,

dbo.ControlloCodiceFiscale(CodiceFiscale) CodiceFiscaleOK
FROM Soggetti
La funzione usata per restituire un campo nella clausola SELECT, analogamente a quanto facciamo con le funzioni native. In Figura 2.8
possibile osservare il resultset della query di esempio. Il campo Codice-
Figura 2.8: La query che richiama la funzione CLR
FiscaleOK contiene i valori 0 (codice fiscale erratto) o 1 (codice fiscale corretto). In effetti, tra i record inseriti con le INSERT iniziali, vi un soggetto con codice fiscale errato.
2.4.3 Debug della funzione

Noi programmatori moderni siamo sempre meno capaci, ahinoi, di scrivere codice senza il debugger. La cosa non tanto grave in s per ragioni etiche, deontologiche o estetiche, perch tutto sommato i debugger
sono comodi e sono fatti a posta per semplificarci la vita, ma perch
l'indice dell'incapacit di figurarsi il flusso di esecuzione del codice senza prima eseguirlo step by step. Ma siccome le questioni filosofiche sono off topic esattamente come lo l'algoritmo del codice fiscale, tralaI libri di ioPROGRAMMO/SQL Server aspetti avanzati
69
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:04
Pagina 70
Capitolo 2
sciamo queste considerazioni per sottolineare, invece, come il supporto integrato offerto da Visual Studio 2005 Professional allo sviluppo di
oggetti CLR per SQL Server 2005 contempla pure un potente debugger. In pratica SQL Server esegue il vostro codice CLR all'interno della sua
macchina virtuale .NET e voi potete effettuarne il debugging dal vostro
comodo IDE.
Come fare? Il file Test.sql contenuto nella cartella Test Scripts del progetto ci viene incontro. Infatti, riportando al suo interno il codice T-SQL in
grado di richiamare la nostra nuova funzione (ad esempio la query precedente) e semplicemente impostando un breakpoint all'ingresso della funzione C#, con la semplice pressione del tasto F5, che avvia l'esecuzione dei progetti in Visual Studio, l'IDE eseguira il codice T-SQL in
Figura 2.9: A kind of managed magic
script.sql, che conterr l'attivazione della funzione e che quindi potr

essere seguita in debug dall'IDE, analogamente a quanto avviene con
le applicazioni desktop o web tradizionali. In Figura 2.9 si mostra la magia...
70
Capitolo 2 (037-102):007-050
29-10-2007
Capitolo 2
16:04
Pagina 71
SQL SERVER
ASPETTI
AVANZATI
2.5 STORED PROCEDURE IN CLR

La prima funzione era volutamente semplice e non faceva uso di risorse del database o esterne perch tutto ci che le serviva era contenuto
nell'argomento ad essa passato. La scrittura di una stored procedure
CLR un'operazione altrettanto semplice e apparirebbe ripetitiva, pertanto facendolo ne approfitteremo per introdurre un maggiore livello di
complessit. Selezionando il progetto appena creato dal pannello Solution Explorer e attivando il men contestuale con il tasto destro del
mouse, sceglieremo la voce Add > Stored Proceure. La nostra stored
procedure si chiama ScomponiCodiceFiscale, accetta un parametro IdSoggetto e restituisce un resultset contenente tutti i campi della tabella Soggetti, eventualmente filtrati secondo la condizione IdSoggetto =
@IdSoggetto OR @IdSoggetto = -1, pi due nuovi campi calcolati:
G
G
" CodiceFiscaleCalcolato (il codice fiscale ricalcolato a partire dai

campi Cognome, Nome, Sesso, DataNascita e LocalitaNascita)
" Corretto (un campo boolean che indica se il valore ricalcolato corrisponde al codice fiscale memorizzato in tabella).
Dunque, nella stored procedure dovremo:

G
G
G
G
" recuperare tutte le righe di Soggetti secondo la condizione di filtro indicata dall'argomento IdSoggetto;
" per ciascun riga eseguire il calcolo del codice fiscale a partire dai
campi Cognome, Nome, Sesso, DataNascita e LocalitaNascita;
" verificare se il codice fiscale ricalcolato corrisponda a quello fisicamente salvato nella riga;
" restituire l'intero resultset costituito dai campi della tabella pi i due
nuovi calcolati.
L'operazione sembrerebbe sostanzialmente semplice, ma complicata

dal fatto che dobbiamo eseguire internamente alla procedure un accesso al database sulla tabella Soggetti, operazione che non era necesI libri di ioPROGRAMMO/SQL Server aspetti avanzati
71
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:04
Pagina 72
Capitolo 2
saria nella funzione di esempio vista in precedenza e anche dal fatto

che per il calcolo del codice fiscale non serve il nome della localit di
nascita ma bens il corrispondente codice belfiore che, per effetto della
Terza Forma Normale, stato portato nella tabella Localita. Infatti Soggetti contiene solo il campo LocalitaNascita: il CodiceBelfiore va recuperato dall'altra tabella.
Dunque abbiamo tutti gli elementi per partire.
2.5.1 Il codice
Osserviamo il codice. Anche in questo caso il metodo che soggiace alla stored procedure statico ed incorniciato da un attributo Microsoft.SqlServer.Server.SqlProcedure. Esso completamente commentato per una
pi facile comprensione, pertanto verrano evitati ulteriori commendi
fuori dal codice:
using System;
using System.Data;
{
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetSoggettoConCodiceFiscale(int idSoggetto)
{
//viene caricata la cache dei codici belfiore
PartiComuni.CacheCodiciBelfiore();
//si apre una connessione al database per eseguire la query
//la connectionstring semplicemente "context connection=true"
72
Capitolo 2 (037-102):007-050
Capitolo 2
29-10-2007
16:04
Pagina 73
SQL SERVER
ASPETTI
AVANZATI
//che consente di agganciarsi alla connessione SQL Server

//corrente da cui stata lanciata la stored procedure
using (SqlConnection conn =
new SqlConnection("context connection=true"))
{
//apertura dela connessione
conn.Open();
//recupero del Pipe, cio del canale di comunicazione con SQL Server
SqlPipe pipe = SqlContext.Pipe;
//esecuzione della query che recupera i dati da Soggetti
//filtrati secondo il paramatro passato come argomento alla sp
SqlCommand cmd = new SqlCommand(
" SELECT IdSoggetto, Cognome, Nome, LocalitaNascita, " +
" CodiceFiscale, Sesso, DataNascita " +
" FROM Soggetti " +
" WHERE IdSoggetto = @IdSoggetto OR @IdSoggetto = -1",
conn);
//valorizzazione del parametro @IdSoggetto
cmd.Parameters.AddWithValue("@IdSoggetto", idSoggetto);
//apertura di una DataReader per il recupero del resultset
SqlDataReader reader = cmd.ExecuteReader();
//definizione della struttura del resultset
//vengono indicati tutti i campi e il corrispondente tipo
//la definzione posizionale e pertanto i campi del resultset
//si ritroveranno esattamente nello stesso ordine
SqlMetaData[] metadata = new SqlMetaData[]
{
new SqlMetaData("IdSoggetto", SqlDbType.Int),
new SqlMetaData("Cognome", SqlDbType.VarChar, 50),
new SqlMetaData("Nome", SqlDbType.VarChar, 50),
new SqlMetaData("LocalitaNascita", SqlDbType.NVarChar, 50),
73
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:04
Pagina 74
Capitolo 2
new SqlMetaData("CodiceFiscale", SqlDbType.VarChar, 16),

new SqlMetaData("Sesso", SqlDbType.NChar, 1),
new SqlMetaData("DataNascita", SqlDbType.DateTime),
new SqlMetaData("CodiceFiscaleCalcolato", SqlDbType.VarChar,
16),
new SqlMetaData("Corretto", SqlDbType.Bit),
};
//creazione dell'oggetto record che incapsuler il resultset
SqlDataRecord record = new SqlDataRecord(metadata);
//il pipe viene istruito sul fatto che verr restituito un resultset
pipe.SendResultsStart(record);
//navigazione sulle righe del DataReader contenenti i dati ottenuti dalla
query
while (reader.Read())
{
//definzione e recupero dei valori dei campi della riga corrente del
datareader
int idSogg;
string cognome, nome, localita, codiceFiscale, sesso,
nuovoCodiceFiscale;
DateTime dataNascita;
idSogg = reader.GetSqlInt32(0).Value;
cognome = reader.GetSqlString(1).Value;
nome = reader.GetSqlString(2).Value;
localita = reader.GetSqlString(3).Value;
codiceFiscale = reader.GetSqlString(4).Value;
sesso = reader.GetSqlString(5).Value;
dataNascita = reader.GetDateTime(6).Date;
//invocazione della funzione di ricalcolo del codice fiscale
//ad essa vengono passati come argomento tutte le informazioni
necessarie al
74
Capitolo 2 (037-102):007-050
29-10-2007
Capitolo 2
16:04
Pagina 75
SQL SERVER
ASPETTI
AVANZATI
//ricalcolo
nuovoCodiceFiscale =
PartiComuni.CalcolaCodiceFiscale(cognome, nome, sesso,
localita, dataNascita);
//valorizzazione dei campi della riga corrente; il primo parametro
// delle funzioni Set indica proprio la posizione della riga
nel resultset
record.SetInt32(0, idSogg);
record.SetSqlString(1, cognome);
record.SetSqlString(2, nome);
record.SetSqlString(3, localita);
record.SetSqlString(4, codiceFiscale);
record.SetSqlString(5, sesso);
record.SetSqlDateTime(6, dataNascita);
record.SetSqlString(7, nuovoCodiceFiscale);
record.SetSqlBoolean(8, (nuovoCodiceFiscale == codiceFiscale));
//invio della riga corrente nel resultset
pipe.SendResultsRow(record);
}
//chisura del datareader
reader.Close();
//il flusso del resultset completo e il canale viene chiuso
pipe.SendResultsEnd();
}
}
}
}
Per completezza si riportano le due funzioni statiche CacheCodiciBelfiore e CalcolaCodiceFiscale, entrambe definite nella classe PartiComuni.
Di questa classe abbiamo gi riportato una porzione in precedenza che,
dunque, ometteremo. Osserviamo la prima; essa fa uso di un'istanza di
Dictionary di tipo generic che contiene tutte le coppie chiave valore di
75
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:04
Pagina 76
Capitolo 2
NomeLocalita CodiceBelfiore. Questo oggetto viene popolato solo la

prima volta viene invocato e successivamente funziona come cache per
evitare ulteriori accessi alla base dati:
{
class PartiComuni
{
//cache dei valori dei codici belfiore: la chiave la localit e il valore
// il codice belfiore
internal static readonly Dictionary<string, string> CodiciBelfiore =
CodiciBelfiore = new Dictionary<string, string>();
//la funzione popola la cache
internal static void CacheCodiciBelfiore()
{
//se la cache gi stato popolata non viene effettuata nessuna
operazione
if (CodiciBelfiore.Count > 0) return;
string codiceBelfiore, localita;
//apertura della connessione a SQL Server
{
conn.Open();
// open the connection
//caricamento di tutti i dati presenti nella tabella Localita

" SELECT Localita, CodiceBelfiore " +
" FROM Localita " ,
conn);
76
Capitolo 2 (037-102):007-050
29-10-2007
Capitolo 2
16:04
Pagina 77
SQL SERVER
ASPETTI
AVANZATI
{
//riempimento della cache
localita = reader.GetSqlString(0).Value;
codiceBelfiore = reader.GetSqlString(1).Value;
CodiciBelfiore[localita] = codiceBelfiore;
}
reader.Close();
}
}
La successiva funzione, invece, effettua il ricalcolo del codice fiscale a partire da tutti i parametri che lo costituiscono. Verr omessa ogni spiegazione perch non attinente allo scopo del volume:
internal static string CalcolaCodiceFiscale(
string cognomeSoggetto,
string nomeSoggetto,
string sesso,
string localitaNascita,
DateTime dataNascita)
{
initializeValues();
try
{
int cnt = 0;
string cognome = null;
for (int i = 0; i < cognomeSoggetto.Length; i++)
{
if (reConsonanti.Match(cognomeSoggetto[i].ToString()).Success)
{
cognome = cognome + cognomeSoggetto[i].ToString();
77
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:04
Pagina 78
Capitolo 2
cnt++;
if (cnt == 3)
break;
}
}
if (cnt < 3)
{
for (int i = 0; i < cognomeSoggetto.Length; i++)
{
if (reVocali.Match(cognomeSoggetto[i].ToString()).Success)
{
cognome = cognome + cognomeSoggetto[i].ToString();
cnt++;
if (cnt == 3)
break;
}
}
}
for (int i = cnt; i < 3; i++)
cognome = cognome + "X";
MatchCollection totCons = reConsonanti.Matches(nomeSoggetto);
cnt = 0;
string nome = null;
int consContante = 0;
for (int i = 0; i < nomeSoggetto.Length; i++)
{
if (reConsonanti.Match(nomeSoggetto[i].ToString()).Success)
{
consContante++;
if (totCons.Count > 3 && consContante == 2)
78
Capitolo 2 (037-102):007-050
Capitolo 2
29-10-2007
16:04
Pagina 79
SQL SERVER
ASPETTI
AVANZATI
continue;
else
{
cnt++;
nome = nome + nomeSoggetto[i].ToString();
if (cnt == 3)
break;
}
}
}
if (cnt < 3)
{
for (int i = 0; i < nomeSoggetto.Length; i++)
{
if (reVocali.Match(nomeSoggetto[i].ToString()).Success)
{
cnt++;
nome = nome + nomeSoggetto[i].ToString();
if (cnt == 3)
break;
}
}
}
for (int i = cnt; i < 3; i++)
nome = nome + "X";
string anno = dataNascita.ToString("yy");
string mese = mesi[dataNascita.Month - 1].ToString();
string giorno = sesso == "M" ? dataNascita.ToString("dd") :
(dataNascita.Day + 40).ToString();
string codicebelfiore = PartiComuni.CodiciBelfiore[localitaNascita];
string codfisc = cognome + nome + anno + mese + giorno + codice
79
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:04
Pagina 80
Capitolo 2
belfiore;
codfisc = codfisc.ToUpper();
int codicecontrollo = 0;
for (int i = 0; i < 15; i++)
{
if ((i + 1) % 2 == 0)
codicecontrollo = codicecontrollo + (int)pari[codfisc[i]];
else
codicecontrollo = codicecontrollo + (int)dispari[codfisc[i]];
}
codfisc = codfisc + controllo[(codicecontrollo % 26)];
if ( VerificaCodiceFiscale(codfisc ) )
return codfisc;
else
return null;
}
catch (Exception ex)
{
//
}
return null;
}
2.5.2 Registrazione, test e debug della

stored procedure
Visual Studio 2005 Professional come al solito si occupa della registrazione della procedure sul database. Ma se operassimo manualmente, dopo aver ricompilato e caricato l'assembly in SQL Server come abbiamo
fatto nel precedente esempio, dovremmo registrare la stored procedure con il seguente comando T-SQL:
CREATE PROCEDURE [dbo].[GetSoggettoConCodiceFiscale]
80
Capitolo 2 (037-102):007-050
29-10-2007
Capitolo 2
16:04
Pagina 81
SQL SERVER
ASPETTI
AVANZATI
@idSoggetto [int]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME
[CLREsempio].[CLREsempio.StoredProcedures].[GetSoggettoConCodiceFisc
ale]
GO
Per il test della stored procederemo semplicemente invocandola via TSQL:

--per ottenere l'elenco di tutti i soggetti
EXEC GetSoggettoConCodiceFiscale -1
Figura 2.10: La stored procedure in azione
--per ottenere solo il soggetto con id 2

EXEC GetSoggettoConCodiceFiscale 2
In Figura 2.10 possiamo osservare il resultset generato dalla sua chiamata.
2.6 TRIGGER IN CLR

Per dovere di completezza non possiamo concludere la lunga disanima
81
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:04
Pagina 82
Capitolo 2
di questo argomento mostrando la creazione di un trigger in C#. SQL Server 2005 offre la possibilit di implementare l'intera gamma di trigger
in .NET, ma per l'esempio ci concentreremo su uno molto semplice. Selezionando il progetto corrente dal pannello Solution Explorer di Visual
Studio 2005 e attivando il men contestuale con il tasto destro del mouse, sceglieremo la voce Add > Trigger. Il nostro trigger di esempio si
chiama TriggerCodiceFiscale ed di tipo FOR UPDATE,INSERT sulla tabella Soggetti. Infatti sul solito metodo statico che lo implementa presente l'attributo Microsoft.SqlServer.Server.SqlTrigger che, per, a differenza della stored procedure e della funzione, propone una serie di
parametri aggiuntivi quali il nome (Name = "TriggerCodiceFiscale"), la
tabella su cui agir (Target = "Soggetti") e la tipologia di trigger (Event
= "FOR INSERT,UPDATE").
Il trigger verificher che il codice fiscale della riga in via di inserimento
o modifica sia corretto. In caso affermativo non far nulla, diversamente sollever un'eccezione applicativa intercettabile e gestibile da codice T-SQL. Eccone il codice commentato:
using System;
using System.Data;
using System.Data.Sql;
{
public partial class Triggers
{
[Microsoft.SqlServer.Server.SqlTrigger(Name = "TriggerCodiceFiscale",
Target = "Soggetti", Event = "FOR INSERT,UPDATE")]
public static void TriggerCodiceFiscale()
{
82
Capitolo 2 (037-102):007-050
29-10-2007
Capitolo 2
16:04
Pagina 83
SQL SERVER
ASPETTI
AVANZATI
//apertura della connesione al database

{
conn.Open();
//recupero del contesto del trigger
SqlTriggerContext triggerContext = SqlContext.TriggerContext;
//recupero del Pipe, cio del canale di comunicazione con SQL Server
SqlPipe sqlPipe = SqlContext.Pipe;
//recupero dei dati in via di inserimento nella tabella e che
//hanno scatenato il trigger
" SELECT CodiceFiscale FROM INSERTED ",
conn);
{
//lettura del parametro codice fiscale in via di inserimento
string codiceFiscale = reader.GetSqlString(0).Value;
reader.Close();
if (triggerContext.TriggerAction ==
TriggerAction.Insert)
{
//verifica della bont del codice fiscale
if (!PartiComuni.VerificaCodiceFiscale(codiceFiscale))
{
//se il codice fiscale non valido viene sollevata un'eccezione
utente
//con il buon vecchio comando RAISERROR
cmd.CommandType = CommandType.Text;
cmd.CommandText = "RAISERROR ('Codice Fiscale errato o
mancante', 1, 5)";
SqlContext.Pipe.ExecuteAndSend(cmd);
83
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:04
Pagina 84
Capitolo 2
}
}
else
{
//gestisci UPDATE
}
break;
}
SqlContext.Pipe.Send("Trigger FIRED");
}
}
}
}
2.6.1 Registrazione,
test e debug del trigger
Ancora una volta Visual Studio 2005 Professional fa tutto il lavoro di
deployment e registrazione del trigger per noi, ma operando manualmente, dopo l'inevitabile compilazione e upload dell'assembly gi viste in precedenza, dovremmo effettuare la registrazione del trigger con il seguente comando:
CREATE TRIGGER [dbo].[TriggerCodiceFiscale] ON [dbo].[Soggetti] AFTER
INSERT, UPDATE AS
EXTERNAL NAME
[CLREsempio].[CLREsempio.Triggers].[TriggerCodiceFiscale]
GO
Il trigger non direttamente invocabile come le stored procedure e le funzioni, ma viene automaticamente invocato dal database engine al verificarsi dell'evento per cui il trigger registrato. Pertanto per il test e il
debugging del nostro esempio dovremmo operare con un inserimento
di una riga nella tabella Soggetti:
84
Capitolo 2 (037-102):007-050
Capitolo 2
29-10-2007
16:04
Pagina 85
SQL SERVER
ASPETTI
AVANZATI
Figura 2.11: Il trigger puntiglioso

VALUES ('Vessia', 'Vito', N'Bari', 'VSSVSI74M30A662W', N'M', '1974-0830
00:00:00.000')
Il codice fiscale inserito volutamente errato. Osserviamo le reazioni di

SQL Server all'esecuzione della INSERT in Figura 2.11.
2.7 AGGREGATI PERSONALIZZATI

SQL Server 2005 introduce una nuova possibilit nel suo supporto al
CLR: gli aggregati personalizzati. Il linguaggio T-SQL gi ricco di aggregati, cio di funzioni su base statistica (AVG, STDEV) o che in generale
operano sulla massa di dati (delle righe restituite nel resultset) e non
sul singolo campo (MIN, MAX, SUM, ecc...). Con SQL Server 2005 e
.NET possibile espandere all'infinito questa possibilit introducendo
aggregati personalizzati che soddisfino qualsiasi esigenza. L'aggregato viene sviluppato in .NET, compilato, registrato e caricato su SQL Server, pronto per essere usato nelle query alla stregua degli aggregati
convenzionali.
Per meglio comprenderne l'uso e le modalit di codifica, procediamo
con il solito esempio che ha a che fare con il codice fiscale: immaginiaI libri di ioPROGRAMMO/SQL Server aspetti avanzati
85
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:04
Pagina 86
Capitolo 2
mo un aggregato che agisca sul campo CodiceFiscale e che ritorni il codice fiscale corrispondente al Soggetto pi giovane nell'ambito del resultset. Come sappiamo, la data di nascita, nell'ambito del codice fiscale, occupa una porzione centrale del codice stesso pertanto non si pu
utilizzare un normale criterio di ordinamento alfabetico per ordinare il
risultato per data di nascita, ma bisogna procedere con l'interpretazione del codice fiscale stesso. Inoltre sappiamo anche che l'algoritmo, per
distinguere le donne dagli uomini aggiunge 40 alla porzione relativa al
giorno di nascita, aspetto che deve essere tenuto in considerazione
quando si confrontano le date di nascita per determinare il pi giovane.
Una volta messo appunto il nostro aggregato Younger saremo in grado di usarlo nel seguente modo, come mostrato in Figura 2.12:
Figura 2.12: Il nostro nuovo e giovane aggregato
SELECT dbo.Younger(CodiceFiscale) from Soggetti
Ma procediamo con la creazione dell'aggregato: selezionando il progetto corrente dal pannello Solution Explorer di Visual Studio 2005 e attivando il men contestuale con il tasto destro del mouse, sceglieremo la
voce Add > Aggregato. Verr proposto un scheletro di codice di un tipo
strutturato (una struct). Sulla struct presente l'attributo Microsoft.SqlServer.Server.SqlUserDefinedAggregate dotato di un costruttore a cui passeremo
il parametro Format. Questo un enumerativo che pu contenere i valori Native, UserDefined e Unknown. Il suo scopo descrivere la modalit
di persistenza dei valori all'interno dell'aggregato. Per sua stessa natura,
86
Capitolo 2 (037-102):007-050
Capitolo 2
29-10-2007
16:04
Pagina 87
SQL SERVER
ASPETTI
AVANZATI
infatti, un aggregato ha memoria, cio deve conservare i valori. Si pensi

alla MIN che deve conservare il minimo valore nell'ambito del resultset,
magari confrontando quel valore con quelli delle righe resultset corrente
per determinare se altre righe hanno valori che possano costituire il nuovo minimo e questo fino al completo scorrimento di tutti i valori. Con la
stessa finalit presente anche l'attributo MaxByteSize che esprime la
dimensione in byte del valore che dovr essere conservato e restituito
dall'aggregato. Il codice fiscale occupi 16 caratteri e cos riserviamo 32 byte per il formato Unicode. Il codice dell'aggregato riportato di seguito
ed completamente commentato pertanto si rimanda ai commenti per
una migliore comprensione.
using System;
using System.IO;
using System.Data;
{
//l'aggregato viene marcato col doppio attributo di Serializable perch il suo
valore temporaneo verr
//serializzato e conservato all'interno di SQL Server e con l'attributo
SqlUserDefinedAggregate
//che indica la sua natura di aggregato SQL Server 2005 CLR.
//l'argomento MaxByteSize indica la dimensione in byte dell'informazione
conservata dall'aggregato
//nel caso specifico si tratta di un codice fiscale che sappiamo occupare 16
caratteri e quindi 32 byte
//in formato Unicode
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Unknown,
87
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:04
Pagina 88
Capitolo 2
MaxByteSize = 32)]
public struct Younger : IBinarySerialize
{
//la variabile privata che conterr il codice fiscele del soggetto
pi giovane
private SqlString _cfGiovane;
//metodo di inizializzazione dell'aggregato che viene invocata all'inizio
della query in cui
//compare l'aggregato custom; nel caso specifico viene messo a null la
variabile che contiene
//il codice fiscale del pi giovane in modo che il primo elmento restituito
dalla query venga
//reso automaticamente il pi giovane a meno di confronti con i CF dei
record successivi
public void Init()
_cfGiovane = null;
}
// il metodo fondamentale dell'aggregato e viene invocato ad ogni
iterazione passando l'argomento
//passato all'aggregato nella query di esempio.
//
Es.
SELECT dbo.Younger(CodiceFiscale) FROM Soggetti
public void Accumulate(SqlString Value)

{
//se la variabile membro nulla (condizione possibile solo sul primo
record visto che
//il campo codice fiscale non nullable), viene considerato il primo
soggetto come il
//pi giovane
if (_cfGiovane.IsNull )
{
_cfGiovane = Value;
88
Capitolo 2 (037-102):007-050
29-10-2007
Capitolo 2
16:04
Pagina 89
SQL SERVER
ASPETTI
AVANZATI
}
else
{
//la data di nascita del codice fiscale espressa nella forma YYMDD
// YY = ultime due cifre dell'anno di nascita
// M = lettera corrispondente al mese di nascita nella sequenza
//
{ 'A', 'B', 'C', 'D', 'E', 'H', 'L', 'M', 'P', 'R', 'S', 'T' }
// DD = giorno di nascita per i maschi, giorno di nascita + 40 per le

donne
//dunque per confrontare veramente due date di nascita
necessario riportare
//le due date da confrontare al maschile in modo che l'offset 40 per
le donne
//non infici il confronto
//codice fiscale del soggetto attualmente considerato il pi giovane
string dataMaschile = _cfGiovane.ToString();
//codice fiscale del soggetto corrispondete all'attuale record
da confrontare
string dataMaschileValue = Value.ToString();
//se il codice fiscale del soggetto attualmente pi giovane
appartiene
//ad una donna avr "DD" > 40
if (int.Parse(dataMaschile.Substring(9, 2)) > 40)
{
//la data viene ricondatta al maschile per facilitare il confronto
dataMaschile = dataMaschile.Substring(0, 9) +
(int.Parse(dataMaschile.Substring(9, 2)) 40).ToString("00") +
dataMaschile.Substring(11, 5);
}
//stessa considerazione da fare per il codice fiscale del soggetto
89
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:04
Pagina 90
Capitolo 2
attualmente oggetto
//del confronto
if (int.Parse(dataMaschileValue.Substring(9, 2)) > 40)
{
dataMaschileValue = dataMaschileValue.Substring(0, 9) +
(int.Parse(dataMaschileValue.Substring(9, 2)) 40).ToString("00") +
dataMaschileValue.Substring(11, 5);
}
//una volta riportate le porzioni di codice fiscale relative alle date
di nascita
//rapportate al maschile, il confronto tra due date si riconduce ad
un banale confronto tra
//stringhe visto che la parte pi significa l'anno che gi
espressa in forma numerica
//facilmente confrontabile, il carattere del mese ordinato in modo
crescente al crescere del
//mese stesso e la parte del giorno un numero di due cifre non pi
soggetto al problema
//dell'addizionamento di 40 per le donne a seguito
della conversione al maschile
if (dataMaschile.Substring(6,
5).ToUpper().CompareTo(dataMaschileValue.Substring(6, 5).ToUpper()) <
0)
{
//se la data di nascita del soggetto corrente inferiore a quella
dell'attuale soggetto
//pi giovane, il primo diventa il nuovo soggetto pi giovane
_cfGiovane = Value.ToString();
}
}
}
90
Capitolo 2 (037-102):007-050
Capitolo 2
29-10-2007
16:04
Pagina 91
SQL SERVER
ASPETTI
AVANZATI
//pattern standard per la funzione di Merge dei valori

public void Merge(Younger Group)
{
Accumulate(Group.Terminate());
}
//il metodo di chiusura dell'aggregato, invocato al termine della query
public SqlString Terminate()
{
return _cfGiovane;
}
//serializzazione del valore del codice fiscale dell'attuale soggetto
pi giovane
//viene usato un BinaryWriter per salvare il valore
public void Write(BinaryWriter w)
{
w.Write(_cfGiovane.ToString());
}
//deserializzazione del valore del codice fiscale dell'attuale soggetto pi
giovane
//viene recuperato il valore da BinaryWriter
public void Read(BinaryReader r)
{
_cfGiovane = r.ReadString();
}
}
}
2.7.1 Registrazione, test e debug

dell'aggregato
Ancora una volta Visual Studio 2005 Professional fa tutto il laI libri di ioPROGRAMMO/SQL Server aspetti avanzati
91
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:04
Pagina 92
Capitolo 2
voro di deployment e registrazione dell'aggregato per noi, ma

operando manualmente, dopo l'inevitabile compilazione e upload
dell'assembly gi viste in precedenza, dovremmo effettuare la registrazione del trigger con il seguente comando:
CREATE AGGREGATE [dbo].[Younger]
(@Value [nvarchar](4000))
RETURNS[nvarchar](4000)
EXTERNAL NAME [CLREsempio].[CLREsempio.Younger]
GO
2.8 TIPO DEFINITO DALL'UTENTE

Per completare questa ricca disquisizione sul supporto al CLR fornito
da SQL Server 2005 necessario introdurre un ultimo ed importante
elemento: la possibilit di creare tipi di dati personalizzati da usare in tabelle, viste e query T-SQL e che siano in grado da fornire al tipo di dato
una logica consistente e strutturata che solo il codice di programmazione pu dare. L'esempio naturalmente d'obbligo e, a questo punto
della trattazione, sarete sicuramente giunti alla conclusione che il mondo dei database, e forse anche l'intero universo conosciuto, ruotano intorno al codice fiscale e dunque quale esempio di tipo utente personalizzato migliore da mostrare se non proprio il tipo CodiceFiscale? L'idea piuttosto semplice: il tipo utente CodiceFiscale sar un campo in
grado naturalmente di ospitare codici fiscali, ma avr anche la logica, una
volta inserito in un campo di una tabella, di rifiutare la INSERT di un
nuovo record qualora il codice fiscale sia errato, analogamente a quanto visto in precedenza col trigger, ma senza aggiungere altra logica alla tabella perch essa sar gestita dal campo stesso, analogamente a quanto avviene per le Check Constraint.
Inoltre questo campo render inutile l'aggiunta dei campi Localit di
Nascita, Data di Nascita e Sesso nella tabella anagrafica perch queste
informazioni saranno ricavabili dal codice fiscale stesso. Paradossalmen92
Capitolo 2 (037-102):007-050
Capitolo 2
29-10-2007
16:04
Pagina 93
SQL SERVER
ASPETTI
AVANZATI
te, infatti, si potrebbe quasi affermare che l'uso di un campo Codice Fiscale viola la Prima Forma Normale proprio perch aggrega pi informazioni nello stesso campo, ma naturalmente prendete questa affermazione come una provocazione e continuate a tranquillamente a definire i
campi codice fiscale nelle vostre tabelle... Osserviamo in Figura 2.13
l'uso del nuovo tipo come campo di una tabella.
Figura 2.13: Uso del tipo di dato personalizzato in una tabella
Selezionando il progetto corrente dal pannello Solution Explorer di Visual Studio 2005 e attivando il men contestuale con il tasto destro del
mouse, sceglieremo la voce Add > Tipo definito dall'utente. Il wizard
produrr un nuovo file di sorgente nel progetto che contiene una struct.
Su di essa verr posto l'attributo Microsoft.SqlServer.Server.SqlUserDefinedType a cui viene passato l'argomento gi visto in precedenza
Format e i parametri MaxByteSize (anch'esso gi incontrato) e IsByteOrdered che esprime la modalit di ordinamento dei campi. Infatti si immagini una query su una tabella che dispone di un campo di tipo CodiceFiscale: l'attributo esprime come si dovr comportare la eventuale
clausola ORDER BY CodiceFiscale.
Nell'esempio specifico, poi, il tipo implementa anche l'interfaccia IBinarySerializer gi incontrata in precedenza nell'aggregato e l'interfaccia INullable che permette di gestire campi con valori di tipo null per il
tipo personalizzato appena introdotto. Ma si osservi il codice commentato per una maggiore comprensione:
93
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:04
Pagina 94
Capitolo 2
using System;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Data;
{
//Un UDT implementato come una normale struct di .NET
//va corredato con l'attributo SqlUserDefinedType a cui si passa il tipo
di formato di serializzazione
//come per gli aggregati, la dimensione richiesta dal tipo espressa in byte,
il tipo di ordinamento
//e altre informazioni
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined,
IsByteOrdered=true, MaxByteSize=32)]
//l'UDT pu implementare le interfacce per la gestione del NULL e per la
serializzazione custom
public struct CodiceFiscale : INullable, IBinarySerialize
{
//variabile privata membro che contiene il codice fiscale vero e proprio
//il suo valore sar l'unico salvato e recuperato effettivamente
dal database, tutti gli
//altri valori verranno semplicemente estratti e ricalcolati in base
a questo
//in questo modo si riduce l'occupazione di spazio sul database
portandola alla stessa
//occupazione di un normale campo NVARCHAR(16) in grado
di conservare un codice fiscale
private SqlString m_CF;
94
Capitolo 2 (037-102):007-050
29-10-2007
Capitolo 2
16:04
Pagina 95
SQL SERVER
ASPETTI
AVANZATI
//campo data di nascita, estratto dinamicamente dal codice fiscale

public SqlDateTime DataNascita;
//campo sesso, pu assumere i valori "M" o "F", estratto
dinamicamente dal codice fiscale
public SqlString Sesso;
//campo Codice Belfiore della localit di nascita, estratto
automaticamente dal codice fiscale
public SqlString CodiceBelfiore;
//serve per testare la condizione di null
private bool m_Null;
//il metodo ToString() implementa il classico metodo ToString() di CLR,
utile in questi casi
//quando si intende ottenere una rappresentazione in stringa del valore
public override string ToString()
{
return m_CF.ToString();
}
//l'UDT implementa l'interfaccia INullable e cos gestisce la possibilit di
valori null nel campo
public bool IsNull
{
get
{
return m_Null;
}
}
//metodo statico dell'UDT in grado di restituire un'istanza null del tipo
public static CodiceFiscale Null
{
get
{
95
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:04
Pagina 96
Capitolo 2
CodiceFiscale h = new CodiceFiscale();

h.m_Null = true;
return h;
}
}
// il metodo fondamentale degli UDT CLR: consente l'analisi e il parsing
del valore stringa
//che soggiace al tipo personalizzato: nell'esempio specifico verr
passato proprio il codice
//fiscale da gestire e a questo punto verr validato e ne verranno
estratte le componenti
//fondamentali
public static CodiceFiscale Parse(SqlString s)
{
//verifica dell condizione di null
if (s.IsNull)
return Null;
//viene verificata la bont del codice fiscale attraverso l'algoritmo di
controllo
//del carattere di controllo finale
if (!PartiComuni.VerificaCodiceFiscale(u.m_CF.ToString()))
throw new ArgumentException("Codice Fiscale Errato!");
//se il codice fiscale valido, vengono estratte le sue parti salienti
CodiceFiscale u = new CodiceFiscale();
u.m_CF = s.ToString().ToUpper();
u.CodiceBelfiore = s.ToString().Substring(11, 4).ToUpper();
u.Sesso = int.Parse(u.ToString().Substring(9, 2)) > 40 ? "F" : "M";
u.DataNascita = PartiComuni.CalcolaDataNascita(s.ToString());
return u;
96
Capitolo 2 (037-102):007-050
Capitolo 2
29-10-2007
16:04
Pagina 97
SQL SERVER
ASPETTI
AVANZATI
}
//gestione del recupero dell'informazione dallo stream di persistenza del
valore nel database
//l'implementazione di questo metodo necessaria perch si scelto il
formato di seralizzaizone
//Format.UserDefined in abbinamento all'implementazione
dell'interfaccia IBinarySerialize
//in realt, nell'esempio specifico, l'unica informazione da recuperare
proprio la stringa di
//16 caratteri del codice fiscale perch tutti i campi che costituiscono il
codice fiscale vengono
//calcolati ed estratti dinamicamente a partire da questa
public void Read(System.IO.BinaryReader r)
{
m_CF = r.ReadString();
CodiceBelfiore = m_CF.ToString().Substring(11, 4).ToUpper();
Sesso = int.Parse(m_CF.ToString().Substring(9, 2)) > 40 ? "F" : "M";
DataNascita = PartiComuni.CalcolaDataNascita(m_CF.ToString());
}
//gestione della serializzazione dell'informazione in uno stream binario
public void Write(System.IO.BinaryWriter w)
{
w.Write(m_CF.ToString());
}
}
}
Le caratteristiche salienti del tipo utente CodiceFiscale sono la possibilit di gestire una stringa di 16 caratteri corrispondente ad un codice
fiscale e di questa validarne la bont (metodo Parse), di permetterne la
serializzazione e la deserializzazione nella base dati (i metodi Read e
97
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:04
Pagina 98
Capitolo 2
Write), di gestirne i valori Null (il metodo IsNull e il metodo statico Null
che restituisce proprio un'istanza a valore null del tipo utente) e di restituirne il codice fiscale come stringa (il metodo convenzionale ToString
derivante da System.Object).
Ma probabilmente gli elementi che rendono davvero uniche e rilevanti
le caratteristiche di questo tipo utente e che, pi in generale, mostrano
la potenza e la versatilit del meccanismo dei tipo utente CLR in SQL Server 2005, la possibilit di estrarre ed esporre del codice fiscali informazioni quali il Codice Belfiore (propriet CodiceBelfiore), la data di nascita (propriet DataNascita) e il sesso (propriet Sesso).
Esso sono fruibili con la normale sintassi <nome_campo>.<propriet>,
pertanto, se volessimo conoscere il sesso di un soggetto, dovremmo
usare la sintassi CodiceFiscale.Sesso.
2.8.1 Registrazione, test e debug del tipo

utente
Ancora una volta Visual Studio 2005 Professional fa tutto il lavoro di
deployment e registrazione del tipo utente per noi, ma operando manualmente, dopo l'inevitabile compilazione e upload dell'assembly gi viste in precedenza, dovremmo effettuare la registrazione del trigger con
il seguente comando:
CREATE TYPE [dbo].[CodiceFiscale]
EXTERNAL NAME [CLREsempio].[CLREsempio.CodiceFiscale]
GO
A questo punto siamo pronti ad introdurre una tabella di esempio che usi questo tipo. Si tratta di una versione semplificata di Soggetti usata nel corso del capitolo: essa, infatti, presenta i soli campi Cognome, Nome e CodiceFiscale e omette i campi DataNascita, LocalitaNascita e Sesso che sono tutte ricavabili direttamente del codice fiscale e che sono esposte dal tipo utente CodiceFiscale come propriet richiamabili direttamente da query T-SQL,
98
Capitolo 2 (037-102):007-050
Capitolo 2
29-10-2007
16:04
Pagina 99
SQL SERVER
ASPETTI
AVANZATI
come vedremo in seguito. Ecco lo script T-SQL di creazione della

tabella:
CREATE TABLE [dbo].[SoggettiEx](
[IdSoggetto] [int] IDENTITY(1,1) NOT NULL,
[Cognome] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[Nome] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[CodiceFiscale] [dbo].[CodiceFiscale] NULL,
CONSTRAINT [PK_SoggettiEx] PRIMARY KEY CLUSTERED
(
[IdSoggetto] ASC
) ON [PRIMARY]
L'inserimento o la modifica di righe nella tabella molto semplice perch il valore del tipo CodiceFiscale viene trattato come un normale
NVARCHAR(16), come si evince dal metodo Merge implemenato nel
codice del tipo utente di esempio. Ed ecco una INSERT esemplificativa:
INSERT INTO dbo.SoggettiEx
(Cognome, Nome, CodiceFiscale)
VALUES ('Vessia', 'Vito', 'VSSVSI74M30A662W')
Nella Merge, inoltre, abbiamo osservato come venga contestualmente fatta una verifica della validit del codice fiscale appena inserito sollevando un'eccezione in caso di codice fiscale non valido, condizione possibile grazie al ricalcolo del codice/carattere di
controllo finale e del confronto con quello presente nel codice fiscale. In caso di INSERT o di UPDATE di codici fiscali non validi, verr
sollevata la seguente eccezione e l'operazione di scrittura non
verr eseguita:
99
Capitolo 2 (037-102):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:04
Pagina 100
Capitolo 2

A .NET Framework error occurred during execution of user-defined routine
or aggregate "CodiceFiscale":
System.ArgumentException: Codice Fiscale Errato!
System.ArgumentException:
at CLREsempio.CodiceFiscale.Parse(SqlString s)
.
The statement has been terminated.
In Figura 2.14 possiamo osservare gli effetti di una INSERT in SoggettiEx

con CodiceFiscale errato.
Figura 2.14: Inserimento di un codice fiscale errato nel tipo utente

CodiceFiscale
La query che segue mostra tutta la potenza e la versatilit del nostro tipo utente personalizzato: dalla tabella SoggettiEx, oltre ai
campi di tipo convenzionale Nome e Cognome, vengono estratte tutta una serie di informazioni dal campo CodiceFiscale. Per
cominciare il codice fiscale stesso, invocando il metodo ToString()
dell'oggetto che, nell'implementazione che abbiamo fornito per
il tipo CodiceFiscale restituisce proprio il codice fiscale intero.
Inoltre, invocando rispettivamente le propriet DataNascita, CodiceBelfiore e Sesso del tipo, si possono estrarre direttamente le
omonime informazioni rendendo cos non necessaria la presenza
esplicita di questi campi nella tabella che contiene il tipo utente
100
Capitolo 2 (037-102):007-050
29-10-2007
Capitolo 2
16:04
Pagina 101
SQL SERVER
ASPETTI
AVANZATI
CodiceFiscale.
SELECT Cognome, Nome, CodiceFiscale.ToString() AS CodiceFiscale,
CodiceFiscale.DataNascita AS DataNascita,
CodiceFiscale.CodiceBelfiore AS CodiceBelfiore,
CodiceFiscale.Sesso AS Sesso
FROM SoggettiEx
La Figura 2.15 ci mostra proprio il risultato di questa query di esempio.
Figura 2.15: Uso intensivo delle caratteristiche del tipo utente

CodiceFiscale
101
Capitolo 2 (037-102):007-050
29-10-2007
16:04
Pagina 102
Capitolo 3 (103-128):007-050
Capitolo 3
29-10-2007
16:05
Pagina 103
SQL SERVER
ASPETTI
AVANZATI
CATALOGO E FUNZIONI DI SISTEMA

La struttura di ciascun database e cio l'insieme delle tabelle, delle viste, delle stored procedure, delle funzioni, dei trigger e di ogni altro elemento che lo costituisce conservata e gestita attraverso il Catalogo di
Sistema. In SQL Server sono presenti quattro database di sistema, cio
necessari a SQL Server per il suo funzionamento corrente e che vengono creati al momenti dell'installazione dell'istanza del motore:
G
G
G
G
" master;
" model;
" tempdb;
" msdb.
Master il database principale, master, che conserva le informazioni e

le impostazioni di base del motore e che poi vengono riapplicate a livello di ciascun database a meno che queste non vengano esplicitamente ridefinite. Comprende tutte le tabelle di sistema necessarie per lavorare con il sistema di database. Pertanto esso contiene tutte le informazioni su tutti gli altri database gestiti da SQL Server, sulle connessioni di
sistema ai client e sulle autorizzazioni degli utenti.
TempDB, invece, fornisce lo spazio di memorizzazione necessario alle
tabelle temporanee e a tutti gli altri oggetti temporanei definiti nelle
interrogazioni e negli accessi a tutti gli altri database. Infatti, ogni volta che si crea un tabella temporanea in qualsiasi database, in realt viene creata una tabella fisica reale in questo database e poi viene rimossa al termine della sessione.
Model viene usato come modello quando vengono creati i database
definiti dall'utente e contiene un sottoinsieme di tutte le tabelle di sistema di Master necessarie alla definizione dei database utenti.
Msdb, infine, il database alla base delle operazioni definibili per lo
schedulatore integrato di SQL Server e cio SQL Server Agent. In pratiI libri di ioPROGRAMMO/SQL Server aspetti avanzati
103
Capitolo 3 (103-128):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:05
Pagina 104
Capitolo 3
ca in esso vengono memorizzate sia le operazioni da compiere per ciascuna schedulazione programmata (dagli script T-SQL da eseguire, ai
comandi agli script) che la loro programmazione temporale di esecuzione. In Figura 3.1 vengono mostrate queste tabelle da SQL Server Management Studio.
Figura 3.1: Database di sistema
3.1 TABELLE DEL CATALOGO

DI DATABASE
Le tabelle di sistema del database master sono distinte dalle tabelle di
sistema dei database creati dagli utenti. Le seconde costituiscono il Catalago di Database. Queste tabelle hanno la stessa struttura logica delle tabelle utente e pertanto si usa il medesimo approccio: per eseguirne l'inserimento, la modifica e la cancellazione di righe si procede attraverso il Data Manipulation Language, per effettuarne le interrogazioni,
si fauso di query T-SQL. In realt, nonostante l'inserimento, la modifica
e la cancellazione di righe in queste tabelle sia possibile, pu risultare
altamente pericoloso e pertanto questo approccio viene scoraggiato
dal produttore in favore dell'uso di stored procedure di sistema speci104
Capitolo 3 (103-128):007-050
Capitolo 3
29-10-2007
16:05
Pagina 105
SQL SERVER
ASPETTI
AVANZATI
fiche che fanno le stesse operazioni ma con maggior cognizione di causa e tenendo conto di tutti gli aspetti di interdipendenza.
Di seguito viene riportato l'elenco delle principali tabelle di sistema.
da notare che ciascun oggetto del database identificato da un nome
univoco definito dall'utente al momento della creazione, qualora si tratti di un oggetto utente, e di un identificativo numerico univoco assegnato in maniera progressiva dal sistema al momento della creazione
dell'oggetto, con un normale meccanismo di Identity come accede nelle nostre tabelle utente, a riprova del fatto che i dati nelle tabelle di sistema sono trattati alla stregua dei dati nelle tabelle utente.
COLONNA
id
name
uid
type
crdate
parent_obj
DESCRIZIONE
L'identificativo univoco dell'oggetto all'interno del database
Il nome dell'oggetto del database
L'identificativo del possesso dell'oggetto
Il tipo di oggetto del database. Pu assumere i seguenti
valori:
C = vincolo check;
D = predefinito;
F = chiave esterna;
K = chiave primaria o vincolo di univocit
R = regola
RF = stored procedure di replicazione
S = tabella di sistema
TR = trigger
U = tabella ordinaria definita dall'utente
V = vista
X = stored procedure estesa
Data di creazione dell'oggetto nel database
Identificativo dell'oggetto padre. Non tutti gli oggetti hanno
un padre, ma taluni si, ad esempio una chiave primaria (tipo
K) ha per padre la tabella di cui rappresenta il vincolo. In
questo modo possibile risalire alla gerarchia di tutti gli
oggetti del database.
105
Capitolo 3 (103-128):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:05
Pagina 106
Capitolo 3
3.1.1 sysobjects
Si tratta della tabella di sistema principale di SQL Server. presente sia
nel database master che in tutti i database creati dall'utente. Contiene
una riga per ciascun oggetto presente nel database e quindi ogni tabella, ogni stored procedure. Ogni vista ed ogni altro oggetto del database sono censiti come righe diquesta tabella. Di seguito se ne riportano
i campi
Come si accennava in precedenza, tutte le tabella di sistema sono gestibili come normali tabelle create dall'utente, pertanto sar possibile effettuare la seguente query di esempio:
SELECT *
FROM sysobjects
WHERE TYPE NOT IN ('S')
In Figura 3.2 possibile osservare il risultato della query di esempio sul

database Libro usato nella presente esposizione.
Figura 3.2: Interrogazione su sysobjects
106
Capitolo 3 (103-128):007-050
29-10-2007
Capitolo 3
16:05
Pagina 107
SQL SERVER
ASPETTI
AVANZATI
3.1.2 syscolumns
presente sia nel database master che in tutti i database creati
dall'utente. Contiene una riga per ciascuna colonne delle tabelle e delle viste presenti nel database e per ciascun parametro di
stored procedure del database. Di seguito se ne riportano i campi pi importanti:
COLONNA
id
colid
name
DESCRIZIONE
Rappresenta l'identificativo univoco dell'oggetto nel quale
compare la colonna
Rappresenta l'identificativo univoco di colonna dell'oggetto
rappresentato dal campo id
il nome della colonna vera e propria
Si osservi al seguente query che estrae l'elenco delle colonne della tabella Soggetti definita nel database di esempio Libro:
SELECT *
FROM syscolumns
WHERE id = 1973582069 'id della tabella Soggetti
La query filtrata per un id specifico che, nell'esempio, proprio

l'id della tabella Soggetti. In Figura 3.3 se ne pu avere un saggio del risultato.
Figura 3.3: Interrogazione su syscolumns

107
Capitolo 3 (103-128):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:05
Pagina 108
Capitolo 3
3.1.3 sysindexes
dall'utente. Contiene una riga per ciascun indice. La sua interpretazione e il suo uso sono per troppo complessi, pertanto preferibile l'uso della stored procedure di sistema sp_helpindex che
permette di riportare in formato decisamente pi comprensibile
lo stato degli indici delle tabelle del database. La sua sintassi :
sp_helpindex @nome_oggetto
Ecco un'interrogazione di esempio degli indici della tabella Orders
di Northwind:
sp_helpindex 'Orders'
In Figura 3.4 possibile osservare il risultato dell'invocazione.
Figura 3.4: Gli indici della tabella Orders di Northwind via sp_helpindex
3.1.4 sysusers
dall'utente. Contiene una riga per ciascun account di Windows, gruppo
di Windows, login di SQL Server o ruolo di SQL Server. Di seguito
se ne riportano i principali campi: In Figura 3.5 possiamo osservare
una tipica interrogazione su questa tabella.
108
Capitolo 3 (103-128):007-050
Capitolo 3
COLONNA
uid
sid
name
29-10-2007
16:05
Pagina 109
SQL SERVER
ASPETTI
AVANZATI
DESCRIZIONE
L'identificativo numerico dell'utente, univoco nel database
L'identificativo di sistema dell'utente creatore del database
Il nome dell'utente, anche quest'ultimo deve essere univoco
nell'ambito del database
Figura 3.5: La tabella di sistema degli utenti del database
3.1.5 sysdatabases
Questa tabella di sistema appare nel solo database master e contiene
una riga per ciascuno dei database presenti nell'istanza di SQL Server.
COLONNA
dbid
name
sid
crdate
filaname
status
DESCRIZIONE
Identificativo numerico univoco del database nell'ambito
dell'istanza
Nome simbolo univoco del database nell'ambito dell'istanza
di SQL Server
Identificativo di sistema del creatore del database
Data di creazione del database
Nome del file fisico che soggiace al database
Stato del database. Sono possibili diversi stati
contemporaneamente sullo stesso database e pertanto viene
usata la seguente codifica bitwise. Eccone alcuni:
1 = autoclose (ALTER DATABASE)
109
Capitolo 3 (103-128):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:05
COLONNA
status
Pagina 110
Capitolo 3
DESCRIZIONE
4 = select into/bulkcopy (ALTER DATABASE con l'opzione SET
RECOVERY)
8 = trunc. log on chkpt (ALTER DATABASE con l'opzione SET
RECOVERY)
16 = torn page detection (ALTER DATABASE)
32 = loading
64 = pre recovery
128 = recovering
256 = not recovered
512 = offline (ALTER DATABASE)
1024 = read only (ALTER DATABASE)
2048 = dbo use only (ALTER DATABASE con l'opzione SET
RESTRICTED_USER)
4096 = single user (ALTER DATABASE)
16384 = ANSI null default (ALTER DATABASE)
32768 = emergency mode
65536 = concat null yields null (ALTER DATABASE)
4194304 = autoshrink (ALTER DATABASE)
1073741824 = cleanly shutdown
Ancora una volta, con una normale query su questa tabella, possibile
ottenere l'elenco dei database presenti nell'istanza, magari filtrati da una
clausola di WHERE sullo stato usando l'operatore & di bitwise, come mostrato in Figura 3.6, per ottenere l'elenco dei soli database che dispongono dell'opzione concat null yields null.
Figura 3.6: La tabella di sistema dei database
110
Capitolo 3 (103-128):007-050
Capitolo 3
29-10-2007
16:05
Pagina 111
SQL SERVER
ASPETTI
AVANZATI
A volte pu rendersi necessario conoscere il solo dbid di un certo database, ad esempio per impostare un filtro di profilazione nel SQL Server Profiler o per verificare lo stato dei lock in un certo database.A tal proposito esiste la comoda funzione di sistema db_id() che, a fronte del nome simbolico del database, ne restituisce proprio il dbid come mostrato di seguito nell'esempio:
print db_id('Libro')
'risposta --> 9
3.1.6 sysdepends
dall'utente. Contiene una riga per ciascuna relazione di dipendenza tra tabelle, viste e stored procedure. Di seguito se ne riportano i campi pi importanti:
COLONNA
id
number
depid
depnumber
DESCRIZIONE
Identificativo numerico univoco della relazione di dipendenza
nell'ambito dell'istanza
Il numero della stored procedure
Il numero di identificazione dell'oggetto in relazione di
dipendenza con l'oggetto identificato da id
Il numero della stored procedure in relazione di dipendenza
Procediamo con il solito esempio selettivo di interrogazione sulla tabella di sistema in questione, come mostrato in Figura 3.7.
Figura 3.7: La tabella di sistema delle relazioni di dipendenza del database

111
Capitolo 3 (103-128):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:05
Pagina 112
Capitolo 3
3.1.7 sysconstraints
presente sia nel database master che in tutti i database creati dall'utente. Contiene una riga per ciascun vincolo di integrit definito per un
oggetto del database definito con una CREATE TABLE o con una ALTER TABLE. Di seguito se ne riportano i campi pi importanti:
COLONNA
constid
id
colid
status
DESCRIZIONE
Identificativo numerico univoco del vincolo nell'ambito
dell'istanza
Identificativo univoco della tabella su cui applicato il
vincolo di integrita, come definito nella tabella sysobjects
Identificativo univoco della colonna su cui applicato il
vincolo di integrita, come definito nella tabella syscolumns
Tipologia di vincolo di integrit, come elencato di seguito:
1 = vincolo PRIMARY KEY
2 = vincolo UNIQUE KEY
3 = vincolo FOREIGN KEY
4 = vincolo CHECK
5 = vincolo DEFAULT
6 = vincolo di colonna
7 = vincolo di tabella
In Figura 3.8 possibile osservare un esempio di interrogazione su questa tabella di sistema.
Figura 3.7: La tabella di sistema delle constraint del database
112
Capitolo 3 (103-128):007-050
Capitolo 3
29-10-2007
16:05
Pagina 113
SQL SERVER
ASPETTI
AVANZATI
3.2 VISTE DI CATALOGO

Le Viste di Catalogo sono una novit di SQL Server 2005 e si affiancano alla normale interrogazione delle tabelle di sistema per
conoscere la struttura e i metadati di ciascun database. Sono un
metodo da preferirsi al primo che mantenuto solo per ragioni di
compatibilit. In pratica esiste una vista di catalogo per ciascuna
delle principali tabelle di sistema. Osserviamo il seguente elenco
di mappatura:
TABELLA DI SISTEMA
sysdatabases
sysobjects
syscolumns
sysusers
VISTA DI CATALOGO
sys.databases
sys.objects (mostra i soli oggetti utente della
tabella sysobjects)
sys.system_objects (mostra i soli oggetti di
sistema della tabella sysobjects)
sys.all_objects (corrisponde esattamente alla
tabella di sistema sysobjects)
sys.columns
sys.users
3.2.1 sys.objects
Contiene una riga per ciascun oggetto presente nel database e quindi
ogni tabella, ogni stored procedure, ogni vista ed ogni altro oggetto del
database sono censiti come righe di questa tabella. Come gi mostrato
COLONNA
name
object_id
schema_id
type
DESCRIZIONE
Nome simbolico univoco dell'oggetto nell'ambito del
database
Identificativo numerico univoco nell'ambito del database
Identificativo dello schema a cui appartiene l'oggetto
Tipo dell'oggetto, rimappato direttamente sul campo type di
sysobjects di cui, evidentemente, le condivide il dizionario di
possibili valori.
113
Capitolo 3 (103-128):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:05
Pagina 114
Capitolo 3
nella tabella precedente,esistono in realt tre viste di questo tipo:sys.objects,

sys.system_objects e sys.all_objects. Tutte hanno la stessa struttura di
campi. Di seguito se ne riportano i campi pi importanti:
In Figura 3.9 possibile osservare un esempio di interrogazione su questa tabella di sistema, in particolare su sys.all_objects.
Figura 3.9: Interrogazione della Vista di Catalogo sys.all_objects
3.2.2 sys.columns
mappata direttamente sulla tabella di sistema syscolumns e pertanto contiene una riga per ciascuna colonne delle tabelle e delle viste presenti nel database e per ciascun parametro di stored procedure del database. Di seguito se ne riportano i campi pi importanti:
COLONNA
object_id
column_id
name
114
DESCRIZIONE
Rappresenta l'identificativo univoco dell'oggetto nel quale
compare la colonna
Rappresenta l'identificativo univoco di colonna dell'oggetto
rappresentato dal campo id
il nome della colonna vera e propria
Capitolo 3 (103-128):007-050
29-10-2007
Capitolo 3
16:05
Pagina 115
SQL SERVER
ASPETTI
AVANZATI
In Figura 3.10 possiamo osservare una tipica interrogazione su questa

vista.
Figura 3.10: La Vista di Catalogo delle colonne
3.2.3 sys.database_principals
Contiene una riga per ciascuno degli oggetti di sicurezza presenti nel database e quindi per ciascun utente, gruppo e ruolo del database. Di seguito se ne riportano i campi pi importanti:
COLONNA
name
principal_id
type
DESCRIZIONE
il nome univoco dell'oggetto di sicurezza (principal)
Identificativo univoco dell'oggetto di sicurezza nell'ambito
del database
Tipologia di principal. Pu assumere i seguenti valori:
S = SQL user
U = Windows user
G = Windows group
A = Application role
R = Database role
C = User mapped to a certificate
K = User mapped to an asymmetric key
115
Capitolo 3 (103-128):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:05
Pagina 116
Capitolo 3
Osserviamo, in Figura 3.11, un esempio di interrogazione di questa Vista di Catalogo per i soli tipi R.
Figura 3.11: La Vista di Catalogo dei principal
3.3 STORED PROCEDURE

DI SISTEMA
Un'altra metodologia di accesso alle meta informazioni di sistema
costituita dalla chiamata alle stored procedure di sistema. Inoltre esse
possono essere utilizzate non solo per la mera consultazione ma anche per operazioni di cambio del nome, di modifica o di cancellazione
di tali oggetti. Questo approccio da preferire all'accesso diretto in modifica alle tabelle di sistema o alle viste di catalogo perch garantisce maggiore affidabilit e consistenza nell'operazione. Alcune di queste stored procedure sono state gi incontrate ed esaminate in precedenza e
pertanto ci si limiter a presentarne soltanto alcune altre rilevanti.
3.3.1 sp_help
Questa stored procedure visualizza le informazioni di uno o pi oggetti del database. Se invocata senza parametri si limita ad elencare tutti
gli oggetti di sistema, diversamente, se parametrizzata con il nome simbolico dell'oggetto da interrogare, restituir un vero e proprio report
completo di ogni informazione relativa all'oggetto in questione.
116
Capitolo 3 (103-128):007-050
29-10-2007
Capitolo 3
16:05
Pagina 117
SQL SERVER
ASPETTI
AVANZATI
Si supponga di voler interrogare la tabella Soggetti del database di

esempio Libro. Si proceder come di seguito:
USE Libro
GO
sp_help 'Soggetti'
GO
Figura 3.12: La potente sp_help in azione
Si osservi la Figura 3.12: essa ci mostra il risultato dell'interrogazione della tabella Soggetti attraverso la stored procedure in questione. Restituisce numerosi resultset:
G " un resultset relativo all'oggetto Soggetti stesso, corrispondente
grosso modo alla riga di sysobjects relativa a questo oggetto (infatti sono visibili il nome, l'owner, la tipologia di tabella e la data di
creazione);
G " un resultset relativo alle colonne della tabella, corrispondente
grosso modo alle righe della tabella syscolumns filtrate per l'id delI libri di ioPROGRAMMO/SQL Server aspetti avanzati
117
Capitolo 3 (103-128):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
G
G
G
G
16:05
Pagina 118
Capitolo 3
la tabella Soggetti;
" un resultset relativo alla Identity impostata sul campo IdSoggetto della tabella, sempre che sia presente un identity nell'oggetto;
" un resultset relativo alla eventuale presenza di tipi rowGuid nella tabella (nell'esempio specifico non vi sono colonne di questo tipo);
" un resultset relativo al partizionamento fisico dei dati della tabella, utile qualora la tabella risulti partizionata;
" un resultset relativo agli indici definiti su questa tabella, corrispondente alle informazioni accessibili attraverso la tabella di sistema
sysindexes o alla stored procedure specifica di sistema sp_helpindex;
" un resultset realtivo alle constraint presenti nella tabella (in effetti, nel caso specifico, possibile osservare una constraint di tipo
check sul campo Sesso, che pu assumere isoli valori 'S' o 'N', una
constraint di tipo chiave primaria di tipo CLUSTERED sul campo IdSoggetto e una constraint relativa ad una foreign key del campo LocalitaNascita sul campo chiave Localita della tabella Localita dello
stesso database Libro);
" infine, un resultset che indica se la tabella usate in una o pi viste e le informazioni di dettaglio relative (nessuna informazione di
questo tipo viene restituita nell'esempio perch la tabella non viene adoperata in nessuna vista).
3.3.2 sp_depends
Questa stored procedure molto utile perch permette di verificare le
dipendenze tra tabelle, viste, trigger e stored procedure del database e
pertanto risulta molto utile quando si deve realizzare un'analisi d'impatto di una modifica di un qualsiasi oggetto del database.
Proviamo ad esaminare le dipendenze della tabella Person.Contact del
database di esempio AdventureWorks:
USE AdventureWorks
GO
118
Capitolo 3 (103-128):007-050
Capitolo 3
29-10-2007
16:05
Pagina 119
SQL SERVER
ASPETTI
AVANZATI
sp_depends 'Person.Contact'
GO
SQL Server ci moster l'elenco di tutti gli oggetti del database dipendenti da quello passato come argomento, precisandone, per ciascuno di
essi, il nome e la tipologia (tabella, stored procedure, funzione, ecc...),
come mostrato in Figura 3.13.
Figura 3.13: Analisi delle dipendenze tra oggetti con sp_depends
3.3.3 sp_helptext
Questa comoda stored procedure si limita a resituire un'informazione molto utile: il corpo testuale degli oggetti definiti interamente in T-SQL e cio stored procedure, viste, trigger e funzioni.
Si passa il nome dell'oggetto da esaminare come argomento e
la stored procedure restituisce un resultset in cui ciascuna riga
contiene una linea del corpo del T-SQL dell'oggetto, suddiviso per
line break.
Si osservi il seguente esempio:
119
Capitolo 3 (103-128):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:05
Pagina 120
Capitolo 3
USE AdventureWorks
GO
sp_helptext 'usp_LookupConversionRate'
GO
Nel caso specifico sar richiesto di esaminare il corpo della stored procedure usp_LookupConversionRate. Il risultato sar molto utile e persino decorativo, come mostrato in Figura 3.14.
Figura 3.14: Come ottenere un output formattato del corpo degli oggetti
definiti in T-SQL puro
3.4 FUNZIONI SCALARI

PREDEFINITE
SQL Server fornisce numerose funzioni scalari che possono essere utilizzate all'interno di query, di stored procedure e di funzioni utente per
costruire e restituire valori scalari. Esistono centinaia di queste funzioni built-in in SQL Server 2005, ma fondamentale possiamo suddividerle nei seguenti gruppi logici:
120
Capitolo 3 (103-128):007-050
29-10-2007
Capitolo 3
G
G
G
G
G
16:05
Pagina 121
SQL SERVER
ASPETTI
AVANZATI
" funzioni numeriche;

" funzioni di data;
" funzioni di stringa;
" funzioni di testo/immagine;
" funzioni di sistema.
Le funzioni scalari di sistema vanno usate esattamente come le funzioni scalari definite dall'utente. Si osservi il seguente uso all'interno di
una banale query sulla tabella Sales.SalesOrderDetail di Northwind:
SELECT
GetDate() AS DataCorrente,
FLOOR(UnitPrice) AS FlooredPrice, UnitPrice,
SUBSTRING(CarrierTrackingNumber, 6, 4) AS PartialTrackingNumber,
CarrierTrackingNumber
FROM Sales.SalesOrderDetail
WHERE SalesOrderId = 43661
Si pu osservare l'uso di diverse funzioni di sistema, con o senza parametri da passare come argomento delle stesse. In particolare sono state
adoperate la funzione GetDate per ottenere la data corrente dal sistema, la funzione FLOOR per ottenere l'arrotondamento per eccesso di un
numero decimale e la funzione SUBSTRING che restituisce una sottostringa di una stringa pi ampia. In Figura 3.15 si pu osservare il resultset generato dalla query di esempio.
Figura 3.15: Esempio di uso di funzioni scalari predefinite in una query

121
Capitolo 3 (103-128):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:05
Pagina 122
Capitolo 3
3.4.1 Funzioni numeriche

Le funzioni numeriche sono funzioni matematiche finalizzate al calcolo e alla modifica di valori numerici. Di seguito se ne riportano le pi rilevanti:
FUNZIONE
ABS(n)
DESCRIZIONE
Restituisce il valore assoluto (positivo) dell'espressione
numerica specificata.
ACOS(n)
Restituisce l'angolo, espresso in radianti, il cui coseno

corrisponde all'espressione di tipo float specificata
(denominato anche arcocoseno).
ASIN(n)
Restituisce l'angolo, espresso in radianti, il cui seno

corrisponde all'espressione float specificata. Il valore
restituito viene definito anche arcoseno.
ATAN(n)
Restituisce l'angolo, espresso in radianti, la cui tangente

corrisponde all'espressione di tipo float specificata (definito
anche arcotangente).
ATN2(n, m)
Restituisce l'angolo, in radianti, tra l'asse X positivo e il

raggio dall'origine al punto (x, y), dove x e y sono i valori
delle due espressioni float specificate.
CEILING(n)
Restituisce il pi piccolo valore integer maggiore o uguale

all'espressione numerica specificata.
COS(n)
Restituisce il coseno dell'angolo specificato, espresso in

radianti, nell'espressione specificata.
COT(n)
Restituisce la cotangente dell'angolo specificato, espressa in

radianti, nell'espressione float specificata.
DEGREES(n) Restituisce l'angolo in gradi corrispondente all'angolo

specificato in radianti.
EXP(n)
Restituisce il valore esponenziale dell'espressione float

specificata.
FLOOR(n)
Restituisce il valore integer maggiore che risulta minore o

uguale all'espressione numerica specificata.
LOG(n)
Restituisce il logaritmo naturale dell'espressione float

specificata.
122
Capitolo 3 (103-128):007-050
29-10-2007
Capitolo 3
FUNZIONE
LOG10(n)
16:05
Pagina 123
SQL SERVER
ASPETTI
AVANZATI
DESCRIZIONE
Restituisce il logaritmo in base 10 dell'espressione float
specificata.
PI()
Restituisce il valore della costante pi greco.
POWER(n, y) Restituisce il valore dell'espressione specificata elevato alla

potenza indicata y.
RADIANS(n) Restituisce l'equivalente in radianti dell'espressione numerica
specificata espressa in gradi.
RAND(n)
Restituisce un valore float casuale compreso tra 0 e 1.
ROUND(n,
Restituisce un valore numerico arrotondato alla lunghezza o
len, tipo)
alla precisione specificata. Il tipo indica il formato di

conversione e pu assumere i valori tinyint, smallint o int.
SIGN(n)
Restituisce il segno positivo (+1), zero (0) o il segno negativo

(-1) dell'espressione specificata.
SIN(n)
Restituisce il seno dell'angolo specificato, espresso in

radianti, in un'espressione numerica approssimata di tipo
float.
SQRT(n)
Restituisce la radice quadrata del valore float specificato.
SQUARE(n)
Restituisce il quadrato del valore float specificato.
TAN(n)
Restituisce la tangente dell'espressione di input.
3.4.2 Funzioni di data

Le funzioni di data, come evidentemente si evince dal loro nome, agiscono sulle date e sulle ore calcolandone parti o interi e restituendo a
loro volta date o intervalli temporali. Esse agiscono su intervalli temporali prestabiliti e che sono riassunti di seguito:
yy (anno);
qq (trimestre);
mm (mese);
dy (giorno dell'anno, da 1 a 366);
dd (giorno);
dw (giorno della settimana);
wk (settimana);
123
Capitolo 3 (103-128):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:05
Pagina 124
Capitolo 3
hh (ora);
mi (minuti);
ss (secondi;
ms (millisecondi).
Di seguito se ne riportano le pi rilevanti:
FUNZIONE
DESCRIZIONE
Restituisce la data e l'ora di sistema correnti nel formato

interno standard di SQL Server 2005 per i valori datetime.
Restituisce un valore integer che rappresenta la parte
DATEPART
specificata della data indicata. L'argomento datepart pu
(datepart,
essere del tipo descritto all'inizio del paragrafo.
data)
DATEANAME Restituisce una stringa di caratteri che rappresenta la parte
specificata della data indicata. applicabile solo a quei
(datepart,
datepart che hanno un nome simbolico come ad esempio i
data)
nomi dei mesi e i giorni della settimana.
Restituisce il numero di limiti di data e ora che si
DATEDIFF
sovrappongono tra due date specificate.
(datepart,
data_inizio,
data_fine)
Restituisce un nuovo valore datetime basato sull'aggiunta di
DATEADD
un incremento alla data specificata.
(datepart,
incremento,
data)
GETDATE()
Funzioni di stringa
Le funzioni di stringa manipolano stringhe effettuandone modifiche,
concatenazioni e trasformazioni e restituendo le stringe risultati. Di seguito se ne riportano le pi rilevanti:
FUNZIONE
DESCRIZIONE
ASCII(caratte Restituisce il codice ASCII corrispondente al primo carattere a

re)
124
sinistra in una stringa di caratteri.
Capitolo 3 (103-128):007-050
29-10-2007
Capitolo 3
FUNZIONE
16:05
Pagina 125
SQL SERVER
ASPETTI
AVANZATI
DESCRIZIONE
CHAR(codice Converte un codice ASCII di tipo int in un carattere.

_numerico)
CHARINDEX( Restituisce il punto iniziale dell'espressione specificata in una
stringa di caratteri. expr1 include la sequenza di caratteri che
expr1,
expr2[,
si desidera trovare. L'espressione expr2, in genere una
loc_partenza colonna, in cui viene eseguita la ricerca della sequenza

specificata. Loc_partenza indica la posizione da cui iniziare la
])
ricerca di expression1 in expression2. Se loc_partenza viene
omesso, un numero negativo oppure uguale a zero.
DIFFERENCE Restituisce un valore integer che indica la differenza tra i
valori SOUNDEX di due espressioni di caratteri.
(expr1,
expr2)
LOWER
Restituisce un'espressione di caratteri dopo aver convertito i
(expr)
caratteri maiuscoli in caratteri minuscoli.
LTRIM(expr) Restituisce una espressione di caratteri dopo aver rimosso gli

spazi vuoti iniziali.
NEWID()
Crea un valore univoco di tipo uniqueidentifier.
REPLICATE(e Ripete un valore stringa il numero di volte specificato.

xpr, numrip)
REVERSE(exp Restituisce un'espressione di caratteri nell'ordine inverso.
r)
RIGHT(expr) Restituisce la parte finale di una stringa di caratteri, di
lunghezza pari al numero di caratteri specificato.
RTRIM(expr) Restituisce una espressione di caratteri dopo aver rimosso gli
spazi vuoti finali.
SOUNDEX(ex Restituisce un codice di quattro caratteri (SOUNDEX) per
valutare la similarit di due stringhe.
pr)
SPACE(nume Restituisce una stringa di spazi ripetuti.
ro_spazi)
STR(numero, Restituisce dati di tipo carattere convertiti da dati di tipo
numerico. I parametri lunghezza e decimali indicano
lunghezza
[,decimali])
rispettivamente il numero di cifre per rappresentare la parte

intera e il numero di cifre decimali.
125
Capitolo 3 (103-128):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:05
Pagina 126
FUNZIONE
STUFF(expr,
inizio, fine,
lunghezza,
expr2)
SUBSTRING(
expr, inizio,
fine)
UPPER
Capitolo 3
DESCRIZIONE
Elimina un determinato numero di caratteri nella stringa expr
e inserisce un altro set di caratteri a partire dal punto
specificato nella stringa expr2.
Restituisce parte di un'espressione di tipo carattere, binario,
testo o immagine.
Restituisce un'espressione di caratteri dopo aver convertito i
caratteri maiuscoli in caratteri maiscoli.
3.4.3 Funzioni di testo/immagine

Queste colonne agiscono su colonne di tipo testo/immagine. Di seguito se ne riportano le pi rilevanti:
FUNZIONE
PATINDEX
('%pattern%
',
espressione)
TEXTPTR
(colonna)
DESCRIZIONE
Restituisce la posizione di inizio della prima occorrenza di un
criterio di ricerca in un'espressione specificata, oppure zero se
il criterio di ricerca non viene trovato, in tutti i dati di tipo
carattere e text validi.
Restituisce il valore del puntatore di testo corrispondente a
una colonna di tipo text, ntext o image in formato varbinary.
possibile utilizzare il valore del puntatore di testo
recuperato nelle istruzioni READTEXT, WRITETEXT e
UPDATETEXT.
3.4.4 Funzioni di sistema

Queste funzioni sono da ritenersi un'estensione dell'argomento precedente relativo agli oggetti e al catalogo di sistema. Ciascun oggetto di
sistema identificato da un codice numero univoco nell'ambito del database; le funzioni di sistema utilizzano proprio questi id per identificare ed effettuare ricerche su singoli oggetti. Di seguito se ne riportano le
pi rilevanti:
126
Capitolo 3 (103-128):007-050
Capitolo 3
FUNZIONE
COLAESCE(e
xpr, n)
COL_LENGT
H ('tabella',
'colonna')
COL_NAME
(idtabella,
idcolonna)
DATALENGT
H(expr)
GETANSINUL
L([
'database' ] )
NULLIF(expr
1, expr2)
29-10-2007
16:05
Pagina 127
SQL SERVER
ASPETTI
AVANZATI
DESCRIZIONE
Restituisce la prima espressione non Null tra i relativi
argomenti.
Restituisce la lunghezza definita di una colonna, espressa in
byte.
Restituisce il nome di una colonna corrispondente al numero
di identificazione di tabella e al numero di identificazione di
colonna specificati.
Restituisce il numero di byte utilizzati per rappresentare
un'espressione.
Restituisce l'impostazione predefinita relativa al supporto dei
valori Null del database per la sessione corrente.
Restituisce un valore Null se le due espressioni specificate
sono uguali.
127
Capitolo 3 (103-128):007-050
29-10-2007
16:05
Pagina 128
Capitolo 4 (129-150):007-050
Capitolo 4
29-10-2007
16:19
Pagina 129
SQL SERVER
ASPETTI
AVANZATI
NUOVE FUNZIONALIT
IN SQL SERVER 2005
Quest'ultimo capitolo non presenta una coerenza e continuit di argomenti, ma si pone quasi come una sorta di raccolta di appendici
che richiamano alcune delle pi interessanti novit di SQL Server
2005 che non stato possibile affrontare direttamente nel corso del
volume. Pertanto possibile leggere il capitolo anche non in modo
lineare, ma concentrandosi solo sui paragrafi di interesse e nell'ordine che si preferisce.
4.1 COLONNE CALCOLATE

PERSISTENTI
Le query SQL sono piene di campi calcolati, che sono una funzionalit molto comoda ma spesso piuttosto onerosa per il database engine. Soprattutto perch a volte quasi inevitabile riproporre sempre gli stessi campi ad ogni interrogazione sulla stessa tabella. Un esempio pu essere il campo calcolato TotaleRiga, di una riga d'ordine, che
per effetto delle regole di normalizzazione non pu essere inserito come campo fisico, ma deve essere sempre calcolato come PrezzoUnitario * Quantita. SQL Server, per, offre la possibilit di definire i
campi calcolati nella creazione di una tabella, in modo che possa
essere utilizzata nelle interrogazioni come un normale campo fisico, ma il suo valore viene calcolato al volo ogni volta. Osserviamo un
esempio:
CREATE TABLE RigheOrdine
(
CodRiga INT,
CodOrdine INT,
CodProdotto INT,
129
Capitolo 4 (129-150):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:19
Pagina 130
Capitolo 4
PrezzoUnitario FLOAT,
Quantita INT,
TotaleRiga AS PrezzoUnitario * Quantita
)
Questo era gi possibile con SQL Server 2000, ma l'approccio ha un

grosso limite: le colonne cos definite, per la loro natura volatile non
sono indicizzabili, costringendo cos ad un table scan ogni volta che
se ne interroga il valore in una WHERE. SQL Server 2005 introduce
le colonne calcolate persistenti che consentono di salvare fisicamente nel database il risultato del calcolo, alla stregua di un normale
campo fisico non calcolato, ma con il vantaggio di venire aggiornato in automatico dal sistema ogni volta che cambia il suo valore per
effetto della variazione dei valori di uno o pi campi che compongono la sua espressione di calcolo. Questi campi sono indicizzabili e
dunque non offrono svantaggi prestazionali come in passato. Osserviamo la sintassi DML di definizione di un campo calcolato persistente:
CREATE TABLE RigheOrdine
(
CodRiga INT,
CodOrdine INT,
CodProdotto INT,
PrezzoUnitario FLOAT,
Quantita INT,
TotaleRiga AS PrezzoUnitario * Quantita PERSISTED
)
4.2 M.A.R.S.
(MULTIPLE ACTIVE RESULT SET)
La fruizione dei dati di un database possibile in ADO.NET abbina130
Capitolo 4 (129-150):007-050
Capitolo 4
29-10-2007
16:19
Pagina 131
SQL SERVER
ASPETTI
AVANZATI
to a SQL Server in diversi modi. Tra le tante novit che questa nuova versione di SQL Server introduce, ce n' una estremamente comoda chiamata MARS: Multiple Active Result Set. Essa funziona in abbinamento alla piattaforma .NET 2.0 e in particolare alla libreria di
accesso ai dati ADO.NET 2.0.
Quando abbiamo bisogno di accedere a dei dati in modo veloce e leggero non possiamo che optare per il DataReader che consente di leggere un flusso di dati ricevuto da un database in modalit read-only,
forward-only. Esso impone che la connessione al database venga
mantenuta aperta finch tutti i dati non sono stati letti. Fino alla
versione ADO.NET 1.1 e Microsoft SQL Server 2000 questo model lo
di programmazione introduceva una limitazione: limpossibilit di
eseguire altre operazioni sul database finch non si liberata la connessione, pena il sollevamento dell'eccezione che indicava la preesistenza di un DataReader gi aperto associato alla connessione
corrente. Non era possibile aprire altri DataReader nella stessa connessione prima che questo non venisse chiuso. Osserviamo il seguente esempio: facendo riferimento al solito database di esempio
Northwind, consideriamo la tabella Orders (ordini) e le sue righe Order Details. Scorrendo la lista di tutti gli ordini, per ciascun ordine vogliamo determinare il valore dell'ordinato e cio la somma del valore unitario di tutte le righe moltiplicato per la quantit ordinata per
ciascuna riga.
L'algoritmo pi intuitivo prevede che:
G
G
si leggano le testate dalla tabella Orders;

per ciascun ordine, usando la chiave primaria OrderId, si esegua
una query che sommi il valore delle righe di ciascun ordine (quantit ordinata x valore unitario).
Per compiere questa operazione dobbiamo necessariamente eseguire due comandi T-SQL distinti sul database, ma sempre sulla stessa connessione. Cosa che, come abbiamo visto, non potremmo fare
131
Capitolo 4 (129-150):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:19
Pagina 132
Capitolo 4
con il DataReader.
Il Multiple Active Resultset Set ci viene in aiuto perch ci permette di
eseguire comandi distinti attraverso la stessa connessione. Osserviamo il seguente esempio in C# (.NET 2.0):
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
namespace MARS {
class Program {
static void Main( string[] args ) {
SqlConnection myConnection = new SqlConnection();
//Va impostato il parametro MultipleActiveResultSets sulla stringa
di connessione
myConnection.ConnectionString = "Data Source=(local);Integrated
Security=SSPI;Persist Security Info=False;Initial
Catalog=Northwind;MultipleActiveResultSets=True";
SqlCommand myCommand = myConnection.CreateCommand();
myCommand.CommandType = CommandType.Text;
myCommand.CommandText = "Select OrderId from Orders";
myConnection.Open();
SqlDataReader myDataReader = myCommand.ExecuteReader();
while ( myDataReader.Read() ) {
SqlCommand mySecondCommand =
myConnection.CreateCommand();
mySecondCommand.CommandType = CommandType.Text;
mySecondCommand.CommandText = "Select cast(Sum(UnitPrice
* Quantity) as float) as Venduto from [Order Details] where OrderId =
@OrderId";
SqlParameter parameter =
132
Capitolo 4 (129-150):007-050
Capitolo 4
29-10-2007
16:19
Pagina 133
SQL SERVER
ASPETTI
AVANZATI
mySecondCommand.CreateParameter();
parameter.ParameterName = "@OrderId";
parameter.Value = (int)myDataReader["OrderId"];
mySecondCommand.Parameters.Add( parameter );
//Eseguo il secondo comando sulla stessa connessione
double Ordinato = (double)mySecondCommand.ExecuteScalar();
Console.WriteLine( "Order Id: {0}\r\nTotale Ordinato: {1}\r\n----\r\n", myDataReader["OrderId"].ToString(), Ordinato.ToString() );
}
myConnection.Close();
}
}
}
Per sfruttare MARS la connectionstring deve essere predisposta impostando il parametro MultipleActiveResultSets = true nella stringa
di connessione. La cosa va fatta solo per le connessioni che realmente necessitano di questa funzionalit, pena un decadimento delle prestazioni.
A questo punto viene prima eseguita una selezione su tutti i record
della tabella Orders il cui risultato un DataReader. Eseguendo un
ciclo su tutti i record, viene recuperato lOrderId che viene passato
alla seconda query che effettua il conteggio dei quantitativi venduti. Impostare a true il parametro MultipleActiveResultSets nella stringa di connessione anche quando non serve, potrebbe avere impatti negativi sul fronte delle prestazione, dunque il suo uso va attentamente ponderato. Tuttavia la sua introduzione, ove serva, fornisce
un'estrema semplificazione del codice di programmazione.
4.3 SUPPORTO MIGLIORATO

AL FORMATO XML
Il supporto al formato XML in SQL Server stato introdotto gi dalI libri di ioPROGRAMMO/SQL Server aspetti avanzati
133
Capitolo 4 (129-150):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:19
Pagina 134
Capitolo 4
la precedente versione 2000 grazie alla clausola FOR XML che consentiva (e consente) di restituire un resultset in formato XML oppure utilizzando OPENXML. SQL Server 2005 estende grandemente il
suo supporto a questo formato introducendo l'implementazione del
nuovo tipo di dati XML. Vi erano in passato per una serie di limitazioni: ad esempio, per memorizzare interamente un file XML nel database, era necessario inserire il contenuto in campi text o nei campi BLOB perdendo le potenzialit del formato XML. Con SQL Server
2005, invece, adesso abbiamo un tipo di dati nativo XML ovvero
possiamo utilizzarlo come campo di una tabella o come variabile di
input/output per stored procedure e funzioni. Dunque, all'interno
del campo XML, si potranno inserire direttamente interi documenti
o anche solo porzioni well-formed. Tale condizione sar direttamente verificata dal parser xml interno di SQL Server 2005 che ne segnaler l'errore analogamente a quanto viene quanto avviene con
gli invalid cast. Quindi se proviamo ad inserire nel database un file
non well-formed sar direttamente SQL Server che ci avviser che l'operazione non possibile e quindi la INSERT o un UPDATE che vadano a modificare non avr esecuzione. Al fine di meglio comprendere questa funzionalit, procediamo con un semplice esempio. Costruiamo una semplice tabella che contiene un campo di tipo XML:
CREATE TABLE TabellaEsempio
(
IdRec int IDENTITY(1,1) NOT NULL,
ValoreXML XML NULL
)
La tabella, a parte la solita chiave primaria identity, contiene un campo ValoreXML del nuovo data type XML. Siamo dunque pronti ad
inserire un record in questa tabella:
INSERT INTO TabellaEsempio
134
Capitolo 4 (129-150):007-050
Capitolo 4
29-10-2007
16:19
Pagina 135
SQL SERVER
ASPETTI
AVANZATI
(ValoreXML)
VALUES('<?xml version="1.0" encoding="utf-8" ?>
<Indice>
<Capitolo>
<Titolo>Cenni sul database relazionale</Titolo>
<Sommario>Introduzione e storia del modello
relazionale</Sommario>
<Pagine>25</Pagine>
</Capitolo>
<Capitolo>
<Titolo>Installazione e amministrazione di SQL Server
2005</Titolo>
<Sommario>La parte piu noiosa da scrivere di tutto i
volume</Sommario>
<Pagine>50</Pagine>
</Capitolo>
</Indice>')
Il documento XML di esempio rappresenta l'estratto dell'indice del

volume che state leggendo. Esso contiene un nodo principale Indice e diversi sottonodi Capitolo. Ciascun Capitolo costituito da un
nodo Titolo, da un nodo Sommario ed da un nodo Pagine (il numero di pagine del capitolo). Il documento XML corretto nel senso
che well formed ( sintatticamente corretto, ad esempio tutti i nodi di aprono e si chudono correttamente, non ci sono caratteri non
consentiti, esiste un solo nodo radice, ecc...). Infatti SQL Server non
segnala alcun errore e consente l'inserimento del record. Proviamo
ad effettuare una piccola modifica all'xml da inserire, magari usando un carattere non consentito. Si osservi il seguente documento
XML errato:
<?xml version="1.0" encoding="utf-8" ?>
<Indice>
135
Capitolo 4 (129-150):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:19
Pagina 136
Capitolo 4
<Capitolo>
<Titolo>Funzionalit nuove a avanzate di SQL Server 2005</Titolo>
<Sommario>Il capitolo corrente</Sommario>
<Pagine>25</Pagine>
</Capitolo>
</Indice>
La lettera di Funzionalit accentata e quindi non fa parte dell'alfabeto UTF-8, come descritto nel preambolo del documento. SQL
Server ci segnaler un inequivocabile:
XML parsing: line 10, character 22, illegal xml character
Per eseguire un'interrogazione su tabelle contenenti campi di tipo XML
possibile con la vecchia e rassicurante SELECT:
SELECT IdRec, ValoreXML FROM TabellaEsempio
Oppure utilizzare la vecchia clausola FOR XML per ritornare in forma XML l'intera riga, compreso il valore scalare tradizionale IdRec:
SELECT IdRec, ValoreXML FROM TabellaEsempio FOR XML AUTO
La Figura 4.1 ci mostra propria la resa nella griglia di SQL Server
Management Studio delle due query. Cliccando sul contenuto del
campo ValoreXML, Management Studio apre un nuovo documento,
questa volta di tipo XML, in cui mostra il contenuto XML del campo
con un layout simile a quello usato dai browser per rappresentare i
Figura 4.1: Un resultset XML nella griglia.
136
Capitolo 4 (129-150):007-050
Capitolo 4
29-10-2007
16:19
Pagina 137
SQL SERVER
ASPETTI
AVANZATI
Figura 4.2: Il contenuto di un campo di tipo XML
documenti di questo formato, come si evince dalla figura 4.2.
4.3.1 XPath e XQuery

Il linguaggio SQL nato per trattare nativamente i dati relazionali,
ma forse meno adeguato per formati strutturati e gerarchici come
XML. Per interrogare questo formato esistono da anni diversi linguaggi. Il pi diffuso XPath che alla base di quasi tutti i DOM
XML attualmente in circolazione.
Attualmente, per, si sta imponendo un nuovo e pi efficace standard: XQuery. Lo standard non ancora definitivo, ma Microsoft,
che fa parte del gruppo tecnico di definizione dello standard in seno al W3C, ne ha gi implementato una versione draft in SQL Server
2005. XQuery utilizza una serie di istruzioni che possono essere raccolte ed identificate con la sigla FLOWR (For Let Order_By Where
Return) dove ogni lettera corrisponde alla lettera iniziale di ogni
istruzione.
Proviamo a realizzare alcuni esempi di query, ma prima di fare questo dobbiamo preparare la nostra precedente tabella di esempio per
ospitare un numero pi consistente di record sui quali effettuare
l'interrogazione. Eseguiamo tre INSERT:
(ValoreXML)
<Indice>
<Capitolo>
<Titolo>Cenni sul database relazionale</Titolo>
137
Capitolo 4 (129-150):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:19
Pagina 138
Capitolo 4
<Sommario>Introduzione e storia del modello relazionale</Sommario>

<Pagine>40</Pagine>
</Capitolo>
</Indice>')
(ValoreXML)
<Indice>
<Capitolo>
<Titolo>Il linguaggio Transact-SQL</Titolo>
<Sommario>SQL per tutti</Sommario>
<Pagine>70</Pagine>
</Capitolo>
</Indice>')
(ValoreXML)
<Indice>
<Capitolo>
<Titolo>Installazione e amministrazione di SQL Server 2005</Titolo>
<Sommario>La parte piu noiosa da scrivere di tutto i volume</Sommario>
<Pagine>50</Pagine>
</Capitolo>
</Indice>')
Abbiamo introdotto tre righe con altrettanti documenti XML (Capitoli, nel nostro esempio) nella tabella. Con la seguente query XPath
vogliamo estrarre i soli capitoli che hanno pi di 40 pagine e cio che
il contenuto del sottonodo Pagine sia maggiore di 40:
SELECT IdRec, ValoreXML.query('/Indice/Capitolo[Pagine>40]')
138
Capitolo 4 (129-150):007-050
Capitolo 4
29-10-2007
16:19
Pagina 139
SQL SERVER
ASPETTI
AVANZATI
FROM TabellaEsempio
La query contenuta nella propriet .query del campo ValoreXML che

di tipo XML. In Figura 3.3 possibile osservare il risultato della
query nel Query Editor.
Figura 4.3: Una query XPath
Riscriviamo l'analoga query nella nuova sintassi XQuery, pi versatile e potente:

SELECT IdRec, ValoreXML.query(
'for $a in /Indice/Capitolo
where $a/Pagine>40
order by $a
return $a')
FROM TabellaEsempio
Il risultato esattamente lo stesso ottenuto con la query XPath.

Anche l'interpretazione della query la stessa: la clausola for dice di cercare in tutti i sottonodi della gerarchia Indice/Capitolo,
la where dice di cercare tutti i sottonodi Pagine che hanno valore maggiore di 40, la clausola where di restituire per intero
quanto trovato ( possibile infatti restituirne anche dei sottonodi). L'unica apparente differenza nella clausola group by
che consente di restituire il risultato ordinato. In realt Xquery
estramente pi potente e complesso di quanto appaia nell'eI libri di ioPROGRAMMO/SQL Server aspetti avanzati
139
Capitolo 4 (129-150):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:19
Pagina 140
Capitolo 4
sempio, ma lo studio della sua sintassi non argomento di questo volume. Lo scopo, invece, era solo mostrare il livello di sofisticazione che ha raggiunto il supporto al formato XML in SQL
Server 2005, tanto da far stare stretta a questo prodotto la definizione di mero RDMS.
4.3.2 Modificare i dati dei campi XML

XQuery consente anche di effetturare modifiche al contenuto dei
campi XML. Immaginiamo di voler introdurre un attributo Completato a ciascun nodo Capitolo, preimpostando il valore a NO.
Osserviamo la seguente istruzione di UPDATE:
UPDATE TabellaEsempio
SET ValoreXML.modify('insert attribute Completato {"NO"}
into(/Indice[1])')
Questa istruzione consente di aggiungere un attributo Completato

nel nodo radice Indice. Otterremo cos, ad esempio:
<Indice Completato="NO">
<Capitolo>
<Titolo>Il linguaggio Transact-SQL</Titolo>
<Sommario>SQL per tutti</Sommario>
<Pagine>70</Pagine>
</Capitolo>
</Indice>
XQuery offre anche la possibile di modificare i valori oltre che inserirne di nuovi, ma si rimanda alla documentazione specifica sull'argomento per approfondimenti.
140
Capitolo 4 (129-150):007-050
Capitolo 4
29-10-2007
16:19
Pagina 141
SQL SERVER
ASPETTI
AVANZATI
4.4 NOVIT NELLA GESTIONE

DELLA SICUREZZA
SQL Server 2005 introduce un modello di gestione della sicurezza completamente rivisitato. Osserviamone gli aspetti salienti. Un nuovo
approccio per lo sviluppo del software stato avviato con l'iniziativa Trustworthy Computing varata nei primi mesi del 2002 da parte
di Microsoft. Essa si resa necessaria a causa del crescente numero di exploit diretto al sistema operativo Microsoft e alle applicazioni, che ha portato il produttore a progettare e rilasciare prodotti sicuri per default, e cio gi inattaccabili con le sole impostazioni predefinite e senza che si dovesse procedere a complesse o esoteriche
configurazione. Infatti, l'argomento principale usato dal produttore
a propria difesa in questo ambito, stato sempre quello che i suoi
prodotti non fossero poco sicuri in s, ma che nella maggior parte dei
casi si trattasse di problemi di non corretta installazione e tuning
del sistema.
In questo paragrafo discuteremo le caratteristiche relative alla autenticazione (il processo di identificazione che collega i dati di accesso
Figura 4.4: Creazione di una nuova login

141
Capitolo 4 (129-150):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:19
Pagina 142
Capitolo 4
a SQL Server e utenti che accedono a basi di dati), e proseguir con

l'autorizzazione (determinare il livello delle autorizzazioni concesse una volta che la connessione iniziale stabilita).
Il primo dato interessante che l'installazione tipica del prodotto, quella cio eseguita senza specificare alcuna indicazione aggiuntiva rispetto a quelle proposte di default, evita l'installazione o la non attivazione di componenti essenziali e di impostazioni che possono
esporre il server e dei suoi dati a potenziali attacchi. Tale impostazione applicata ai principali componenti del server applicativo e cio
SQL Server Agent, l'indicizzazione Full Text Search, e Data Transformation Services, Analysis, Reporting, e Notification Services, SQL
Browser, Service Broker di connettivit di rete, Database Mirroring,
SQLMail, o SQL Debugging.
Figura 4.5: Elenco delle login esistenti
Proprio come i suoi predecessori, SQL Server 2005 supporta sia la modalit di autenticazione mista che quella Windows. In Windows
Authentication Mode, garantito l'accesso basato su un token di
142
Capitolo 4 (129-150):007-050
Capitolo 4
29-10-2007
16:19
Pagina 143
SQL SERVER
ASPETTI
AVANZATI
protezione assegnato durante l'autenticazione effettuata correttamente al dominio o al server locale da parte di un account Windows.
In Active Directory disponibile un ulteriore livello di protezione fornito dal protocollo Kerberos. Sebbene l'autenticazione basata su
Windows sia intrinsecamente pi sicura di quella offerta dalla modalit mista, la sicurezza di SQL Server basato login migliorata attraverso la sua cifratura con certificati.
Figura 4.6: Elenco degli utenti
Tra le altre novit vi la capacit di impostare e gestire alcune regole per la corretta definizione degli account e delle password per le login di SQL Server in modalit nativa. Questo permette di far rispettare tali limiti sulla password quali la complessit, la scadenza della password, e il blocco degli account. Le regole di complessit delle password possono essere riassunte come di seguito:
G la lunghezza della password deve essere di almeno 6 caratteri (in
generale, le password di SQL Server pu avere tra 1 e 128 caratteri);
143
Capitolo 4 (129-150):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:19
Pagina 144
Capitolo 4
G la password deve contenere almeno tre dei quattro tipi diversi di
caratteri quali lettere maiuscole, lettere minuscole, numeri e caratteri non alfanumerici;
G la password deve differire dalle seguenti stringhe: admin, administrator, password, sa, sysadmin, il nome dell'host di hosting
calcolare l'installazione di SQL Server, e in tutto o in parte e l'impossibilit che la password ricordi da vicino la login dell'utente
correntemente connesso a Windows.
Si osservi che, indipendentemente dalla modalit di autenticazione
e dalle policy, l'installazione guidata non consente password non
vuote sull'utente SA. Inoltre vi sono delle forti restrizioni anche nella definizione della password. Pertanto si pu affermare che le impostazioni di sicurezza possibili al momento dell'installazione sono
persino pi restrittive di quanto SQL Server gi consenta di fare. Lo
scopo chiaro: tutti coloro che installano l'applicazione senza modificare sostanzialmente le impostazioni di default offerte dal setup,
si ritroveranno immediatamente un sistema piuttosto sicuro e meno
esposto a problemi di sicurezza.
possibile utilizzare le clausole CHECK_EXPIRATION e CHECK_POLICY per la creazione di nuove login con la CREATE LOGIN per impostarne o disattivarne la conformit con le policy (con i parametri
ON o OFF) e CHECK_EXPIRATION per le regole di scadenza della
password, mentre CHECK_POLICY determina il livello di complessit
della password.
CREATE LOGIN drevil
WITH
PASSWORD = 'Ch4ngeMe' MUST_CHANGE,
CHECK_EXPIRATION = ON,
CHECK_POLICY = ON
La vista di sistema Sys.sql_logins indica se la policy della password

e la scadenza della password sono state applicate per i dati di acces144
Capitolo 4 (129-150):007-050
Capitolo 4
29-10-2007
16:19
Pagina 145
SQL SERVER
ASPETTI
AVANZATI
so di SQL esistenti. Le stesse informazioni sono disponibili per i singoli account attraverso l'interfaccia grafica di SQL Server Management Studio.
Lo statement ALTER LOGIN supporta la clausola UNLOCK, che ha la
funzione di sbloccare le login di accesso di SQL Server, che sono stati bloccate da ripetuti errori di immissione della password, a seguito della policy che prevede il blocco dell'account dopo un certo nu
Osserviamo un altro tipico script di creazione della login prodotto
Figura 4.7: Informazioni dell'utente
direttamente dalla funzione di produzione degli script sugli oggetti

di database via SQL Server Management Studio:
/****** Oggetto: Login [sa]
Data script: 10/15/2007 23:56:29 ******/
/* For security reasons the login is created disabled and with a random
password. */
145
Capitolo 4 (129-150):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:19
Pagina 146
/****** Oggetto: Login [sa]
Capitolo 4
Data script: 10/15/2007 23:56:29 ******/
CREATE LOGIN [sa] WITH PASSWORD=N'@_U\;r_>q)d_L]_

_M7;0|2_', DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[Italiano], CHECK_EXPIRATION=OFF,
CHECK_POLICY=ON
GO
EXEC sys.sp_addsrvrolemember @loginame = N'sa', @rolename =
N'sysadmin'
GO
ALTER LOGIN [sa] DISABLE
L'utente riportato SA e possiamo osservare come la sua password

sia criptata in modo che, anche venendo non sia facilmente reversibile.
4.4.1 Ruoli di amministrazione e di

database
A ciascun utente sono assegnati i ruoli che consentono di definire le
operazione che l'utente pu effettuare sui dati e sulla struttura del
database. Ecco i principali ruoli amministrativi:
G bulkadmin Sono autorizzate operazioni di inserimento multiplo
di massa
G diskadmin Gestiscono i file sul disco
G dbcreator Creano e modificano database
G processadmin Gestiscono i processi e i job
G serveradmin Configurano il server
G setupadmin Gestiscono la replica
G sysadmin Qualsiasi attivit amministrativa sul database
Ai ruoli amministrativi, che sono trasfersabili perch riferiti al motore di accesso ai dati pi che ai singoli database, si aggiungono
quelli che, invece, si legano a questi ultimi. In realt possibile ap146
Capitolo 4 (129-150):007-050
Capitolo 4
29-10-2007
16:19
Pagina 147
SQL SERVER
ASPETTI
AVANZATI
plicare questi ruoli con granularit molto elevata perch impostabili sui singoli oggetti del database (tabelle, campi, stored procedure,
viste, ecc...).
Eccone i principali:
G db_accessadmin Gestiscono gli utenti del database
G db_backupoperator Possono eseguire le funzioni di backup
G db_datareader Possono esclusivamente visualizzare i dati
G db_datawriter Possono eseguire INSERT e UPDATE
G db_ddladmin Possono modificare gli oggetti o vincolarli
G db_denydatareader Non possono leggere i dati db_denydataw-
riter Non possono eseguire modifiche ai dati

Grazie a queste caratteristiche, oltre alla creazione personalizzata
di ruoli, possiamo gestire al meglio la sicurezza.
4.4.2 Indicizzazione e ricerca Full Text

SQL Server 7 introduce il supporto nativo alla funzionalit di indicizzazione full text SQL Full-Text Search (SQL FTS). Grazie a tale funzionalit possibile effettuare interrogazioni veloci ed efficienti su
indicizzazioni di testi, ovvero query che si basano su termini chiave,
parti di testo o semplici parole.
La ricerca full-text consente l'indicizzazione per query basate su parole chiave dei dati di testo archiviati in un database di Microsoft
SQL Server. A differenza del predicato LIKE, che supporta solo i modelli di caratteri, le query full-text eseguono ricerche linguistiche su
tali dati, operando su parole e frasi in base alle regole di una lingua
specifica.
possibile creare indici full-text su colonne che contengono dati
char, varchar e nvarchar e su colonne che contengono dati binari
formattati, ad esempio documenti di Microsoft Word, archiviati in
una colonna varbinary(max) o image. Non possibile utilizzare il
predicato LIKE per eseguire query su dati binari formattati.
147
Capitolo 4 (129-150):007-050
SQL SERVER
ASPETTI
AVANZATI
29-10-2007
16:19
Pagina 148
Capitolo 4
Per creare un indice full-text su una tabella, quest'ultima deve contenere una colonna singola, univoca e non Null. Si consideri ad esempio un indice full-text per la tabella Document in Adventure Works,
in cui DocumentID la colonna chiave primaria. L'indice full-text indica che la parola "instructions" la 24 e la 44 nella colonna DocumentSummary della riga associata al valore DocumentID di 3.
Questa struttura di indice supporta una ricerca efficiente di tutti gli
elementi che includono parole indicizzate e operazioni di ricerca
avanzate, ad esempio ricerche di frasi e di prossimit.
Durante l'elaborazione di una query full-text, il motore di ricerca restituisce a SQL Server i valori delle chiavi delle righe che soddisfano
i criteri di ricerca. Se si desidera utilizzare una query full-text per individuare documenti che contengono la parola "instructions", dall'indice full-text si ottengono i valori DocumentID 3, 4, 6, 7 e 8. SQL
Server utilizza quindi tali chiavi per restituire le righe corrispondenti. SQL Server consente linstallazione del servizio Full Text che quindi dovr essere avviato (possiamo verificare lo stato del servizio tramite lapplicazione SQL Server Configuration Manager presente al-
Figura 4.8: SQL Server Configuration Manager
linterno dei Configuration Tools), come mostrato in Figura 4.5.

La funzionalit offerta dal motore di indicizzazione, che si connette alla nostra istanza tramite un provider OLE-DB che si occupa del
popolamento e della gestione degli indici e da un filter daemon co148
Capitolo 4 (129-150):007-050
Capitolo 4
29-10-2007
16:19
Pagina 149
SQL SERVER
ASPETTI
AVANZATI
stituito da componenti responsabili dell'accesso ai dati delle tabelle e della relativa applicazione di filtri e un word breaking, meccanismo in grado di suddividere testo in token basandosi su regole lessicali.
4.4.3 Esempio di interrogazioni full text

Possiamo visualizzare tutti i linguaggi supportati da SQL Server 2005
interrogando la vista di sistema sys.fulltext_languages come:
select * from sys.fulltext_languages
Internamente il motore di indicizzazione costruisce uno o pi indici,
attraverso un processo chiamato popolamento in grado di associare le parole ed i termini con le loro posizioni allinterno dei dati, ed
in grado di comprendere ed estrarre il testo dalle colonne indicizzate.
Per conoscere i tipi di file supportati e disponibili per operazioni di indicizzazione possiamo interrogare la vista di sistema sys.fulltext_document_types, ovvero tramite:
select document_type, path from sys.fulltext_document_types
Con questa interrogazione siamo in grado di conoscere lestensione del file ed il percorso della libreria che contiene il componente
iFilter appropriato.
4.4.4 Attivazione delle funzionalit

Di seguito si riporta la sequenza di operazioni da eseguire per attivare ed utilizzare il motore di ricerca full text:
G abilitare la ricerca per il database (sp_fulltext_database)
G creare il catalogo (sp_fulltext_catalog)
G marcare la tabella per l'indicizzazione fulltext (sp_fulltext_table)
G aggiungere le colonne che si vogliono indicizzare (sp_fulltext_co-
lumn)
G attivare l'indice (sp_fulltext_table)
G popolare l'indice (sp_fulltext_catalog)
149
Capitolo 4 (129-150):007-050
29-10-2007
NOTE
16:19
Pagina 150
Capitolo 4 (129-150):007-050
29-10-2007
NOTE
16:19
Pagina 151
Capitolo 4 (129-150):007-050
29-10-2007
NOTE
16:19
Pagina 152
Capitolo 4 (129-150):007-050
29-10-2007
NOTE
16:19
Pagina 153
Capitolo 4 (129-150):007-050
29-10-2007
NOTE
16:19
Pagina 154
Capitolo 4 (129-150):007-050
29-10-2007
NOTE
16:19
Pagina 155
Capitolo 4 (129-150):007-050
29-10-2007
NOTE
16:19
Pagina 156
Capitolo 4 (129-150):007-050
29-10-2007
NOTE
16:19
Pagina 157
Capitolo 4 (129-150):007-050
29-10-2007
NOTE
16:19
Pagina 158
Capitolo 4 (129-150):007-050
29-10-2007
16:19
Pagina 159
Tamburino:Tamburino
29-10-2007
16:22
Pagina 1
SQL SERVER
ASPETTI AVANZATI
Autore: Vito Vessia
EDITORE
Edizioni Master S.p.A.
Sede di Milano:Via Ariberto, 24 - 20123 Milano
Sede di Rende: C.da Lecco, zona ind. - 87036 Rende (CS)
Realizzazione grafica:
Cromatika Srl
C.da Lecco, zona ind. - 87036 Rende (CS)
Art Director: Paolo Cristiano
Responsabile grafico di progetto: Salvatore Vuono
Coordinatore tecnico: Giancarlo Sicilia
Illustrazioni: Tonino Intieri
Impaginazione elettronica: Francesco Cospite
Servizio Clienti
Tel. 02 831212 - Fax 02 83121206

@ e-mail: customercare@edmaster.it
Stampa: Grafica Editoriale Printing - Bologna

Finito di stampare nel mese di Novembre 2007
Il contenuto di questopera, anche se curato con scrupolosa attenzione, non pu
comportare specifiche responsabilit per involontari errori, inesattezze o uso scorretto. Leditore non si assume alcuna responsabilit per danni diretti o indiretti causati
dallutilizzo delle informazioni contenute nella presente opera. Nomi e marchi
protetti sono citati senza indicare i relativi brevetti. Nessuna parte del testo pu essere in alcun modo riprodotta senza autorizzazione scritta della Edizioni Master.
Copyright 2007 Edizioni Master S.p. A.

Tutti i diritti sono riservati.

SQL Server - Aspetti Avanzati PDF

Caricato da

Copyright:

Formati disponibili

SQL Server - Aspetti Avanzati PDF

Caricato da

Informazioni sul documento

Titolo originale

Copyright

Formati disponibili

Condividi questo documento

Condividi o incorpora il documento

Opzioni di condivisione

Hai trovato utile questo documento?

Questo contenuto è inappropriato?

Copyright:

Formati disponibili

SQL Server - Aspetti Avanzati PDF

Caricato da

Copyright:

Formati disponibili

Copertina:IV-I copertina

DA T-SQL ALLINTEGRAZIONE CON IL FRAMEWORK.NET.

Programmazione con T-SQL