Sql Server 2008 fetch first n rows

Sulla versione 2008 per ottenere la limitazione dell'output si usa una scomoda sintassi di questo tipo:

SELECT * FROM ( 
  SELECT *, ROW_NUMBER() OVER (ORDER BY nomeCampo desc) as row FROM miaTabella 
 ) a WHERE a.row >0 and a.row <= 100;

Questa sintassi è analoga alla LIMIT 0,100 di MySql

Verificare su che porta risponde Sql Server

Si può eseguire il seguente comando sql

xp_readerrorlog 0, 1, N'Server in ascolto sulla porta'','any', NULL, NULL, N'asc'

Query per trovare tabelle che hanno una FK verso la tabella in esame

Query per trovare tutte le foreign key verso una tabella, testato con Sql Server 2008

select t.name , fk.constraint_column_id  , c.name
from sys.foreign_key_columns as fk
inner join sys.tables as t on fk.parent_object_id = t.object_id
inner join sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id
where fk.referenced_object_id = (select object_id from sys.tables where name = 'NOME_TABELLA')
order by t.name , fk.constraint_column_id

Query per trovare tabelle e righe totali in un db

Con questa query possiamo trovare tutte le tabelle con il numero di righe ordinate per tabella con più dati

select so.name as NomeTabella,max(si.rows) as righe from sysobjects as so
inner join sysindexes as si
on si.id=so.id and so.xtype='U'
group by so.name
order by 2 desc;

Utility bcp Sql Server

L'utility bcp.exe, presente nelle installazioni di Sql Server di default, ci può servire per scaricarci i dati di una tabella assieme alla struttura.
Vediamo ad esempio come utilizzarla per creare un file di struttura della tabella e dopo per esportare i dati:

Con il comando

bcp domini.dbo.comuni format nul -f c:\comuni.frm -S 127.0.0.1 -U username-P password-n

Creiamo il file struttura specificando grazie al comando -n i dati nativi (quindi se ci sono campi di tipo data etc.)

Il risultato di questo comando è il seguente file:

10.0
3
1       SQLNCHAR            2       510     ""   1     codice avviamento bancario                               Latin1_General_CI_AS
2       SQLNCHAR            2       510     ""   2     denominazione                                            Latin1_General_CI_AS
3       SQLNCHAR            2       510     ""   3     sigla automobilistica                                    Latin1_General_CI_AS

Per esportare i dati possiamo fare così:

bcp domini.dbo.comuni out c:\comuni.dat -S 127.0.0.1 -U username -P password-c -v

L'opzione -c evita che ci sia il prompt per ogni campo e lo scrive di default come character usando sempre di default come separatore il tab tra un campo e l'altro.
Il risultato sarà di questo tipo (qui uno stralcio):

010009 TORINO TO
012005 AOSTA AO
014001 GENOVA GE
016006 MILANO MI
018002 TRENTO TN
020008 VENEZIA VE
022004 TRIESTE TS
024000 BOLOGNA BO
026005 ANCONA AN
028001 FIRENZE FI
030007 PERUGIA PG
032003 ROMA RM
034009 NAPOLI NA
036004 L'AQUILA AQ
038000 CAMPOBASSO CB
040006 BARI BA
042002 POTENZA PZ
044008 CATANZARO CZ
046003 PALERMO PA
......
......

Per ricaricare i dati su un'altra tabella o un altro db occorre invece eseguire il comando

bcp domini2.dbo.BKCR_DOMANDA  in comuni.dat -f comuni.fmt  -e scarico.log -S 127.0.0.1 -U gianos -P gianos -c

L'opzione -e scrive se necessario dei log di errore.

Si noti che per quanto riguarda lo scarico dei dati (creazione del file .dat) è possibile anche scaricare i dati in base ad una query con il seguente comando:

bcp "select TOP 100 * from domini.dbo.comuni" queryout output.txt -S 127.0.0.1 -U gianos -P gianos -c

Resettare valore autoincrement di una chiave primaria

