SQL Server

SQL Server è un sistema di gestione di database relazionali sviluppato da Microsoft, progettato per archiviare, recuperare e gestire efficacemente grandi quantità di dati utilizzando il linguaggio SQL. Vantaggi:

• Robustezza
• Performance
• Maturità
• Presenza sul mercato
• Integrazione con sistemi terzi
• Facilità di impiego
• Bassi costi di manutenzione
• Economico
• Ambia divulgazione e documentazione
• Costante crescita

Componenti

1. Database Engine
   • Il componente principale di SQL Server che gestisce l’archiviazione, la gestione e l’elaborazione dei dati.
2. Server Agent
   • Strumento di automazione che consente di programmare ed eseguire operazioni di manutenzione, backup e monitoraggio in SQL Server. Assomiglia al cron di linux.
3. Reporting Services
   • Componente che permette di sviluppare report basati sui dati presenti in SQL Server.
   • Oggi è considerato quasi obsoleto, poiché altre soluzioni di reportistica e visualizzazione dati sono diventate più avanzate e diffuse.
4. Analysis Services
   • Strumento per creare e gestire strutture multidimensionali (come cubi o ipercubi) che contengono dati già aggregati provenienti da altre origini.
   • Un tempo molto utile per velocizzare le query analitiche, ma oggi il suo utilizzo è diminuito, grazie a hardware sempre più potente che permette l’elaborazione diretta dei dati senza necessità di pre-aggregazioni.

Edizioni

• Enterprise
  • L’edizione più completa di SQL Server, con funzionalità avanzate.
  • Raramente utilizzata, poiché spesso eccede le necessità della maggior parte delle applicazioni aziendali.
• Standard
  • Rappresenta circa il 99% degli utilizzi, offrendo un buon equilibrio tra funzionalità e costo.
  • Limiti di risorse: supporta fino a 25 core e 256 GB di RAM.
  • Disponibile con due opzioni di licenza:
    • Licenza per core: costa circa 1.500€ per core, con un minimo di 2 core.
    • Licenza per utente: costa 250€ per utente, a cui bisogna aggiungere 1.000€ per il motore SQL.
• Developer
  • Edizione gratuita destinata agli sviluppatori per creare e testare applicazioni.
  • Molto simile alla Standard in termini di funzionalità, ma non può essere utilizzata in ambienti di produzione.
• Express
  • Edizione gratuita, pensata per piccoli progetti e applicazioni di base.
  • Limiti di risorse:
    • Massimo di 10 GB di storage.
    • Non supporta le query in parallelo.
  • Sconsigliata per ambienti di produzione con grandi quantità di dati, a causa delle limitazioni di performance.

Istanze

In SQL Server, è possibile avere una o più istanze del database server installate sulla stessa macchina. Questo offre una grande flessibilità, soprattutto in ambienti in cui diverse applicazioni richiedono versioni differenti o configurazioni specifiche di SQL Server.

Istanza predefinita vs. Named Instance

1. Istanza predefinita (Default Instance):
   • Quando si installa SQL Server, si può scegliere di installare un’istanza predefinita. Quest’istanza è accessibile semplicemente usando l’indirizzo IP della macchina o il suo nome di rete, senza dover specificare ulteriori dettagli.
   • Di solito, l’istanza predefinita utilizza la porta 1433 per le connessioni TCP/IP, che è anche la porta standard per SQL Server. Se non viene modificata, qualsiasi connessione in arrivo verso questa porta verrà automaticamente instradata verso l’istanza predefinita.
   • Un’istanza predefinita è utile quando si desidera un accesso rapido e semplice, senza la necessità di specificare ulteriori informazioni nel nome del server.
2. Named Instances (Istanze nominate):
   • Quando si desidera installare più di una versione o configurazione di SQL Server sulla stessa macchina, è necessario ricorrere alle istanze nominate. Ogni istanza nominata deve avere un nome univoco, scelto dall’utente durante l’installazione (ad esempio ServerName\InstanceName).
   • Per accedere a un’istanza nominata, è necessario specificare sia il nome della macchina sia il nome dell’istanza (ad esempio 192.168.1.10\NomeIstanza).
   • Le istanze nominate generalmente utilizzano una porta diversa dalla porta 1433 per evitare conflitti con l’istanza predefinita.

File di dati e log

Microsoft SQL Server utilizza un’architettura in cui i dati e i log delle transazioni sono separati in file distinti per garantire l’integrità e la sicurezza del database. In pratica, il database SQL Server memorizza i dati in file con estensione .MDF (Master Data File) e .NDF (Secondary Data File), mentre le operazioni di log vengono salvate in un file con estensione .LDF (Log Data File).

Funzionamento

1. Scrittura nel Log di Transazione (LDF)
   • Quando un’applicazione scrive o modifica dati nel database, la modifica viene prima registrata nel log delle transazioni (file .LDF).
   • Il log delle transazioni registra ogni operazione di modifica (ad esempio, INSERT, UPDATE, DELETE) sotto forma di eventi di transazione.
   • In questo modo, ogni modifica al database è tracciata e sequenzialmente memorizzata nel log prima di essere applicata al file di dati (file .MDF).
2. Conferma della Transazione e Persistenza nei Dati (MDF)
   • Dopo che la modifica è stata registrata nel log delle transazioni, SQL Server conferma all’applicazione che l’operazione è stata completata. Questo processo è detto commit della transazione.
   • Solo in un secondo momento, in modo asincrono, SQL Server applica effettivamente le modifiche ai file di dati .MDF tramite un’operazione di scrittura chiamata checkpoint.
   • Durante un checkpoint, il sistema sincronizza il contenuto dei file di dati con il log delle transazioni, assicurandosi che tutte le modifiche registrate nel log siano state applicate anche ai dati.
3. Recupero in Caso di Interruzioni (Recovery)
   • In caso di interruzioni del servizio, come un arresto improvviso del server o un’interruzione di corrente, SQL Server può utilizzare il log delle transazioni per mantenere l’integrità del database.
   • Quando il server si riavvia, SQL Server esamina il log delle transazioni e applica qualsiasi modifica che era stata registrata nel log ma non ancora scritta nel file di dati. Questo processo è chiamato recovery.
   • Poiché tutte le modifiche sono prima registrate nel log, SQL Server è in grado di ripristinare il database allo stato esatto in cui si trovava prima dell’interruzione, riducendo il rischio di corruzione dei dati.

