Installazione

Su s.o. server installare l’entreprise edition

Su win2000 prof installare la developer edition.

Infatti l’e.e ,su quest’ultima piattaforma,installa solo i “client tools only” e non un’istanza di sql.

Get di Sistema

Utente autenticato

Es:

Sa per sql server autentication

Renzif per windows autentication

select SYSTEM_USER

Funzioni stringa

CharIndex

Ritorna la posizione di una sottoscringa all’interno di un’altra

SELECT CHARINDEX('der', 'wonderful')

Ritorna 4

Substring

Ritorna la sottostringa a partire da una posizione per una certa lunghezza

SELECT SUBSTRING('abcdef', 2, 3)

Ritorna ‘bcd’

Len

Ritorna il numero di caratteri

Right/Left

Ritorna la sottostringa da destra o sinistra

SELECT LEFT('wonderful', 5)

Ritorna ‘wonde’

Upper/Lower

Converte una stringa in maiuscolo/minuscolo

Str

Converte un numero in stringa

Replace

Serve per cambiare un determinato valore all’interno di una colonna

SELECT REPLACE(colonna,’A’,’a’) as colonna FROM tabella

n.b. e’ una funzione SQL,quindi valida anche per oracle.

Split

Da utlizzare quando si vuole passare un parametro separato da virgole

AND TIPO_CONTRATTO.ID_TIPO_CONTRATTO IN (select * from dbo.Split( @IdTipoContratto,','))

CREATE FUNCTION dbo.Split(@String nvarchar(4000), @Delimiter char(1))

RETURNS @Results TABLE (Items nvarchar(200))

BEGIN

DECLARE @INDEX INT

DECLARE @SLICE nvarchar(4000)

-- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z

-- ERO FIRST TIME IN LOOP

SELECT @INDEX = 1

WHILE @INDEX !=0

BEGIN

-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER

SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)

-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE

IF @INDEX !=0

SELECT @SLICE = LEFT(@STRING,@INDEX - 1)

ELSE

SELECT @SLICE = @STRING

-- PUT THE ITEM INTO THE RESULTS SET

INSERT INTO @Results(Items) VALUES(@SLICE)

-- CHOP THE ITEM REMOVED OFF THE MAIN STRING

SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)

-- BREAK OUT IF WE ARE DONE

IF LEN(@STRING) = 0 BREAK

END

RETURN

End

Funzioni

GetDate

Select GetDate()

Ciclo While

        SET @MESE=1

        WHILE @MESE <= 12

        BEGIN

               SET @MESE=@MESE + 1

END

Case-When

1.CASE WHEN cod_mese = 1 THEN b.codice ELSE 0 END

2.SELECT

'TIPO'=CASE

WHEN UDIENZE.DATA_UDIENZE IS NULL THEN 'TERMINE'

ELSE

'UDIENZA'

END,

'NUM FASCICOLO'=CASE

WHEN SUFF_CONTENZIOSO.DESCR_SUFFISSO IS NULL THEN CONVERT(VARCHAR,CONTENZIOSI.NUMERO_CONTENZIOSO)

ELSE CONVERT(VARCHAR,CONTENZIOSI.NUMERO_CONTENZIOSO) + ' ' + CONVERT(VARCHAR,SUFF_CONTENZIOSO.DESCR_SUFFISSO)

END

3.Da utilizzare anche per condizione l’ORDER BY

CASE WHEN @SortField='DATA' THEN

DATA

END DESC,

CASE WHEN @SortField='CODICE' THEN

primocodice

END

4. Da utilizzare nella WHERE

(case when T.t_stato_autorizzazione='E' then

CONVERT(VARCHAR(10),T.d_esecuzione_lavoro,103)

else

CONVERT(VARCHAR(10),T.d_inserimento_autorizzazione,103)

end)

BETWEEN @From AND @To

IsNull

Serve per filtrare i dati secondo un certo parametro se passato altrimenti non viene considerato.

@CodPre int=null, //dicharazione parametro

AND P.COD_PRENOTAZIONE=ISNULL(@CodPre,P.COD_PRENOTAZIONE)

Convert Datetime

da stringa italiana a datetime

