Blog di Zanirato Luca

TABLE Type

In questo articolo tratto da HTML.it (Database) viene specificato in maniera

chiara una tecnica che non implica l'uso di tabelle temporanee e cursori che spesso sono

necessari per tirarsi fuori dagli impicci.

L'uso eccessivo di cursori e tabelle temporanee però dimostrano chiaramente

un' intensa attività I/O fatta da SQL Server richiedendo un lavoro extra.

La strada per risolvere questi inconvenienti ci viene da un nuovo data type

(meglio dire table type) introdotto con la versione 2000 di SQL Server.

Versione integrale dell'articolo con un esempio a pagina 2 :

http://database.html.it/articoli/leggi/716/usare-le-variabili-tabella-per-eliminare-luso-dei-cursori/1/

Ulteriori informazioni sul tipo di dato TABLE :

http://technet.microsoft.com/it-it/library/ms175010.aspx

Ciao a Tutti

Supporto alle query XML in SQL Server

Nella maggior parte delle applicazioni sviluppate per SQL Server

i programmatori sono abituati a recupare i dati contenuti nelle tabelle dei databases,

ad esempio con l'oggetto recordset di ADO e come tali vengono

inviati al client per l'elaborazione.

La grossa novità introdotta da Microsoft nella versione 2000 di SQL Server

è la possibilità di estendere le funzionalità standard dell'istruzione SELECT

per recuperare i dati in formato XML.

Nei seguneti link vedremo l'evoluzione di questa fantastica funzionalità :

SQL Server 2000 : http://www.aspitalia.com/articoli/db/xmlt-sql.aspx

SQL server 2005 e 2008 : http://www.aspitalia.com/articoli/sqlserver/xml-sql.aspx

Per ulteriori informazioni andare al seguente LINK :

http://www.microsoft.com/sqlserver/2008/en/us/wp-sql-2008-whats-new-xml.aspx

Ciao a Tutti

HINT

Gli Hint sono particolari Opzioni a livello di istruzione che vengono specificati

per imporre un comportamento specifico alle query per l'esecuzione di istruzioni

SELECT, INSERT, UPDATE o DELETE.

Gli Hint consentono di ignorare eventuali piani di esecuzione selezionati

da Query Optimizer per una query.

Dal momento che di norma Query Optimizer di SQL Server seleziona

il miglior piano di esecuzione possibile per una query,

Join_Hint, Query_Hint e Table_Hint devono essere utilizzati solo da amministratori

di database e sviluppatori esperti in casi di effettiva necessità.

Per maggiori informazioni sulle 3 varie tipologie :

Join_Hint : http://msdn.microsoft.com/it-it/library/ms173815.aspx

Query_Hint : http://msdn.microsoft.com/it-it/library/ms181714.aspx

Table_Hint : http://msdn.microsoft.com/it-it/library/ms187373.aspx

Per maggiorni informazioni sul concetto di Opzioni SQL Server :

http://technet.microsoft.com/it-it/library/ms191203.aspx

Ciao a Tutti

ALTER INDEX, alternativa a DBCC DBREINDEX

L'utilizzo eccessivo del db, provoca a lungo andare una frammentazione

dei dati nelle pagine, e si rileva quindi necessario riorganizzare o reindicizzare

una o più tabelle al fine di ripristinare una buona performance sia in lettura che in scrittura.

Normalmente la percentuale di frammentazione dovrebbe attestarsi

non oltre il 35% (Microsoft dice anche il 25%),

limite oltre il quale le performance della tabella o del db iniziano a scemare.

Per verificare la percentuale di frammentazione si può ricorrere a questa istruzione :

DECLARE @object_name VARCHAR(20);
SET @object_name = 'TableName'

DBCC SHOWCONTIG (@object_name) WITH TABLERESULTS, ALL_INDEXES

valida per SQL 2000 e SQL 2005 oppure una più elegante :

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;

SET @db_id = DB_ID(N'DBName');
SET @object_id = OBJECT_ID(N'TableName');

SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'SAMPLED');

valida per il solo SQL 2005.