Per visualizzare il file di log posso usare la query:

SELECT * FROM fn_dialog(NULL, NULL)

Tipologie di database

SQL Server ha due grosse categorie di database:

• Database di sistema
  • master: database di sistema che deve necessariamente esistere che include varie informazioni di sistema, tra le quali: gli account di accesso, i server collegati e le impostazioni di configurazione di sistema. Vengono anche registrare le posizioni di tutti gli altri db collegati e le informazioni di inizializzazione di SQL Server.
  • tempdb: database temporaneo utilizzato non solo quando finisce la RAM ma anche per caching o operazioni particolari. Viene ricreato ad ogni riavvio di SQL Server.
  • model: database modello che viene utilizzato quando creo un nuovo database.
  • msdb: sono contenuti tutti i Jobs, alert e procedure automatiche avviate dall’agent.
• Database utente

Tipi di dati

Dati carattere

Il più usato è nvarchar, che ha sia una lunghezza variabile che la gestione di caratteri Unicode.

Tipo	Descrizione	Range di lunghezza	Archiviazione
char	Stringa a lunghezza fissa	1 - 8,000 caratteri	Fissa, 1 byte per carattere
varchar	Stringa a lunghezza variabile	1 - 8,000 caratteri o varchar(max)*	Variabile, 1 byte per carattere
nchar	Stringa a lunghezza fissa (Unicode)	1 - 4,000 caratteri	Fissa, 2 byte per carattere
nvarchar	Stringa a lunghezza variabile (Unicode)	1 - 4,000 caratteri o nvarchar(max)*	Variabile, 2 byte per carattere

Dati ora e data

I più usati sono smalldatetime o datetime se voglio una precisione al millisecondo.

Tipo	Range	Precisione	Archiviazione
date	0001-01-01 to 9999-12-31	Giorno	3 byte
datetime	1753-01-01 00:00:00.000 to 9999-12-31 23:59:59.997	3.33 millisecondi	8 byte
datetime2	0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999	100 nanosecondi	Da 6 a 8 byte (dipende dalla precisione)
smalldatetime	1900-01-01 00:00 to 2079-06-06 23:59	1 minuto	4 byte
time	00:00:00.0000000 to 23:59:59.9999999	100 nanosecondi	Da 3 a 5 byte (dipende dalla precisione)
datetimeoffset	0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999 (con offset di fuso orario)	100 nanosecondi	Da 8 a 10 byte (dipende dalla precisione)

Dati numerici

I dati più utilizzati sono int per gli interi e decimal per i numeri reali.

Tipo	Range	Intervallo	Archiviazione
bigint	-2^63 to 2^63 - 1	64 bit	8 byte
int	-2^31 to 2^31 - 1	32 bit	4 byte
smallint	-2^15 to 2^15 - 1	16 bit	2 byte
tinyint	0 to 255	8 bit	1 byte
bit	0 or 1	1 bit	1 bit
decimal	-10^38 +1 to 10^38 -1	Fino a 38 cifre decimali (18,6 più usato).	Da 5 a 17 byte (dipende dalla precisione). Più usato 9.

Comandi comodi

• T-SQL - Cursori
• T-SQL - Common Table Expression
• T-SQL - Merge
• T-SQL - Rollup
• SET ROWCOUNT

• Limita il numero di righe restituite da una query in SQL Server.
• Sintassi: SET ROWCOUNT number
• Per ripristinare tutte le righe: SET ROWCOUNT 0.
• Differenza con TOP: SET ROWCOUNT influisce su tutte le query successive finché non viene reimpostato.

• SET NOCOUNT ON
  • Disattiva il conteggio automatico delle righe modificate da ogni istruzione SQL.
  • Utile nelle stored procedure per migliorare le prestazioni.
  • Sintassi: SET NOCOUNT ON

• NULLIF

  • Restituisce NULL se due espressioni sono uguali, altrimenti restituisce la prima espressione.
  • Sintassi: NULLIF(expression1, expression2)

• HAVING

  • Permette di applicare filtri su aggregazioni (GROUP BY), non possibili con WHERE.
  • Esempio:

    SELECT IDTipoMovimento, SUM(PrezzoTotale)
    FROM contrattirighe
    GROUP BY IDTipoMovimento
    HAVING SUM(PrezzoTotale) > 100000.00;

• OUTPUT

  • Ritorna informazioni sulle righe modificate da istruzioni INSERT, UPDATE, DELETE o MERGE.
  • Utile per tracciare modifiche o archiviare cambiamenti.

• CHOOSE

  • Restituisce un valore corrispondente a un indice in una lista.
  • Esempio:

    SELECT CHOOSE(3, 'Manager', 'Director', 'Developer', 'Tester')

• UNION

  • Combina i risultati di più query in un unico set, eliminando i duplicati (per includere i duplicati usa UNION ALL).
  • Le query devono avere lo stesso numero e tipo di colonne.
  • Sintassi:

    query1
    UNION
    query2

• SELECT INTO

  • Crea una nuova tabella con i risultati di una query già tipizzata con i nomi dei campi.
  • Sintassi:

    SELECT column1, column2
    INTO NewTable
    FROM SourceTable;

• INSERT + SELECT e SELECT INTO

  • INSERT + SELECT: inserisce risultati di una query in una tabella esistente.

    INSERT INTO MyTable (column1, column2)
    SELECT column1, column2 FROM SourceTable;

  • SELECT INTO: crea automaticamente una nuova tabella con i risultati della query, utile per tabelle temporanee.

    SELECT column1, column2
    INTO #TempTable
    FROM SourceTable;

Importare DB

Esistono due principali modalità per importare un database in SQL Server:

1. Importazione tramite script SQL
2. Restore tramite file binario compresso

1. Importazione tramite script SQL

Per importare un database utilizzando uno script SQL:

• Apri il file dello script SQL in SQL Server Management Studio (SSMS).
• Premi il tasto F5 per eseguire lo script e importare il database.

2. Restore tramite file binario (es. file .bak)

La seconda opzione consiste nel ripristinare un database utilizzando un file binario compresso, solitamente un file di backup con estensione .bak. Questo file contiene i dati del database, incluse le parti principali:

• File .mdf (contiene i dati principali)
• File .ldf (contiene i log delle transazioni)