Nel caso di chiavi primarie del tipo autoincrement è possibile resettarle con la seguente istruzione

DBCC CHECKIDENT ([NOME_TABELLA], RESEED, 0)

Sql Server ottenere le constraint di una tabella

Di seguito una query per ottenere le informazioni su tutti i constraint di una tabella

SELECT 
     t.Name as NOME_TABELLA,
    c.Name as NOME_COLONNA,
    dc.Name as NOME_CONSTRAINT,
    dc.definition as VALORE
FROM sys.tables t
INNER JOIN sys.default_constraints dc ON t.object_id = dc.parent_object_id
INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id 
AND c.column_id = dc.parent_column_id 
where t.NAME='?';

Sql Server creare un database da file .bak

Con Sql Server 2008 per ripristinare su una istanza di db il back up di un back up (file con estensione .bak) si procede in questo modo:
Per prima cosa dal nodo database con il tasto destro del mouse selezionare l'opzione "Ripristina file e datagroup".

Dalla schermata che appare specificare il nome del db di destinazione (può essere già esistente o meno, nel caso non lo sia sarà generato da sql server).

Una volta specificato il database di destinazione, il dispositivo di origine (il file .bak) e selezionata la check box in basso, prima di procedere al back up occorre abilitare le impostazioni di sovrascrittura cliccando sulla sinistra in opzioni e quindi selezionando "Sovrascrivi il database esistente /WITH REPLACE)

Nel caso in cui non si avesse questa accortezza il back up andrà in errore dandoci un messaggio del tipo: "Il set di backup include un backup di un database diverso dal "nome_db" esistente"

Sql Server express impostare la porta 1433 come default TCP/IP

Per poter raggiungere Sql Server tramite TCP/IP bisogna abilitare la funzionalità da Sql Server Configuration Manager.

Questo tuttavia non basta, di default infatti il server prende una porta casuale per cui si può incappare in problemi di connessione quando si prova a connettersi tramite datasource esterno (nel mio caso datasource definito su Tomcat).
E' necessario quindi andare con il tasto destro su TCP/IP poi su proprietà e quindi sul tab "Indirizzi IP" e quindi settare la porta nella sezione denominata "IP ALL".

A questo punto si può restartare il db e modificare il datasource in questo modo:

<?xml version="1.0" encoding="UTF-8"?>

<Context>

  <Resource name="jdbc/gianospro"
  auth="Container"
  type="javax.sql.DataSource"
  factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
  testWhileIdle="true"
  testOnBorrow="true"
  testOnReturn="false"
  validationQuery="SELECT 1"
  validationInterval="30000"
  timeBetweenEvictionRunsMillis="30000"
  maxActive="100"
  minIdle="10"
  maxWait="10000"
  initialSize="10"
  removeAbandonedTimeout="60"
  removeAbandoned="true"
  logAbandoned="true"
  minEvictableIdleTimeMillis="30000"
  jmxEnabled="true"
  jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
  username="root"
  password="admin"
  driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
  url="jdbc:sqlserver://127.0.0.1:1433;databaseName=nome_db"/>

</Context>

Sql Server select con WHEN e ALIAS

Ci sono due modi di utilizzo delle select con la clausola WHEN.
Si può scrivere qualcosa di questo tipo:

....,
case TIPO_SOGGETTO
WHEN 'PF' then 'Persona Fisica'
WHEN 'PG' then 'Persona Giuridica'
end ,
.....

In questo caso nella esecuzione dell'sql si avrà come intestazione di colonna proprio il campo TIPO_SOGGETTO.
Nel caso in cui si vogliano invece avere degli alias occorre modificare leggermente la sintassi.
Supponiamo ad esempio di applicare per due volte la stessa condizione vista sopra per esporre risultati  diversi,volendo evitare la ripetizione del nome colonna:

......,
case 
WHEN TIPO_SOGGETTO='PF' then 'Persona Fisica'
WHEN TIPO_SOGGETTO='PG' then 'Persona Giuridica'
end as TIPOLOGIA,
case 
WHEN TIPO_SOGGETTO='PF' then (LTRIM(RTRIM(s.nome))+ ' '+LTRIM(RTRIM(s.cognome))) 
WHEN TIPO_SOGGETTO='PG' then (LTRIM(RTRIM(s.denominazione))) 
end as DENOMINAZIONE_SOGGETTO ,
.......

Sql Server 2005 verificare i lock presenti

Per verificare i lock su Sql Server 2005 occorre eseguire la seguente query:

select cmd,* from sys.sysprocesses
where blocked > 0

Conoscere il prossimo contatore identity inserito

Con Sql Server per conoscere il prossimo id che verrà generato nel caso dei campi identity occorre eseguire la seguente query:

SELECT IDENT_CURRENT ('NOME_TABELLA')+ IDENT_INCR('NOME_TABELLA')

N.B.: non usare mai la select max(id) +1 from tabella in quanto nel caso di campi identity potrebbero esserci dei buchi nella numerazione (dovuti ad esempio a delle delete) o la tabella potrebbe essere stata definita con un passo di incremento >1. Da qui la necessità di usare l'accoppiata IDENT_CURRENT e IDENT_INCR

Elencare tutti i db presenti

Per trovare tutti i db presenti su un Sql Server

SELECT name 
    FROM master..sysdatabases 
    ORDER BY name

Hash MD5 con Sql Server

In sql server esiste la funzione HashBytes che prende in input 2 parametri:

• L'algoritmo usato;
• La Stringa di cui calcolare l'hash.

Per cui scrivendo

select HashBytes('MD5','carlo')
>> 0x6E6BC4E49DD477EBC98EF4046C067B5F

Abbiamo il risultato in bytes, per ottenere invece il valore in varchar non funziona il classico metodo CONVERT dell'sql  ma bisogna utilizzare la funzione fn_varbintohexstr definita nel master.

select SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', 'ciao')), 3, 32);
>> 6e6bc4e49dd477ebc98ef4046c067b5f

Si noti che è fondamentale il passaggio per la funzione fn_varbintohexstr altrimenti il substring si applicherebbe a dei bytes e non a delle stringhe con risultati errati.
Esempio:

select substring(HashBytes('MD5','ciao'),3,32); 
>> 0xC4E49DD477EBC98EF4046C067B5F

Sql Server select cast

Con Sql server per ottenere valori decimali si può utilizzare la sintassi

select cast([numero] as decimal(5,2))


Se facciamo così però:

select cast(3/5 as decimal(5,2))

Otteniamo 0, un modo veloce per ottenere il risultato corretto è quello di anteporre 1. * all'espressione, in questo caso il risultato è 0.6 correttamente

Facendo

select cast(1.*3/5 as decimal(5,2))

Clausola over partition by con Sql Server

E' una utile funzionalità che in DBMS come Sql Server consente di effettuare sotto aggregazioni specifiche di colonna e consente di risparmiare lunghe e complesse join.


Sintassi da usare nella select

[FUNZIONE(ad esempio di aggregazione)] OVER (partition by [nome_campo] order by ..) as 'Nome logico'

Esempi utili si trovano sul sito Microsoft (qui )

Trovare tutte le query in esecuzione sul DB

Eseguendo la seguente query sullo schema MASTER si possono tirare fuori tutte le query in esecuzione :

SELECT st.text, r.session_id, r.status, r.command, r.cpu_time, r.total_elapsed_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
order by total_elapsed_time desc

sys.dm_exec_sql_text(sql_handle | plan_handle)
"Restituisce il testo del batch SQL identificato dall'argomento sql_handle specificato" (MSDN)

Può essere utilizzato con le seguenti tabelle:

• sys.dm_exec_query_stats
  
• sys.dm_exec_requests
  
• sys.dm_exec_cursors
  
• sys.dm_exec_xml_handles
  
• sys.dm_exec_query_memory_grants
  
• sys.dm_exec_connections