Nel primo caso la colonna di riferimento sarà la "LogicalFragmentation"

nel secondo "avg_fragmentation_in_percent".

Per ricostruire l'indice in linea o non in linea, in SQL Server 2008, viene utilizzata

l'istruzione ALTER INDEX che, come viene enunciato nel titolo dell'articolo,

sostituisce l'istruzione DBCC DBREINDEX adottata nelle versioni precedenti :

DBCC DBREINDEX ('NomeTabella', '', ValoreDiFrammentazione)

vi è anche una stored procedure che apre un cursore che cicla

su tutte le tabelle e rigenera gli indici :


USE [NomeDatabase]
   
DECLARE @tabella varchar(255)

DECLARE cursore_tabella CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN cursore_tabella

FETCH NEXT FROM cursore_tabella INTO @tabella
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@tabella,' ',90)
FETCH NEXT FROM cursore_tabella INTO @tabella
END

CLOSE cursore_tabella

DEALLOCATE cursore_tabella

L'istruziuone ALTER INDEX invece si presenta come segue :


Ricostruzione di un indice

Nell'esempio seguente viene ricostruito un singolo indice della tabella :

USE Nome Database;
GO
ALTER INDEX NomeIndice ON NomeTabella
REBUILD;
GO

Ricostruzione di tutti gli indici di una tabella e impostazione di opzioni

Nell'esempio seguente viene specificata la parola chiave ALL per ricostruire tutti

gli indici associati alla tabella e vengono inoltre impostate tre opzioni :

USE [NomeDatabase];
GO
ALTER INDEX ALL ON NomeTabella
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);
GO

Le tre opzioni specificate sono :

FILLFACTOR
Specifica una percentuale che indica il livello di riempimento che deve essere applicato
da Database Engine (Motore di database) per il livello foglia di ogni pagina di indice
durante un'operazione di creazione o modifica di un indice.
fillfactor deve essere un valore integer compreso tra 1 e 100. Il valore predefinito è 0.
I valori 0 e 100 sono equivalenti;

SORT_IN_TEMPDB { ON | OFF }
Specifica se i risultati dell'ordinamento devono essere archiviati in tempdb.
Il valore predefinito è OFF;

STATISTICS_NORECOMPUTE { ON | OFF }
Specifica se devono essere ricalcolate le statistiche di distribuzione.

Per Ulteriori informazioni :

http://msdn.microsoft.com/it-it/library/ms188388.aspx

Ciao a Tutti

New Datetime Datatypes in SQL Server 2008

In SQL Server 2008 sono stati introdotti dei nuovi tipi di dato Datetime,
in questo articolo vedremo i tipi :

- TIME;
- DATE;
- DATETIME2;
- DATETIMEOFFSET.

TIME

Realizza un Tipo Ora (Senza Data), include Ore, Minuti e Secondi
con frazioni di secondo fino a 9999999 :

DECLARE @mytime TIME
SET @mytime = GETDATE()
SELECT MyTime = @mytime

Risultato :

MyTime
01:48:18.4870000

E' possibile anche impostare la precisione della frazione di secondo in questo modo :

DECLARE @mytime TIME(3)
SET @mytime = GETDATE()
SELECT MyTime = @mytime

Risultato : 

MyTime
01:51:25.580 

DATE

Realizza un Tipo di Data (Senza Ora), include Mesi, Giorni e Anni.
Il valori delle date possono andare da : 01-01-01 a 9999-12-31.

Esempi :

DECLARE @mydate DATE
SET @mydate = GETDATE()
SELECT MyDate = @MyDate
SET @mydate = '01/01/1997'
SELECT MyDate = @MyDate
SET @mydate = '01/31/9999'
SELECT MyDate = @MyDate
SET @mydate = '01/01/0001'
SELECT MyDate = @MyDate

Risultato :

MyDate
2007-12-18

MyDate
1997-01-01

MyDate
9999-01-31

MyDate
0001-01-01

DATETIME2

Il tipo di dato DATETIME2 è la combinazione fra i tipi

DATE e TIME.

DATETIME2 usa un range di date da 01-01-01 a 9999-12-31 ed utilizza