Procedura di restore in SQL Server:

1. In SQL Server Management Studio, fai clic destro su Database.
2. Seleziona Restore Database.
3. Nella finestra di ripristino, scegli Device e quindi File.
4. Naviga per selezionare il file .bak da importare.

Nota sulle cartelle visibili: Le cartelle visibili nel selettore dipendono dai permessi dell’utente con cui si è effettuato l’accesso:

• Se hai effettuato l’accesso con autenticazione SQL Server, vedrai solo alcune cartelle specifiche, come la cartella di SQL Server in Program Files.
• Non saranno visibili altre cartelle utente (ad esempio, C:\Users\paolo...) o cartelle di rete, a meno che l’utente con cui sei loggato non abbia i permessi su quei percorsi.

Alias e sinonimi

• Sinonimi: Un sinonimo è un nome alternativo per un oggetto di database (come una tabella, una vista o una stored procedure) che si trova nello stesso database o in un altro database. I sinonimi facilitano l’accesso a oggetti esterni o a oggetti con nomi complessi, permettendo di fare riferimento a un oggetto con un nome più semplice o locale senza modificare la struttura delle query. Supponiamo di avere una tabella Products in un database remoto chiamato InventoryDB. Creiamo un sinonimo per questa tabella nel nostro database corrente: CREATE SYNONYM MyProducts FOR InventoryDB.dbo.Products;
• Alias: Un alias è un nome temporaneo assegnato a una colonna o a una tabella all’interno di una singola query per migliorarne la leggibilità o per evitare conflitti di nomi. Gli alias sono specifici della query e non esistono al di fuori di essa. Per esempio, in una SELECT puoi creare un alias per una tabella o per una colonna (AS) per facilitare la scrittura della query. N.b. : non funzionano gli alias di colonna nella clausola WHERE.

Viste

Le viste in SQL Server sono query salvate che agiscono come tabelle virtuali. Una vista rappresenta un set di risultati derivati da una query su una o più tabelle o altre viste. Le viste possono semplificare l’accesso ai dati, migliorare la sicurezza (limitando l’accesso a determinate colonne) e permettere una gestione più semplice delle query complesse.

Buona norma: evitare di creare “viste di viste” (cioè, viste basate su altre viste), poiché ciò può ridurre le prestazioni e aumentare la complessità, rendendo più difficile il debug e la manutenzione del database.

Limiti

• Non è possibile usare la clausola ORDER BY senza in TOP
• Non è possibile usare una SELECT INTO in una view
• Non è possibile usare una clausola COMPUTE o COMPUTE BY
• Non è possibile fare riferimento a tabelle temporanee o variabili tabella
• Non è possibile nascondere la definizione di una vista

Linked Server

Un linked server in SQL Server è una funzionalità che consente di collegare il server SQL a un altro server di database (che può essere un altro SQL Server, PostgreSQL, Oracle, MySQL, ecc.) e accedervi come se fosse una parte dello stesso server. Con un linked server, puoi eseguire query distribuite che combinano dati provenienti da più database server. Vantaggi:

• Accesso remoto ai dati: Consente di accedere a dati di un altro server senza la necessità di trasferirli manualmente.
• Facilità di query: Puoi utilizzare T-SQL per interrogare dati su server remoti come se fossero locali.
• Integrazione: Ideale per sistemi che richiedono l’integrazione tra più database.

Installazione

ODBC

1. Installare un driver ODBC per PostgreSQL:
   • Installa il driver PostgreSQL ODBC (es. psqlODBC).
   • Configura un DSN (Data Source Name) sul sistema con le credenziali e il database PostgreSQL.: apri ODBC Data Source Administrator (x64) → System DSN → PostgreSQL ANSI (x64)
     • Data Source: Postgres
     • Database: mhiradb
     • Server: localhost
     • User Name: postgres
     • Password: (campo riempito, non visibile il valore esatto)
     • SSL Mode: disable
     • Port: (campo vuoto)
2. Abilitare la funzionalità Linked Server in SQL Server:
   • Assicurati che la funzionalità sia abilitata e che SQL Server abbia i permessi per utilizzare provider esterni.

Creare il Linked Server tramite SSMS

1. Apri SSMS e connettiti al tuo server SQL Server.
2. Espandi la cartella Server Objects.
3. Fai clic destro su Linked Servers e scegli New Linked Server.
4. Compila i campi come segue:
   • Linked server: Un nome rappresentativo (es. PostgreSQL_LinkedServer).
   • Provider: Seleziona Microsoft OLE DB Provider for ODBC Drivers.
   • Data source: Inserisci il DSN configurato per PostgreSQL.

Step 2: Configurare la Sicurezza

1. Nella scheda Security, seleziona “Be made using this security context”.
2. Fornisci il nome utente e la password dell’utente che verrà utilizzato da Sql Server per fare le query.

Utilizzo del Linked Server

Posso fare sia quelle query come se fossero in locale (alcuni ODBC convertono il dialetto, per esempio in PostgreSql il TOP 1 viene convertito in un LIMIT 1 automaticamente) oppure scrivere le query direttamente come stringhe usando la sintassi OPENQUERY

-- Query diretta tramite OPENQUERY se ODBC
SELECT * FROM OPENQUERY(PostgreSQL_LinkedServer, 'SELECT * FROM public.users');
 
-- Query distribuita
SELECT * FROM PostgreSQL_LinkedServer.public.users;

Sinonimi

I sinonimi in SQL Server sono un alias per un oggetto database, come una tabella, una vista, una procedura memorizzata o una funzione. Quando utilizzati con i linked server, i sinonimi offrono numerosi vantaggi in termini di leggibilità, manutenzione e flessibilità del codice. Vantaggi:

1. Maggiore leggibilità e semplicità del codice: Quando si lavora con i linked server, il nome completo di un oggetto può diventare lungo e complesso, poiché include il nome del server, del database, dello schema e della tabella:

SELECT * FROM [LinkedServer].[Database].[Schema].[Table];

Utilizzando un sinonimo, puoi semplificare il riferimento all’oggetto remoto:

SELECT * FROM SynonymForRemoteTable;