DECLARE @From VARCHAR(10)

SET @From='01/05/2006'

SELECT CONVERT(DATETIME,@From,103)

Coalesce

La funzione ritorna la prima espressione non nulla.

SELECT COALESCE(@p1 * 5,@p2 * 5)

Where In @fld1,@fld2

Per fare la “IN” su un parametro @uo=”ss10s,ss20s” separato da virgole basta sostituire il parametro con

SELECT ‘SS10S’ UNION SELECT ‘SS20S’.

Cosi facendo ottengo una tabella composta da 2 righe SS10S SS20S

IF OBJECT_ID('tempdb..#tmpElencoUO') IS NOT NULL BEGIN

DROP TABLE #tmpElencoUO

END

SET @UO = 'SELECT ''' + REPLACE(@UO,';',''' AS UO UNION SELECT ''') + ''''

CREATE TABLE #tmpElencoUO(CodiceUO VARCHAR(5))

INSERT #tmpElencoUO

EXEC sp_executesql @UO

DROP TABLE #tmpElencoUO

RaisError

IF EXISTS(SELECT * FROM RS_RilevazioniMisure WHERE IdMisura = @IdMisura)

RAISERROR('Non è possibile eliminare la misura perchè è ancora presente una relazione',16,1)

ELSE IF EXISTS(SELECT * FROM RS_Misure WHERE IdMisuraPadre = @IdMisura)

RAISERROR('Non è possibile eliminare la misura perchè è ancora presente una misura figlia',16,1)

ELSE

DELETE RS_MISURE WHERE IDMISURA=@IdMisura

Collate

lacollate si fa solo sui campi stringa

campo1,PC.PRIMOCODICE COLLATE SQL_Latin1_General_CP1_CI_AS

COLLATE IN JOIN

ON PC.PRIMOCODICE COLLATE SQL_Latin1_General_CP1_CI_AS = PCD.PRIMOCODICE

Table

Insert into

Presuppone gia’ l’esistenza della tabella

Insert into Customers2 select * from Customers

Insert into da sp

INSERT INTO table_variable EXEC stored_procedure

Temp table (sessione)

Select * into #Temp from Customers

Viene cancellata ogni volta che l’utente chiude la connessione

Esitenza Temp Table

IF OBJECT_ID('tempdb..#tmpCodiciUO') IS NOT NULL

BEGIN

DROP TABLE #tmpCodiciUO

END

Temp table (applicazione)

Select * into ##Temp from Customers

Viene cancellata appena l’ultimo utente esce dall’applicazione

Truncate Table

Svuota tutta la tabella,metre la Delete puo’ cancellare solo alcune righe.

Corrisponde alla Delete from Tabella senza la clausola “where”

La Delete registra ogni record cancellato nel Transaction Log.

E’ piu performante rispetto alla Delete.

Non puo’ essere usato su tabelle referenziate

Drop table

Distrugge una tabella

Sommarizzare i Dati

Pivotizzazione

Come incrociare(Pivot) un campo in una query

Ho 1 buono da 9 euro

Ho 2 buoni da 9 euro

Ho 1 buono da 7 euro

Select Cognome,Nome,

'Tot9' = case when (buono = 9) THEN

sum(nBuoni)

else

end,

From tabella

Group by

AutenticazioneUtenti.dbo.Rubrica_Pubblica_Storica.NOM_CognomeDipendente,

AutenticazioneUtenti.dbo.Rubrica_Pubblica_Storica.NOM_NomeDipendente,

Buono

Select Cognome,

Nome,

Sum(Tot7) AS Tot7,

SumM(Tot9) AS Tot9

From

(select precedente)

Group by

Cognome, Nome

GroupBy

Columns that are contained in the SELECT clause that are not aggregates MUST be included in the GROUP BY clause

Type	Store	Number
Dog	Miami	12
Cat	Miami	18
Turtle	Tampa	4
Dog	Tampa	14
Cat	Naples	9
Dog	Naples	5
Turtle	Naples	1

Cube

SELECT Type, Store, SUM(Number) as Number
FROM Pets
GROUP BY type,store
WITH CUBE

And the results of the query:

Type	Store	Number
Cat	Miami	18
Cat	Naples	9
Cat	NULL	27
Dog	Miami	12
Dog	Naples	5
Dog	Tampa	14
Dog	NULL	31
Turtle	Naples	1
Turtle	Tampa	4
Turtle	NULL	5
NULL	NULL	63
NULL	Miami	30
NULL	Naples	15
NULL	Tampa	18

Rollup

SELECT Type, Store, SUM(Number) as Number
FROM Pets
GROUP BY type,store
WITH ROLLUP

And the results:

Type	Store	Number
Cat	Miami	18
Cat	Naples	9
Cat	NULL	27
Dog	Miami	12
Dog	Naples	5
Dog	Tampa	14
Dog	NULL	31
Turtle	Naples	1
Turtle	Tampa	4
Turtle	NULL	5
NULL	NULL	63

Transazioni

BEGIN TRANSACTION

INSERT INTO Order VALUES (@ID, @CustomerID, @OrderDate)

IF (@@Error = 0)

BEGIN

INSERT INTO LineItem VALUES (@ItemID, @ID, @ProductID, @Price)

IF (@@Error = 0)

COMMIT TRANSACTION

ELSE

ROLLBACK TRANSACTION

END

ELSE

ROLLBACK TRANSACTION

END

DeadLock

Impostare DEADLOCK_PRIORITY to LOW sulla transazione meno importante.

Cosi quando accade un deadlock ,la transazione meno importante saraì terminata.

Cursori

DECLARE curRecurse CURSOR LOCAL FOR

SELECT idItem, Description, MLevel FROM Menu WHERE idParent = @TopID

OPEN curRecurse

FETCH NEXT FROM curRecurse INTO @idItem, @Description, @Level

WHILE (@@FETCH_STATUS = 0)

BEGIN

--codice

FETCH NEXT FROM curRecurse INTO @idItem, @Description, @Level

END

-- facciomo pulizia delle risorse utilizzate.....

CLOSE curRecurse

DEALLOCATE curRecurse

StoredProcedure

Creare

CREATE PROCEDURE Ricor

@TopID as int,

@gtemp_table_name as VARCHAR(64)

Declare @Description as varchar(50)

Declare @idItem as int

Declare @Level as int

Chiamata ad una sp

DECLARE @retval int

DECLARE @ytd int

EXEC @retval = get_sales_for_title ‘Net Etiquette’, @ytd OUTPUT

IF @retval < 0 PRINT

‘No sales found’

ELSE

PRINT ‘Year to date sales: ’ + STR (@ytd)

Constraints

Vengo utilizzati per forzare l’integrita di dominio,controllando i valori inseriti in un determinata colonna secondo un’espressione logica.

Primary Key

Determinano l’unicità del valore inserito che pero’ non posso essere nullo.

Automaticamente viene creato un indice che permette di velocizzare le ricerche quando il campo viene utilizzato nelle query.

Foreign key

Le fk sono una sorta di constraints che permettono di controllare che i valori inseriti in una colonna figlia siano gli stessi di una colonna padre di un’altra tabella.Integrita’ referenziale.

Unique

Le unique constraints forzano l’unicità dei valori assicurando che non ci siano valori duplicati nelle colonne specificate che non siano pk.

Anche le pk forzano l’unicità ma non permettono valori nulli.

Le Unique sono raccomandate quando si vuole forzare l’unicità di piu’ colonne,invece di utilizzare piu’ pk che degraderanno le performance.

Constraints vs Trigger

I constraints a differenza dei Trigger non permettono la generazione di custom message.

Non permettono di assicurare la validità di dati(integrita referenziale) secondo un certo calcolo o valori di una colonna.Usare i Trigger.

Disabilitare

NOCHECK CONSTRAINT permette di disabilitare I constraint per permettere ad esempio l’inserimento di record.

Trigger

Sono una specie di sp che vengono eseguiti automaticamente quando si verifica un Insert,Update o Dolete su una tabella o vista.

Spesso vengono utilizzati per forzare le regole di business estendendo l’uso dei conistraints.

For Update

Serve per controllare I valori in Update statement.

Viene utilizzato per andare email con xp_sendmail

Instead of Update

Come per il “for update”,in piu’ sostituisce completamente l’Update statement inviato.

Instead of Insert

Serve per controllare I valori in Update statement,in piu’ sostituisce completamente l’Insert statement inviato.

After Update

Viene utilizzato per controllare I dati dopo che e’ stato eseguito l’Update.

Join

Full outer

In una situazione clienti-ordini, permette di estrarre un insieme di dati cosi composto:

1)tutti i clienti che hanno almeno un’ordine(inner join)

2)tutti i clienti che non hanno ordini

3)tutti gli ordini senza cliente

Cross

Produce ogni possibile combinazione tra le righe di 2 tabelle.

Cross join combina ogni riga della left table con tutte le righe della right table.

Il numero di righe totali=row table1 * row table2

Indici

L’indice e’ un mezzo che ci consente di puntare direttamente ai record.

Puo’ essere paragonato ad un indice telefonico ordinato alfabeticamente.

Funzionamento

Sql suddivide i dati in record,pagine ed estensioni.

Una pagina occupa al massimo 8kb mentre un’estensione che e’ composta da 8 pagine ne occupa 64kb.

Rec 1

Rec 2

Rec 3

Rec 1

Rec 2

Rec 3

Rec 1

Rec 2

Rec 3

Rec 1

Rec 2

Rec 3

Rec 1

Rec 2

Rec 3

Rec 1

Rec 2

Rec 3

Rec 1

Rec 2

Rec 3

Rec 1

Rec 2

Rec 3

Pagina(8 kb) Pagina2 Pagina3 Pagina4 Pagina5 Pagina6 Pagina7 Pagina8

Estensione(64 kb)

Il db quando deve aggiungere un record,individua la posizione,sposta tutti i record successivi di una posizione ed inserisce la nuova riga nel punto desiderato.

Nel caso in cui le pagine siano al completo,si scatena il Page Split: deve aggiungere un’estensione,spostare i dati nelle pagine vuote e procedere con l’inserimento del nuovo record.

Per quanto riguarda la cancellazione il procedimento e’ molto piu semplice:individua il record,lo cancella lasciando lo spazio vuoto per eventuali inserimenti.

In questo scenario si spreca spazio sul disco e si compromette l’efficienza del motore in quanto viene frequentemente impegnato nella costosa operazione di page-split.

Per ridurre il page-split e’ stato introdotto il Fill Factor per mezzo del quale e’ possibile specificare la percentuale di riempimento di una pagina.

Il Fill Factor deve essere valorizzato in base ad una stima del flusso di dati a cui e’ sottoposta la tabella,evitando di assegnargli una percentuale troppo alta,che non ridurrebbe il page split,ma nemmeno una troppo bassa che frammenterebbe la tabella influenzando le operazioni di ricerca e lo spazio su disco.

Tuning

Monitorare un’indice con il comando: DBCC SHOWCONTIG(table|object_id)

- Pages Scanned................................: 20	Numero di pagine esaminate;ogni pagina occupa 8k
- Extents Scanned..............................: 5	Numero di estensioni;teoricamente 5 estensioni contengono 40 pagine invece noi ne abbiamo 20.Significa che e’ frammentato(incide sulle ricerche)
- Extent Switches..............................: 4	Tempo impiegato per passare da un’estensione all’altra.
- Avg. Pages per Extent........................: 4.0	Media di pagine contenute in un’estensione.Deve essere prossimo ad 8 per essere in ottimo stato.
- Scan Density [Best Count:Actual Count].......: 60.00% [3:5]	Percentuale di pagine interne alle estensioni.Sotto al 75% conviene ricostruirlo
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 40.00%
- Avg. Bytes Free per Page.....................: 146.5
- Avg. Page Density (full).....................: 98.19%	Percentuale media di riempimento di una pagina.Dovrebbe coincidere con il FillFactor impostato sull’indice clustered della tabella.Se questa valore supera il FillFactor significa che sono stati fatti piu inserimenti altrimenti piu cancellazioni.

Per ripristinare un’indice possiamo:

Distruggere e ricreare l’indice
Eseguire DBCC DBREINDEX(table,’’,80)
Eseguire DBCC INDEXDEFRAG
Utilizzare Database Maintenance Plans di sql server.

Clustered

Impongono alla tabella di mantenere l’ordinamento dei record a livello fisico.

La colonna indice viene ordinata alfabeticamente e creata una struttura ad albero.Di solito viene usato nelle pk.E’ piu’ performante del non-clustered.

In una tabella non ci puo’ essere piu’ di una colonna Clustered.

NonClustered

Una tabella ne puo’ avere piu’ di uno.Di solito vengono impostati come indici nonclusterd tutti quei campi che vengono utilizzati nelle where.

Xml

Ottenere dati direttamente in xml

SELECT * FROM Products FOR XML AUTO

Leggere I dati da un file xml

SELECT * FROM OPENXML (@docHandle, ‘/ROOT/category/Product’, 1) WITH (ProductID int, CategoryID int ‘../@CategoryID’, CategoryName varchar(50), ‘../@CategoryName’, [Description] varchar (50))

Performance

Sql Profiler

Sql Profiler serve per monitorare le performance di sql server,debuggare statement e store procedure e identificare query lente.

E’ usato per creare un file di trace che verra’ poi analizzato dall’index tuinin wizard.

ShowServerTrace

Eseguire una query/sp nel query analizer,usando l’opzione showservertrace.

Il comando permette di capire l’impatto lato server della query,attraverso una serie di informazioni sulla comunicazione tra query analizer(client) e sql server(server).

ExcecutionPlan

Permette di vedere il costo di ogni operazione compiuta dalla query.

Permette cosi di capire come migliorare la query.

Index Tuning Wizard

Serve per creare un set ottimale di indici e statistiche.

Richiede come input la presenza di un file workload,ovvero uno script di sql batch,procedure call,event class…creato con sql profiler.

Lock

Molte volte le query ci mettono molto perche sono loccate.

L’Sp_Lock serve per ritornare le risorse che sono loccate.

L’Sp_Who ritorna informazioni sul processo corrente passato.

Statistiche

Molte volte per migliorare le performance di una query,bisogna aggiornare le statistiche soprattutto dopo che sono stati aggiunti molti record in una tabella.

EXEC sp_updatestats 'resample'

Sicurezza

Application Role

Creare un’applicatione role permette di attribuire I permessi non agli utenti ma ad un’applicazione.Cosi facendo tutti gli utenti che utlizzano quell’applicazione hanno i diritti.

Varie

passaggio di parametri che se nullI torna tutti i record

CREATE PROCEDURE dbo.GetCustomer3

(

@CustomerID nchar(5)=null,

@IdRagioneSocialeControparte int=null

)

IF @IdRagioneSocialeControparte=0

BEGIN

SET @IdRagioneSocialeControparte=NULL

END

SET NOCOUNT ON;

SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax

FROM Customers

WHERE

(CustomerID = @CustomerID or @CustomerID IS NULL) AND

(IdControparte = @IdRagioneSocialeControparte OR @IdRagioneSocialeControparte IS NULL)

Reset identity

resetta il valore di partenza del contatore

DBCC CHECKIDENT(table, RESEED, 0);

Aggiungere un linked server

EXEC sp_addlinkedserver ‘CORP2’, ‘SQL Server’

Osql

Digitare da prompt

c:\>osql /e

1> use pubs

2> select * from tab

3> go

CharIndex

Substring

Len

Right/Left

Upper/Lower

Str

Replace

GetDate

Ciclo While

IsNull

Convert Datetime

Coalesce

Where In @fld1,@fld2

RaisError

Collate

Table

Insert into

Temp table (sessione)

Esitenza Temp Table

Temp table (applicazione)

Drop table

Sommarizzare i Dati

Pivotizzazione

GroupBy

DeadLock

Cursori

Eseguire stringhe sql

SubQueries

Eseguire query su Linked Server

StoredProcedure

Constraints vs Trigger

Indici

Ottenere dati direttamente in xml

Sicurezza

passaggio di parametri che se nullI torna tutti i record

Aggiungere un linked server