le frazioni di secondo fino a 9999999 :

DECLARE @mydate DATETIME2
SET @mydate = GETDATE()
SELECT MyDate = @MyDate
SET @mydate = convert(datetime2,'01/01/1997 16:14:00.1234567')
SELECT MyDate = @MyDate
SET @mydate = convert(datetime2,'01/01/0001 16:14:00.1234567')
SELECT MyDate = @MyDate

Il Range del tipo DATETIME2 e da 0001-01-01 00:00:00.0000000 
a 9999-12-31 23:59:59.9999999.

Risultato :

MyDate
2007-12-18 02:11:45.3130000
 
MyDate
1997-01-01 16:14:00.1234567
 
MyDate
0001-01-01 16:14:00.1234567

DATETIMEOFFSET

Definisce una data in combinazione con un'ora del giorno
con considerazione del fuso orario ed espressa nel formato 24 ore.

Select
CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS 'DatetimeOffset'

Risultato :

DatetimeOffset
2007-05-08 12:35:29.1234567 +12:15

E' possibile inoltre, attraverso le funzioni CAST e CONVERT, convertire
il tipo DatetimeOffset in altri tipi di dato Datetime.

Esempio :

DECLARE @mydate DATETIME2(4)
SET @mydate = GETDATE()
SELECT @mydate as 'OriginalDate'
SELECT CONVERT (smalldatetime,@mydate ) as 'Smalldate'
SELECT CONVERT (time,@mydate ) as 'Justtime'

Risultato :

OriginalDate
2007-12-19 02:15:09.3130
 
Smalldate
2007-12-19 02:15:00.000
 
Justtime
02:15:09.3130000

Ciao a Tutti

Configuration SQL Server Database_Mail

Per configurare Database_Mail procedere come segue :

1) Impostazione Account Database_Mail

2) Impostazione Profilo Database_Mail

3) Aggancio Profilo / Account Database_Mail

4) Impostazione Profilo Principale

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
        @profile_name = ' ' , (stesso nome messo nel punto 3)
        @principal_name = ' ' ,
        @is_default = 1 ;

TEST Invio Mail

DECLARE @body1 VARCHAR(100)
SET @body1 = 'Server :'+@@servername+ ' My First Database Email !'
EXEC msdb.dbo.sp_send_dbmail
       @recipients= ' ' ,
       @subject = ' ' ,
       @body = @body1,
       @body_format = ' ' ;