2. Facilità di manutenzione: se il nome del linked server cambia (ad esempio, perché l’ambiente di sviluppo, test o produzione utilizza server diversi), puoi aggiornare il sinonimo senza dover modificare ogni query o procedura memorizzata che fa riferimento al linked server.
   • Esempio: Cambi il sinonimo che punta a un nuovo linked server, e tutte le query che lo utilizzano continuano a funzionare senza modifiche.
3. Astrazione: I sinonimi nascondono la complessità del server remoto. Gli sviluppatori possono scrivere query come se stessero lavorando con oggetti locali senza preoccuparsi della struttura del linked server.

CREATE SYNONYM SynonymForRemoteUsers
FOR [PostgreSQL_LinkedServer].[DatabaseName].[SchemaName].[users];

Dopo aver creato il sinonimo, puoi usarlo nelle query senza specificare il linked server:

-- Invece di:
SELECT * FROM [PostgreSQL_LinkedServer].[DatabaseName].[SchemaName].[users];
 
-- Usa:
SELECT * FROM SynonymForRemoteUsers;

Utilizzo

Un esempio di utilizzo è avere uno script che di notte va a prendere dati da vari linked server di varia natura e porta tali dati all’interno di tabelle di Sql Server. Da queste tabelle che creo poi posso creare delle visualizzazioni ad hoc usando, per esempio, PowerBI. In questo modo non vado a toccare tutti i server originali e le rispettive tabelle ma le utilizzo solo in interrogazione per popolare il mio database con cui poter fare tutte le viste che voglio.

Importare e Sincronizzare un Database

Importare un database da un altro DBMS

1. Creare il DBMS come Linked Server
   • Configurare l’altro database come un linked server in SQL Server utilizzando SQL Server Management Studio (SSMS). Questo permette a SQL Server di comunicare con il database esterno come se fosse locale.
2. Creare le tabelle nel database SQL Server
   • Nel database SQL Server, crea le tabelle che rispecchiano la struttura (schema) del database esterno. Puoi farlo manualmente in SSMS oppure automatizzarlo se hai script SQL che descrivono le tabelle.
3. Importare i dati con una query
   • Utilizza una query SQL per copiare i dati dal database esterno al database SQL Server. Ad esempio:

     SELECT * INTO NomeTabella
     FROM OPENQUERY(LinkedServerName, 'SELECT * FROM NomeTabellaRemota');

Sincronizzare i dati con nuovi aggiornamenti

1. Identificare le righe nuove
   • Una volta che la tabella è stata creata in SQL Server, puoi sincronizzarla con i nuovi dati generati nel database remoto. Per fare ciò, utilizza un identificatore univoco, come un ID incrementale o un timestamp, per sapere fino a dove sono stati importati i dati.
2. Creare una stored procedure per l’aggiornamento
   • Scrivi una stored procedure che esegua una query sul database remoto per identificare e importare solo le righe nuove. Ad esempio:

     CREATE PROCEDURE SincronizzaDati
     AS
     BEGIN
         DECLARE @UltimoID INT;
      
         -- Trova l'ultimo ID sincronizzato nel database locale
         SELECT @UltimoID = MAX(ID) FROM NomeTabella;
      
         -- Importa solo le righe con ID maggiori di @UltimoID
         INSERT INTO NomeTabella (Colonna1, Colonna2, Colonna3)
         SELECT Colonna1, Colonna2, Colonna3
         FROM OPENQUERY(LinkedServerName, 
             'SELECT * FROM NomeTabellaRemota WHERE ID > ' + CAST(@UltimoID AS NVARCHAR(10)) + '');
     END;

3. Automatizzare l’esecuzione della stored procedure
   • Utilizza SQL Server Agent per pianificare l’esecuzione della stored procedure a intervalli regolari (ad esempio ogni notte). In questo modo, il database SQL Server sarà costantemente aggiornato con i nuovi dati provenienti dal database remoto.

Stored Procedure

Le stored procedure sono blocchi di codice SQL salvati nel database che possono essere eseguiti come un singolo comando. Sono usate per eseguire operazioni complesse, come inserimenti, aggiornamenti, cancellazioni e selezioni di dati, con la possibilità di includere logica condizionale (come IF e WHILE) e parametri di input/output. Sono utilizzate su database importanti in quanto sono estremamente più veloci di scrivere codice utilizzando magari un Orm per accedere al DB. Quindi non sono il male assoluto come può sembrare, ma devono essere utilizzate con ragionevolezza nei casi in cui siano indispensabili per avere ottime performance su enormi basi di dati. Il loro svantaggio è che sono difficilmente versionabili e mantenibili ma se utilizzate con razionalità permettono di ottenere delle performance incredibili, soprattutto su basi di dati importanti. Esempi:

• Script che girano di notte che sistemano dati e mandano mail nel caso di errori;
• Script che fanno polling su tabelle temp e in base a quello che leggono fanno cose;

Utilizzare i valori di ritorno

Di default una stored procedure fornisce come codice di uscita 0, se invece si verifica un errore il valore sarà diverso da 0 ovviamente. Anche noi possiamo assegnare dei valori all’istruzione RETURN, ad esempio RETURN(-100) esce dalla procedura con codice di errore uguale a -100. Spesso vi è la necessità, in una Stored Procedure, di lavorare con dei valori di ritorno forniti da una altra Stored. Per ottenere ciò esistono tre possibili modalità.

Return Value

All’interno della Stored Procedure posso utilizzare la parola chiave RETURN, per esempio:

CREATE PROCEDURE GetMyInt( @Param int)
AS
DECLARE @ReturnValue int
SELECT @ReturnValue=MyIntField FROM MyTable WHERE MyPrimaryKeyField = @Param
RETURN @ReturnValue
END

In questo modo posso chiamare la procedura come:

DECLARE @SelectedValue int
EXEC @SelectedValue = GetMyInt 999

Questo metodo però ha due importanti limitazioni:

• Il RETURN può fornire solo valori interi, NULL è convertito a 0 e le stringhe forniscono un errore di Conversion failed when converting the varchar value to data type int.
• Il metodo può fornire un solo valore di ritorno

Output Parameter

In fase di dichiarazione di una procedura posso dichiararne i parametri di OUTPUT, come nel seguente esempio:

CREATE PROCEDURE GetMyInt( @Param int, @OutValue int OUTPUT)
AS
SELECT @OutValue=MyIntField FROM MyTable WHERE MyPrimaryKeyField = @Param
END

La chiamata avviene nel seguente modo:

DECLARE @SelectedValue int
EXEC GetMyInt 999, @SelectedValue OUTPUT

La differenza fondamentale con il metodo indicato sopra è il valore può essere di qualsiasi tipo, anche se può essere comunque un solo valore.

Result Set

Questo metodo è particolarmente utile quando voglio fornire un rowset come output di una procedura e non un solo valore. Abbiamo la dichiarazione della procedura nel seguente modo:

CREATE PROCEDURE GetMyInt(@Param int)
AS
SELECT MyIntField FROM MyTable WHERE MyPrimaryKeyField = @Param
END

E’ importante notare di come questa sia la procedura più “spoglia”, non presenta ne un RETURN ne un OUTPUT, semplicemente una generica SELECT. Per chiamarla scrivo, per esempio:

DECLARE @ResultSet table (SelectedValue int)
INSERT INTO @ResultSet (SelectedValue)
EXEC GetMyInt @Param
SELECT * FROM @ResultSet

Questo metodo risolve entrambe le limitazioni dei metodi precedenti in quanto:

• Posso ritornare un qualsiasi numero di righe
• I valori possono essere di qualsiasi tipo

Questo approccio necessità della creazione della tabella temporanea anche se viene ritornato un solo record.

Variabili

Dichiarazione

Ogni variabile utilizzata all’interno di una stored procedure (o di un batch) deve essere dichiarata assieme al suo datatype attraverso una istruzione DECLARE ed in seguito possiamo assegnarle un valore attraverso una istruzione SET o SELECT. Le variabili possono essere dichiare su riga singola:

DECLARE @VARIABILE_TESTO AS VARCHAR(300)

oppure su più linee:

DECLARE 
@VARIABILE_NUMERO AS INTEGER,
@VARIABILE_TESTO AS CHAR(300),
@ VARIABILE_TESTOUNICODE NVARCHAR(500)

In T-SQL le variabili debbono interdirsi come variabili locali, cioè hanno significato solo all’interno del batch (gruppo o sequenza di istruzioni T-SQL) o della stored procedure nella quale vengono dichiarate , fuori da questo contesto perdono significato. E’ possibile passare variabili da una procedura ad un’altra.

Assegnamento

Per assegnare un valore ad una variabile dopo averla dichiarata dobbiamo usare una istruzione SET o SELECT, la cui sintassi è la seguente:

SELECT { @ variabile_locale = espressione } [ ,...n ]

L’espressione può essere una qualsiasi altra variabile un numero , una stringa, una data oppure il valore risultante dalla chiamata di altre funzioni valide per SQL Server. Posso anche fare degli pseudo cicli all’interno del select, nel caso in cui questo ritorni più righe.

SELECT @au_lname = @au_lname + ‘,’ + au_lname FROM authors 

N.B: SQL server quando recupera righe vuote da una SELECT assegna alla variabile il valore che aveva prima dell’istruzione SELECT usata per l’assegnazione, in questo caso il mio nome.

La differenza tra SET e SELECT è che SET può assegnare solo una variabile alla volta mentre la SELECT anche più variabili.

Batch SQL e l’istruzione GO

Un batch SQL è un insieme di comandi SQL inviati come gruppo al server SQL per l’esecuzione. Ogni batch comporta:

• Sincronizzazione tra client e server.
• Parsing, esecuzione dei comandi e invio di un codice di stato di successo o errore al client.

Il comando GO non è un’istruzione T-SQL, ma un segnale per il client che separa i comandi in batch distinti, da inviare uno per volta al server.

Nota: GO funziona singolarmente, dividendo i comandi in batch. Ad esempio:

DELETE FROM a
DELETE FROM b
DELETE FROM c

invio una singola query con tre comandi al server. Invece:

DELETE FROM a
GO
DELETE FROM b
GO
DELETE FROM c

invio tre batch distinti, uno per ogni comando DELETE.

Alcuni comandi richiedono un batch dedicato per funzionare correttamente. Ad esempio:

• DROP TABLE e CREATE TABLE (se usati sullo stesso nome).
• Comandi come CREATE/ALTER PROCEDURE o ADD COLUMN devono essere i primi nel loro batch.

Profiler

Esiste un profiler che permette di visualizzare tutte le query che vengono effettuate su un determinato database. Si accede tramite Tools -> Sql Server Profiler. Per debuggare i tempi di esecuzione di una query posso fare Query -> Include Execution Plan che aggiunge una terza tab dopo l’esecuzione di una query che mostra ogni operazione della query quanto tempo ci mette.

Trick

Concatenare variabili a stringhe

@sql = 'foo' + CAST(@variable AS NVARCHAR(MAX)) + 'bar';

Eseguire una query se esiste un valore in una tabella

Letteralmente significa “Ritorna 1 dalla tabella” e viene spesso utilizzato in combinazione con WHERE EXISTS in modo da verificare (se la select 1 fornisce un risultato) che la tabella esista.

SELECT * FROM TABLE1 T1 WHERE EXISTS (
SELECT 1 FROM TABLE2 T2 WHERE T1.ID= T2.ID
);

Oppure posso usarlo, genericamente, quando voglio verificare che una tabella esista.

Verificare la presenza di un parametro in ingresso in una Stored

IF (ISNULL(@fooInt, 0) = 0)
BEGIN
RAISERROR('Invalid parameter: @fooInt cannot be NULL or zero', 18, 0)
RETURN
END

Verificare se un sp_executesql ha dato risultati

Usare il @@ROWCOUNT che indica il numero di righe ritornate dall’ultima query eseguita

EXEC sp_executesql @sql;
IF @@ROWCOUNT = 0
BEGIN
RETURN;
END

Trasformare il risultato di una select in una stringa

Talvolta può capitare l’esigenza di fornire come risultato di una query non una lista di campi, ma un solo campo formato da una lista concatenata, che sono i valori risultanti della query in questione.

Assumiamo di avere una SELECT che mi ritorna una colonna di valori, per esempio

SELECT Field FROM Table

Che fornisce:

Field
aaa
bbb
ccc
ddd

Ora voglio concatenare i risultati di questa query in una stringa separata da virgola:

aaa,bbb,ccc,ddd

Per ottenere questo risultato è necessario seguire il workaround spiegato in seguito.

Per prima cosa utilizzo la funzione for XML path modificando la query nel seguente modo:

SELECT f
FROM ( SELECT Field FROM TABLE ) AS fields
FOR XML PATH('')
, root('ROOTNODE')
, type

In questo modo l’output ora è un file XML:

<ROOTNODE>
<f>aaa</f>
<f>bbb</f>
<f>ccc</f>
<f>ddd</f>
</ROOTNODE>

Ora devo eliminare il tag <f>, per far ciò modifico la query nel seguente modo:

SELECT ',' + f
FROM ( SELECT Field FROM TABLE ) AS fields
FOR XML PATH('')
, root('ROOTNODE')
, type

Ottenendo così:

<ROOTNODE>,aaa,bbb,ccc,ddd</ROOTNODE>

Ora per eliminare anche il <ROOTNODE> uso la funzione value nel seguente modo:

SELECT (
SELECT ',' + f
FROM (
SELECT Field
FROM TABLE
) AS fields
FOR XML PATH('')
, root('ROOTNODE')
, type
).value('/ROOTNODE[1]', 'nvarchar(max)')
 

Che mi permette di ottenere:

,aaa,bbb,ccc,ddd

L’ultimo punto è eliminare la prima virgola, per far ciò utilizzo la funzione STUFF che consente di inserire una stringa in un’altra stringa eliminando un numero di caratteri specificato nella posizione iniziale della prima stringa e inserendo la seconda stringa in tale posizione.

SELECT STUFF((
SELECT ',' + f
FROM (
SELECT Field
FROM TABLE
) AS fields
FOR XML PATH('')
, root('ROOTNODE')
, type
).value('/ROOTNODE[1]', 'nvarchar(max)'), 1, 1, '')

Ottenendo finalmente

aaa,bbb,ccc,ddd

che è il risultato atteso.

Usare SplitFn per ottenere una tabella a partire da una stringa

Nel caso in cui io debba gestire dei campi formati da ID (per esempio) separati da virgola, devo avere un modo per trasformare questi in una tabella in modo da poterla poi usare in delle altre query. Per questa esigenza viene in soccorso la funzione Selfcare.SplitFn che prende in ingresso una stringa e un separatore fornendo una tabella con i risultati separati.

SELECT * FROM Selfcare.SplitFn('ciao, come, va', ',')

items
ciao
come
va

Concatenare una lista (anche vuota) ad una query

Con il controlla ISNULL appendo il selettore IN solo se la lista non è vuota

DECLARE @sql NVARCHAR(MAX)
SET @sql = 'DELETE FROM dbo.ReclamiSedi WHERE IDReclamo = ' + CONVERT(VARCHAR(10), @IDReclamo)
IF ISNULL(@ListaSedi, '') <> ''
SET @sql = @sql + ' AND IDSede NOT IN (' + @ListaSedi + ')'
EXEC sp_executesql @sql

Fornire una tabella custom come output con una sola SELECT

Il trucco è sfruttare il comando AS del SELECT: Se io ho la seguente query:

SELECT foo FROM bar

Il risultato sarà una tabella monocolonna con n righe, in cui il titolo della colonna sarà foo e come contenuto dei record ci saranno quelli del campo foo della tabella bar. Ora se io invece metto nel SELECT delle costanti, cosa succede?

SELECT 1, foo FROM bar

In questo caso avrò due colonne: la prima con valore costante 1, la seconda invece come prima. Non avendo utilizzato il comando AS la prima colonna non avreà nessun nome. Se invece faccio

SELECT 1 AS costante_uno, foo FROM bar

Ottengo che il nome della prima colonna sarà “costante_uno”. Ora assumiamo di avere n query che restituiscono 0 o 1 in base al fatto che esista un record, voglio ritornare questi risultati in una nuova tabella con delle colonne nominate come voglio: la soluzione è usare una SELECT nel seguente modo:

SELECT 0 AS col_1
, CASE 
WHEN EXISTS (
SELECT TOP 1 1
...
)
THEN 1
ELSE 0
END AS col_2

Ferrà ritornata una tabella con la prima colonna chiamata col_1 e la seconda col_2. Allo stesso modo posso ritornare, in una SELECT, i risultati di una altra SELECT.

SELECT 0 AS col_1
, (SELECT TOP 1 foo from bar) AS col_2

Inserire un valore in una colonna identità

Disattivo temporaneamente i controlli sull’autoincrement con il comando SET IDENTITY_INSERT table_name ON ed eseguo l’INSERT. Nota bene che devo obbligatoriamente elencare le colonne dopo il nome della tabella e deve essere un INSERT SELECT e non un INSERT VALUES. In questo esempio viene anche eseguito un controllo sul

SET IDENTITY_INSERT table_name ON
INSERT INTO table_name (col_1, col_2)
SELECT T.*
FROM (
SELECT 999 AS dummy_name, 'bar' AS dummy_name_2
) T
WHERE NOT EXISTS (
SELECT 1
FROM table_name 
WHERE col_1 = 999
);
SET IDENTITY_INSERT table_name  OFF

Eseguire un UNION con ordinamento

Quando eseguo una UNION SQL non vuole che le due SELECT da unire presentino un ordinamento. Per risolvere il problema utilizzo il seguente trucco:

SELECT *
FROM (
SELECT TOP 1 foo AS col_name
FROM table_name
ORDER BY col ASC
) DummyAlias1
 
UNION ALL
 
SELECT *
FROM (
SELECT TOP 100 PERCENT bar AS col_name
FROM table_name_2
ORDER BY col DESC
) DummyAlias2

UPDATE se esiste, altrimenti INSERT

Metodo 1 (da eseguire in una transazione):

UPDATE foo
SET Name = @Name
WHERE UnitMeasureCode = @UnitMeasureCode
IF (@@ROWCOUNT = 0 )
BEGIN
INSERT INTO foo (UnitMeasureCode, Name)
VALUES (@UnitMeasureCode, @Name)
END

Metodo 2 (consigliato):

