SQL Server 2008: Pensare Set based

Pensare set based è il metodo più efficiente per risolvere problematiche complesse mantenendo le performance del nostro DB a livelli elevati. Non sempre è semplice pensare set based perché la nostra mente è orientata al ragionamento row based che però ha molte controindicazioni.
Iterare su un insieme di righe, magari attraverso l'uso di cursori o di cicli, una soluzione nella stragrande maggioranza dei casi comporta gravi problemi correlati alle performance, rende il codice prolisso e di difficile debug, difficilmente scalabile e soggetto a bug.
Al contrario, pensare una soluzione set based, rende il codice molto più leggibile, compatto e scalabile. Per riuscire a ragionare Set based dobbiamo però conoscere gli strumenti che la piattaforma SQL Server ci mette a disposizione.
Uno degli strumenti, introdotto dalla versione SQL Server 2005 ma che ancora non è molto utilizzato, è la Common Table Expression o CTE.

Qui trovate la documentazione ufficiale.

Vediamo un piccolo esempio di utilizzo delle CTE per creare query ricorsive.
Immaginate di avere una tabella contenente l'anagrafica dipendenti della vostra azienda. Ogni riga dipendente ha un proprio Id, il nome e l'id del diretto responsabile e avete la necessità di calcolare il livello d'appartenenza di ogni persona censita e di visualizzare il diretto responsabile.
Grazie alle CTE possiamo, con un'unica query, raggiungere l'obiettivo prefissato.

Vediamo come Preparo una variabile di tipo tabella contenente la lista dei dipendenti

Use TempDb
Go

--Preparo la tabella anagrafica
Declare @Dipendenti Table
(
 idDipendente tinyint,
 Nome varchar(50),
 idResponsabile tinyint
)

--la popolo
Insert into @Dipendenti values(1,'Amministratore Delegato',NULL)
       ,(2,'Responsabile di Unità operativa 1',1)
       ,(3,'Responsabile di Unità operativa 2',1)
       ,(4,'Capo progetto 1',2)
       ,(5,'Capo progetto 2',3)
       ,(6,'Impiegato1',4)
       ,(7,'Impiegato2',4)
       ,(8,'Impiegato3',4)
       ,(9,'Impiegato4',4)
       ,(10,'Impiegato5',5)
       ,(11,'Impiegato6',5)
       ,(12,'Impiegato7',5)
       ,(13,'Impiegato8',5)


--Ecco la CTE
;With CTELivello
as
(
 --Reperisco i membri di anchor, quelli che nel nostro caso
        --non hanno censito il resposabile diretto, quindi 'Amministratore Delegato'.
        Select idDipendente,
   Nome as NomeDipendente,
   Cast('' as varchar(50))as NomeResponsabile ,
   idResponsabile,
   0 as Livello
 From @Dipendenti
  Where idResponsabile is Null
 
        --Grazie alla union all reperiamo tutti i membri ricorsivi
 Union all
 
 Select Dip.idDipendente,
   Dip.Nome as NomeDipendente,
   Resp.NomeDipendente as NomeResponsabile,
   Dip.idResponsabile,
   Livello + 1 as Livello
 From @Dipendenti Dip
  Inner Join CTELivello Resp
   on Dip.idResponsabile = Resp.idDipendente
)
Select 
 Replicate('>',Livello) + ' ' + NomeDipendente as [Nome dipendente]
 ,NomeResponsabile as [Responsabile diretto]
 ,Livello 
from CTELivello  

Vediamo nel dettaglio come funziona. Il primo passo è reperire il/i membri di anchor, vale a dire le righe danno il via alla ricorsione.

;With CTELivello
as
(
 --Reperisco i membri di anchor, quelli che nel nostro caso
        --non hanno censito il resposabile diretto, quindi 'Amministratore Delegato'.
        Select idDipendente,
   Nome as NomeDipendente,
   Cast('' as varchar(50))as NomeResponsabile ,
   idResponsabile,
   0 as Livello
 From @Dipendenti
  Where idResponsabile is Null

Come potete vedere Reperisco l'id del dipendente, il suo nome, stringa vuota come nome reponsabile diretto, l'id del responsabile e 0 come livello.
Perchè stringa vuota come responsabile e 0 come livello ?
Perchè sono i membri di anchor, non hanno responsabile diretto ed appartengono al livello 0.
Queste colonne mi servono in quanto la union all formatta le colonne della seconda parte della query utilizzando quelle della prima select.
 La seconda parte della CTE è quella che implementa la ricorsione su tutti i membri secondari

--Grazie alla union all reperiamo tutti i membri ricorsivi
 Union all
 
 Select Dip.idDipendente,
   Dip.Nome as NomeDipendente,
   Resp.NomeDipendente as NomeResponsabile,
   Dip.idResponsabile,
   Livello + 1 as Livello
 From @Dipendenti Dip
  Inner Join CTELivello Resp
   on Dip.idResponsabile = Resp.idDipendente
)
Select 
 Replicate('>',Livello) + ' ' + NomeDipendente as [Nome dipendente]
 ,NomeResponsabile as [Responsabile diretto]
 ,Livello 
from CTELivello 

In pratica per ogni membro di anchor vengono ricercati tutti i membri ricorsivi mettendo in join la tabella dipendenti co la CTE stessa tra il campo idresponsabile della tabella stessa ed il campo id dipendente della CTE che mano a mano si va a popolare.
Inoltre effettuo per ogni membro ricorsivo il ricalcolo del livello (Livello + 1) basandomi sul livello del membro di anchor in iterazione.
Ed ecco il risultato :

Ciao

Luca