EXEC msdb.dbo.sp_send_dbmail
        @profile_name = ' ' ,
        @recipients = ' ' , (E' possibile aggiungere più account separati da ";")
        @body = ' ' ,
        @subject = ' ' ;

Ciao a tutti

Esecuzione comando DOS con SQL Server (Script)

SET @Command = 'Comando MS-DOS'

Ricordarsi d'avere 1 nell'opzione XP_CMDSHELL, per verificare questo valore e

successivamente impostarlo a 1 nel caso fosse a 0, procedere come segue :

Ciao a Tutti

SP_HELP & SP_HELPTEXT

La Stored Procedure SP_HELP restituisce informazioni su un oggetto di database

(tutti gli oggetti elencati nella vista di compatibilità sys.sysobjects),

un tipo di dati definito dall'utente oppure un tipo di dati.

Sintassi d'utilizzo :

sp_help [ [ @objname = ] 'name' ]

per ulteriori informazioni : http://msdn.microsoft.com/it-it/library/ms187335.aspx

La Stored Procedure SP_HELPTEXT visualizza la definizione di una regola

definita dell'utente, una stored procedure Transact-SQL non crittografata predefinita,

una funzione Transact-SQL definita dall'utente, un trigger, una colonna calcolata,

un vincolo CHECK, una vista oppure un oggetto di sistema

quale una stored procedure di sistema.

Sintassi d'utilizzo :

sp_helptext [ @objname = ] 'name' [ , [ @columnname = ] computed_column_name ]

per ulteriori informazioni : http://msdn.microsoft.com/it-it/library/ms176112.aspx

Ciao a Tutti

SP_WHO & SP_WHO2

Questa Stored Procedure di sistema restituisce informazioni sugli utenti,

le sessioni e i processi correnti in un'istanza del SQL Server.

È possibile filtrare queste informazioni in modo da ottenere

solo i processi che non sono inattivi, che appartengono a un utente

specifico o che sono associati a una determinata sessione.

Sintassi d'utilizzo :

sp_who [ [ @loginame = ] 'login' | session ID | 'ACTIVE' ]

Ciao a Tutti

SP_DEPENDS & SP_DEPENDS++

La Stored Procedure di sistema SP_DEPENDS, come sappiamo, visualizza le informazioni
sulle dipendenze degli oggetti del database, ad esempio le viste e le procedure che
dipendono da una tabella o una vista e le tabelle e le viste da cui esse dipendono.

I riferimenti agli oggetti esterni al database corrente non vengono riportati.

Fonte d'informazione : http://msdn.microsoft.com/it-it/library/ms189487(SQL.90).aspx

Questa Stored Procedure presenta una limitazione in quanto, come viene descritto
da quest'articolo, non visualizza le informazioni dei Trigger che appartengono
alla tabella interrogata.

Articolo Originale : http://support.microsoft.com/kb/180490/en-us/

Per questo è stata introdotta questa SP_DEPENDS2 (chiamata da me SP_DEPEND++)
che risolve questo Bug.

Il codice lo potete trovare al link sopra riportato oppure in formato pdf all'interno del file *.zip in allegato al Post.

Ciao a Tutti

DROP ForeignKey con popolazione di Codice SQL

Questo script, a me molto utile, permette d'estrapolare

tutte le ForeignKey del mio Database e di popolare in un colpo solo

tutte le istruzioni DROP che riguardano queste ForeignKey :


USE [NomeDatabase]

SELECT cast(f.name AS Varchar(255))  AS Nome_ForeignKey

, r.keycnt                    

, cast(c.name AS Varchar(255)) AS Foreign_Table

, cast(fc.name AS Varchar(255)) AS Foreign_Column_1

, cast(fc2.name AS Varchar(255)) AS Foreign_Column_2

, cast(p.name AS Varchar(255)) AS Primary_Table

, cast(rc.name AS Varchar(255)) AS Primary_Column_1

, cast(rc2.name AS Varchar(255)) AS Primary_Column_2

FROM sysobjects f

INNER JOIN sysobjects c On f.parent_obj = c.id

INNER JOIN sysreferences r On f.id = r.constid

INNER JOIN sysobjects p On r.rkeyid = p.id

INNER JOIN syscolumns rc On r.rkeyid = rc.id And r.rkey1 = rc.colid

INNER JOIN syscolumns fc On r.fkeyid = fc.id And r.fkey1 = fc.colid

LEFT JOIN syscolumns rc2 On r.rkeyid = rc2.id And r.rkey2 = rc.colid                    

LEFT JOIN syscolumns fc2 On r.fkeyid = fc2.id And r.fkey2 = fc.colid

WHERE f.type = 'F'

SELECT

'if exists (select * from dbo.sysobjects where id =

object_id(N''[dbo].['+ Nome_ForeignKey + ']'')

ALTER TABLE [dbo].[' + Foreign_Table + '] DROP CONSTRAINT ' + Nome_ForeignKey

FROM ForeignKey ORDER BY Foreign_Table

Buona Giornata!

Informazioni TABELLE Database

Questa estrapolazione dà una visione più chiara del contenuto principale di un Database :

SELECT
OBJECT_NAME(obj.id) AS 'Tabella',
SUBSTRING(col.name,1, 30) AS 'Colonne',
CASE WHEN EXISTS (
			Select name 
			From syscolumns sc 
			Where	sc.id = obj.id and 
				sc.colid = col.colid and 
				sc.colstat & 1 = 1

		 ) THEN
			 'Si'
		   ELSE
			''
END AS 'Identity',
CASE 	WHEN UPPER(TYPE_NAME(col.xtype)) = 'DECIMAL' THEN
		UPPER(RTRIM(TYPE_NAME(col.xtype)) + 
		'(' + CAST(COL.xprec AS VARCHAR) + ','
		+ CAST(COL.xscale AS VARCHAR) + ')')
	WHEN UPPER(TYPE_NAME(col.xtype)) = 'VARCHAR' THEN
		UPPER(RTRIM(TYPE_NAME(col.xtype)) + '(' 
		+ CAST(COL.length AS VARCHAR) + ')')
	WHEN UPPER(TYPE_NAME(col.xtype)) = 'NVARCHAR' THEN
		UPPER(RTRIM(TYPE_NAME(col.xtype)) + '(' 
		+ CAST(COL.length / 2 AS VARCHAR) + ')')
	ELSE 
		UPPER(RTRIM(TYPE_NAME(col.xtype)))
END AS 'Tipo di Dato',
CASE WHEN col.cdefault <> 0 
	THEN 
	(
		SELECT REPLACE(REPLACE(text, '(', ''), ')', '') 
		FROM syscomments def 
		WHERE def.id = col.cdefault
	)
	ELSE ''
END AS 'Valore Default',
CASE col.isnullable
	WHEN 0 THEN 'Not Null' ELSE ''
END AS 'Valore Nullo'

FROM syscolumns col

INNER JOIN sysobjects obj ON 	col.id = obj.id Or 
			col.id = cast(obj.parent_obj as int)

WHERE obj.xtype = 'U'

ORDER BYobj.name, col.colid 

Ciao a Tutti

Apostrofo in SQL Server

Sarà anche una banalità ma siccome a me ha dato parecchi problemi,

su alcune estrapolazioni dati voglio segnalare la sintassi per l'uso corretto del

carattere apostrofo in una semplice SELECT o WHERE :

La regola è quella d'inserire un doppio apice invece che singolo,

per l'utilizzo del carattere apostrofo, come negli esempi :

SELECT 'Po''' FROM NomeTabella

la condizione, nel secondo esempio, si presenta come segue :

SELECT * FROM NomeTabella
WHERE Campo1 =  'l''istruttore e l''istruttrice'

Ciao a Tutti

Stored Procedure : Usp_KillUsers

Mi sono imbattuto più d'una volta nell'eseguzione di Backup tramite

Job Schedulato SQL.

Il processo schedulato nel mio caso aveva due passaggi :

1) Popolazione Datawarehouse;