-- =============================================
-- MERGE: dichiara la tabella da considerare
-- =============================================
MERGE foo
-- =============================================
-- USING: dichiara le variabili da utilizzare e le associa a source.var_1, socurce.var_2
-- =============================================
USING (
SELECT @UnitMeasureCode, @Name
) AS source(UnitMeasureCode, NAME)
-- =============================================
-- ON: clausola WHERE nella tabella foo
-- =============================================
ON (foo.UnitMeasureCode = source.UnitMeasureCode)
-- =============================================
-- WHEN MATCHED: se la clausola ON da TRUE (IF EXISTS SELECT TOP 1 1 FROM foo WHERE foo.UnitMeasureCode = source.UnitMeasureCode)
-- =============================================
WHEN MATCHED
-- =============================================
-- Aggiorna il nome solo nelle colonne considerate
-- =============================================
THEN
UPDATE
SET NAME = source.NAME
-- =============================================
-- Se invece non trova nessun record esegue il normale inserimento
-- =============================================
WHEN NOT MATCHED
THEN
INSERT (UnitMeasureCode, NAME)
VALUES (source.UnitMeasureCode, source.NAME)

Cercare tutti gli oggetti modificati meno n giorni fa da me

SELECT o.modify_date
, o.NAME
FROM sys.objects AS o
INNER JOIN sys.sql_modules AS m ON o.[object_id] = m.[object_id]
WHERE o.modify_date >= DATEADD(day, - 20, GETDATE())
AND m.DEFINITION LIKE '%myName%'
ORDER BY o.modify_date DESC

Cercare tutti gli oggetti modificati meno n giorni fa

Come da titolo, questa query permette di avere informazioni su tutti gli oggetti (quindi viste, stored procedure…) che sono stati modificati meno di un numero arbitrario di giorni fa, nell’esempio seguente 20:

SELECT o.modify_date, o.name, m.definition
FROM sys.objects AS o
INNER JOIN sys.schemas AS s ON o.[schema_id] = s.[schema_id]
INNER JOIN sys.sql_modules AS m ON o.[object_id] = m.[object_id]
WHERE o.modify_date >= DATEADD(day,-20, GETDATE())

Cercare tutti gli oggetti che contengono una stringa

Quante volte può capitare di dover ricercare nel DB tutti gli oggetti che utilizzano una determinata stored, oppure gli oggetti che contengono un commento TODO o TOFIX. Esistono numerosi tool per eseguire tali ricerce ma spesso la soluzione più veloce e semplice è la conoscenza delle tabelle di sistema.

SELECT o.NAME AS Object_Name
, o.type_desc
, o.modify_date
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.DEFINITION LIKE '%usp_Config_GetByID%'
ORDER BY o.modify_date DESC

Cercare tra i nomi di tutte le colonne

Analogamente a quanto descritto sopra, spesso non ho la necessità di sapere se esiste una colonna con un tal nome all’interno del mio DB (sopratutto se questo è di dimensioni molto grandi e difficilmente consultabile). Per ottenere questa informazione utilizziamo le viste Information Schema, che meriteranno un approfondimento a parte.

SELECT COLUMN_NAME, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%column_name%'

Cercare tutti i processi lanciati da me in questo momento

Lavorando con query complesse spesso nasce la necessità di voler avere sottocontrollo i processi aperti e quante risorse questi stanno utilizzando sul server. Per poter fare ciò sono dipsonibili delle stored procedure di sistema, come sp_who, sp_who2 e sp_who4. Il codice seguente inserisce i valori forniti dalla stored sp_who2 in una tabella, in modo che questi possano essere facilmente interrogati con una query.

DECLARE @ResultSet TABLE (SPID INT, STATUS VARCHAR(100), LOGIN VARCHAR(100), HostName VARCHAR(100), BlkBy VARCHAR(100), DBName VARCHAR(100), Command VARCHAR(100), CPUTime INT, DiskIO INT, LastBatch VARCHAR(100), ProgramName VARCHAR(100), SPID2 INT, REQUESTID INT)
INSERT INTO @ResultSet EXEC sp_who2
SELECT * FROM @ResultSet AS rs WHERE rs.HostName = 'NBMYNAME' AND rs.ProgramName = 'Microsoft SQL Server Management Studio - Query'

Confrontare le colonne della stessa tabella tra due differenti database

Talvolta può capitare di dover verificare eventuali disallineamenti di colonne per la stessa tabella tra due database diversi (solitamente tra il database di staging e il database di produzione). Per trovare le differenze a livello di colonne tra due diversi database viene in aiuto la seguente query.

DECLARE @table_name varchar(50);
SET @table_name = 'calendarioFatturazione';
SELECT 'BillingTLP.dbo.' + c2.table_name AS 'Tabella di riferimento'
, c2.COLUMN_NAME, CAST(c2.data_type AS varchar(30)) + '(' + CAST(c2.character_maximum_length AS varchar(20)) + ')' AS Column_type
FROM BillingTLP.INFORMATION_SCHEMA.COLUMNS c2
WHERE table_name = @table_name
AND c2.COLUMN_NAME NOT IN (
SELECT column_name
FROM BillingTLP_QTYbis.INFORMATION_SCHEMA.COLUMNS
WHERE table_name = @table_name
)
UNION
SELECT 'BillingTLP_QTYbis.dbo.' + c2.table_name AS 'Tabella di riferimento'
, c2.COLUMN_NAME, CAST(c2.data_type AS varchar(30)) + '(' + CAST(c2.character_maximum_length AS varchar(20)) + ')' AS Column_type
FROM BillingTLP_QTYbis.INFORMATION_SCHEMA.COLUMNS c2
WHERE table_name = @table_name
AND c2.COLUMN_NAME NOT IN (
SELECT column_name
FROM BillingTLP.INFORMATION_SCHEMA.COLUMNS
WHERE table_name = @table_name
)

Trasformare i risultati di una query in coppia chiave/valore

In questo brevissimo articolo spiegherò come trasformare i risultati di una qualsiasi query in una coppia chiave/valore, dove la chiave è il nome della colonna e il valore il valore effettivo. Questa può essere utile nel caso abbia una tabella che abbia valorizzato dei campi con nomi di colonne di un’altra tabella, e abbia la nece3ssità di andare in JOIN su queste. Prendiamo ora la seguente query, molto semplice:

SELECT Foo FROM Bar

Che fornisce

Foo
100
200
300
400
500

Per trasformare questo risultato in una coppia chiave valore, utilizzo la funzione UNPIVOT (che eventualmente spiegherò in un altro post).

SELECT Key, Value
FROM Bar
UNPIVOT(Valore FOR Chiave IN (Foo))

Chiave	Valore
Foo	100
Foo	200
Foo	300
Foo	400
Foo	500