2) Backup Automatico.

Normalmente questo processo veniva eseguito di notte,

ma per necessità di aggiornamento di dati questo poteva essere lanciato

anche manualmente durante la normale attività lavorativa

comportando la corruzione del Backup.

Tutto questo preambolo per introdurre una Stored Procedure,

che magari qualcuno conosce già, che nel caso qualche utente sia connesso

al Datawarehouse, nel mio caso, li disconnette.

Il mio processo quindi risultava formato infine da 3 Passaggi :

1) Popolazione Datawarehouse;

2) Disconnessione Utenti (Exec Usp_KillUsers 'NomeDB o Datawarehouse');

2) Backup Automatico.

In questo modo si è certamente sicuri che il Backup venga

eseguito con successo.

Questo è il codice della Stored Procedure :

CREATE PROCEDURE Usp_KillUsers
   @DBName Varchar(30)
As
DECLARE @Sql Varchar(60)

DECLARE Kill_cur cursor fast_forward for

SELECT 'Kill ' + cast(spid as varchar) 
FROM master.dbo.sysprocesses 
WHERE dbid = db_id(@DBName) And
	     memusage > 0

OPEN Kill_Cur
Fetch Next From Kill_Cur into @Sql
While @@fetch_status = 0
Begin
   EXEC(@Sql)
   Fetch Next From Kill_Cur into @Sql
End
CLOSE Kill_Cur
DEALLOCATE Kill_Cur
GO

Ciao a Tutti

Shrink del Database in SQL Server 2005 e 2008

DBCC Shrinkfile (NomeDatabase_log)

USE [NomeDB] 
GO

DBCC SHRINKFILE (N'NomeDB_Log' , 1)
GO

Ciao a Tutti