Script onnicomprensivi T-SQL

SELECT

SELECT
-- =============================================
-- selezione di colonna standard
-- =============================================
foo
-- =============================================
-- Selezione di colonna cambiando il nome della colonna ritornata
-- =============================================
, foo_2 AS changed_name_col
-- =============================================
-- colonna impostata sempre a 'const'
-- =============================================
, 'const' AS const_col
-- =============================================
-- colonna impostata sempre a NULL
-- =============================================
, NULL AS Nome
-- =============================================
-- concatenare campi string con campi interi
-- =============================================
, string_field + CAST(int_field AS VARCHAR(20)) + '$' + CAST(int_field_2 AS VARCHAR(20)) + '$'
-- =============================================
-- colonna booleana che dipende dall'esistenza o meno di un valore in una altra tabella
-- =============================================
, CASE 
WHEN EXISTS (
SELECT TOP 1 1
FROM bar
WHERE foo = 1
)
THEN 1
ELSE 0
END AS boolean_col_exists
-- =============================================
-- valore booleano nel caso in cui una colonna non sia null e una seconda colonna sia LIKE una variabile
-- =============================================
, CASE 
WHEN field_name IS NOT NULL
AND (field_name_2 LIKE '%' + @var + '%')
THEN 1
ELSE 0
END AS boolean_col_like
-- =============================================
-- colonna che viene popolata tramite un SELECT da una altra tabella
-- =============================================
, (
SELECT TOP 1 foo
FROM bar
) AS select_col
-- =============================================
-- il valore della colonna è una concatenazione di valori di colonna con costanti
-- =============================================
, field + CASE another_field
WHEN 2
THEN ' [chiuso]'
WHEN 3
THEN ' [riaperto]'
ELSE ''
END AS concatenated_col
-- =============================================
-- conversione da NULL/stringa a booleani 0/1
-- =============================================
, CASE 
WHEN field_that_could_be_null IS NULL
THEN 0
ELSE 1
END AS boolean_col_if_null
-- =============================================
-- cambio il valore della colonna in base al valore della sua prima lettera
-- =============================================
, CASE 
WHEN LEFT(field_name, 1) = '!'
THEN SUBSTRING(field_name, 2, LEN(field_name) - 1)
ELSE field_name
END AS first_letter_col
-- =============================================
-- colonna stringa booleana (M/F) in base al valore di una sottostringa di un campo iniziale
-- =============================================
, CASE 
WHEN CAST(SUBSTRING(field_name, 10, 2) AS INT) > 40
THEN 'F'
ELSE 'M'
END AS
-- =============================================
-- metto i valori di un campo o di un altro in base all'uguaglianza o meno di due variabili
-- =============================================
, CASE 
WHEN (@var_1 = @var_2)
THEN field_name_1
ELSE field_name_2
END AS col_that_depends_on_var_values
-- =============================================
-- Solo quando l'app name è un determinato valore (Connection string: Application Name=fo;)
-- =============================================
, CASE 
WHEN app_name() = 'fo'
THEN foo
ELSE NULL
END AS Utente
-- =============================================
-- il valore della colonna è la concatenzazione dei valori da una altra tabella
-- =============================================
, (
SELECT STUFF((
SELECT ',''' + CAST(field_to_concatenate AS VARCHAR) + ''''
FROM table_with_field_that_have_to_be_concatenated
FOR XML PATH('')
, root('ROOTNODE')
, type
).value('/ROOTNODE[1]', 'nvarchar(max)'), 1, 1, '')
) AS concatenated_col
FROM bar
-- =============================================
-- confronto il valor di un campo in base al valore di una variabile
-- =============================================
WHERE bar_2 = CASE 
WHEN @local_var = 10
THEN 1
ELSE 0
END
-- =============================================
-- il valore del campo non deve esistere in una altra tabella
-- =============================================
AND (
field_name NOT IN (
SELECT foo
FROM bar
)
)
-- =============================================
-- confronto date validità con la data attuale
-- =============================================
AND GetDate() BETWEEN ValidoDal
AND ValidoAl
-- =============================================
-- Ordinamento custom: Quelle che si chiamano 'Aperto' vanno per prime, e così via
-- =============================================
ORDER BY CASE col
WHEN 'Aperto'
THEN 1
WHEN 'Chiuso'
THEN 2
ELSE 3
END

IF

-- =============================================
-- se esiste alemno un record in una tabella
-- =============================================
IF EXISTS(SELECT TOP 1 1 FROM foo WHERE bar = @var)
-- =============================================
-- Se una variabile è uguale al risultato di una SELECT
-- ============================================= 
IF @var_2 = ( SELECT TOP 1 foo FROM bar WHERE field_= @var )
-- =============================================
-- controllo l'esistenza di variabili
-- =============================================
IF ISNULL(@string_var, '') = ''
IF ISNULL(@int_var, 0) = 0
-- =============================================
-- controlla se i risultati di un SELECT sono un sottoinsieme dei risultati di una seconda query
-- =============================================
IF (SELECT foo FROM bar WHERE field_name = 'string') IN (SELECT DISTINCT field_1 From bar_1 where id = @var)
-- =============================================
-- Se una variabile non è settata la setto a 1 se esiste una config corrispondente, altrimenti 0
-- =============================================
IF ISNULL(@bool_var, 0) = 0
BEGIN
SET @bool_var = CASE WHEN EXISTS (SELECT TOP 1 1 FROM dbo.Config WHERE IDConfig = 'OENext_BancheMultiple' AND VConfig = '1') THEN 0 ELSE 1 END;
END
-- =============================================
-- Se il numero di record forniti da una query è > di k
-- =============================================
IF ( SELECT COUNT(*) FROM foo WHERE bar=@var ) > 10
-- =============================================
-- Se il valore di ritorno di una SELECT è null
-- =============================================
IF ( SELECT TOP 1 ISNULL(field_that_could_be_null, '') FROM bar ) = ''
-- =============================================
-- Confronto una parte di una stringa con un intero
-- =============================================
IF(CAST(SUBSTRING(@CFisc, 10, 2) AS INT) > 40)

Extra

• T-SQL - Condizioni di ricerca dinamiche
• T-SQL - Tabelle temporanee VS variabili di tabella
• T-SQL - Usare variabili @ in query
• T-SQL Information schema
• T-SQL Ricerca full-text