Home > Articoli > About OFFSET/FETCH options (T-SQL) in SQL Server Code-Name “Denali”

About OFFSET/FETCH options (T-SQL) in SQL Server Code-Name “Denali”

Nello scorso mese di novembre, durante il PASS Summit 2010, Microsoft ha annunciato la disponibilità della prima community technology preview (CTP) di SQL Server 2011 noto anche con il Code-Name "Denali". Dopo l’annuncio, la CTP1 è stata resa disponibile per il download. La documentazione online (BOL) descrive le nuove funzionalità del linguaggio T-SQL.

La clausola ORDER BY (utilizzata per ordinare il result set di una query), in SQL Server 2011 Code-Name "Denali", è stata arricchita con alcune interessanti opzioni, che permettono di semplificare la costruzione personalizzata delle pagine del result set di una query. Si tratta delle clausole OFFSET e FETCH di cui vedremo una preview in questo articolo.

L’opzione OFFSET:

OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }

Permette di specificare il numero di righe da saltare prima di iniziare (avviare) la restituzione del result set di una query. I valori specificati per OFFSET devono essere costanti di tipo intero o espressioni il cui valore restituito sia maggiore o uguale a zero.

L’opzione FETCH:

FETCH { FIRST | NEXT } { integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY

Permette di specificare il numero di righe che dovranno essere restituite dopo aver processato la clausola OFFSET. Come per la clausola OFFSET, i valori specificati per FETCH devono essere costanti di tipo intero o espressioni il cui valore restituito sia maggiore o uguale a zero.

L’utilizzo delle opzioni OFFSET e FETCH permette di tradurre semplicemente, in linguaggio T-SQL, la seguente richiesta: Si richiede di ottenere un result set composto da X righe a partire dall’ennesimo record (riferito all’intero data set), che in altre parole significa voler costruire ad hoc le pagine del result set di una query. Le clausole OFFSET/FETCH sono state implementate, principalmente, per rispondere a questa necessità.

Nelle precedenti versioni di SQL Server, questo scenario poteva essere risolto utilizzando due elementi del linguaggio T-SQL: TOP e ROW_NUMBER(). Entrambi gli elementi, però, non soddisfano in pieno la richiesta, l’espressione TOP permette di limitare il numero di righe restituite, ma non permette di saltare, ad esempio, un range di righe. La funzione ROW_NUMBER() permette di filtrare esattamente il range desiderato, ma non può essere utilizzata nella clausola WHERE di una query… per ottenere il risultato richiesto (con SQL Server 2008 e precedenti) è necessario ricorrere ad un workaround che sfrutta Common Table Expression (CTE).

 

Scenario

Si richiede in output l’estrazione dei Contatti presenti nella tabella Person.Contact del database AdventureWorks paginando le righe in gruppi di dieci.

 

Soluzione (per SS2008 e SS2005)

Nelle versioni precedenti a SQL Server "Denali", si può utilizzare la seguente CTE:

————————————————————————
— Ad hoc paging prior SQL Server Code-Name "Denali"
————————————————————————

— Change database context
use [AdventureWorks];
go

— First 10 rows
WITH CTE AS
(
  SELECT
    ROW_NUMBER() OVER (ORDER BY LastName, FirstName) AS RowNum
 ,LastName
 ,FirstName
 ,Phone
  FROM
    Person.Contact
)
SELECT
  *
FROM
  CTE
WHERE
  RowNum BETWEEN 0 AND 10
ORDER BY
  RowNum;
go

…il cui output è illustrato in figura 1.

Figura 1 – Ad hoc paging prima di SQL Server "Denali" (primo gruppo di dieci righe).

Analogamente, il secondo gruppo di dieci righe potrà essere ottenuto con la prossima CTE, modificata rispetto alla precedente nella outer query:

— Next 10 rows
WITH CTE AS
(
  SELECT
    ROW_NUMBER() OVER (ORDER BY LastName, FirstName) AS RowNum
 ,LastName
 ,FirstName
 ,Phone
  FROM
    Person.Contact
)
SELECT
  *
FROM
  CTE
WHERE
  –RowNum BETWEEN 0 AND 10
  RowNum BETWEEN 11 AND 20
ORDER BY
  RowNum;
go

Il secondo gruppo di righe, ottenuto in output, è illustrato in figura 2.

Figura 2 – Ad hoc paging prima di SQL Server "Denali" (secondo gruppo di dieci righe).

 

Soluzione (con SSDenali)

Le nuove opzioni di filtro OFFSET/FETCH possono essere interpretare come estensioni della clausola ORDER BY. Il seguente frammento di codice T-SQL illustra come eseguire l’estrazione delle prime dieci righe del result set (ordinato) della query richiesta, utilizzando le opzioni OFFSET/FETCH:

— First 10 rows
SELECT
  LastName
  ,FirstName
  ,Phone
FROM
  Person.Contact
ORDER BY
  LastName
  ,FirstName
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
go

L’output (analogo a quello di figura 1) è illustrato in figura 3.

Figura 3 – Ad hoc paging in SQL Server "Denali" (primo gruppo di dieci righe).

In modo analogo otteniamo anche il secondo gruppo di dieci righe:

— Next 10 rows
SELECT
  LastName
  ,FirstName
  ,Phone
FROM
  Person.Contact
ORDER BY
  LastName
  ,FirstName
–OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

Figura 4 – Ad hoc paging in SQL Server "Denali" (secondo gruppo di dieci righe).

 

Piani di esecuzione a confronto

Prima di trarre le conclusioni, diamo uno sguardo ai piani di esecuzione delle query utilizzate per la selezione del primo gruppo di dieci righe. In SSMS, dopo aver selezionato Include Actual Execution Plan, eseguiamo le due query (precedenti figure 1 e 3)… l’output ottenuto è illustrato in figura 5.

Figura 5 – Query Execution Plan

I piani di esecuzione generati hanno differenze minime, nella prima query (quella che utilizza CTE) troviamo in più, un Filter Task (per la presenza della clausola WHERE) e un Sequence Project Task (per la presenza della funzione ROW_NUMBER). Al netto di queste piccole differenze, i piani di esecuzione (per questo esempio) appaiono simili.

 

Conclusioni

Confrontate con l’operatore TOP, le opzioni OFFSET/FETCH hanno due importanti vantaggi: sono standard e forniscono la possibilità di saltare un range di righe. Confrontate con la funzione ROW_NUMBER(), per risolvere lo scenario descritto in questo articolo, le opzioni OFFSET/FETCH hanno il vantaggio di poter lavorare senza il layer Common Table Expression (CTE), e questo rende il codice più leggibile (specialmente per chi non lo ha scritto) e più semplice da manutenzionare.

Dal confronto dei piani di esecuzione (in figura 5) non sono emerse differenze prestazionali tra l’utilizzo di ROW_NUMBER() e l’utilizzo di OFFSET/FETCH (per lo scenario descritto).

 

Risorse esterne

Per il download del DB AdventureWorks in versione Denali, seguite le istruzioni indicate in questo articolo:

http://www.ugiss.org/Content/Article/AdventureWorks-DBs-for-SQL-Server-Denali-.aspx 

Chi è Sergio Govoni

Sergio Govoni è laureato in Scienze e Tecnologie Informatiche. Da oltre 16 anni lavora presso una software house che produce un noto sistema ERP, distribuito a livello nazionale ed internazionale, multi azienda client/server su piattaforma Win32. Attualmente si occupa di progettazione e analisi funzionale, coordina un team di sviluppo ed è responsabile tecnico di prodotto. Lavora con SQL Server dalla versione 7.0 e si è occupato d'implementazione e manutenzione di database relazionali in ambito gestionale, ottimizzazione delle prestazioni e problem solving. Nello staff di UGISS si dedica alla formazione e alla divulgazione in ambito SQL Server e tecnologie a esso collegate, scrivendo articoli e partecipando come speaker ai workshop e alle iniziative del primo e più importante User Group Italiano sulla tecnologia SQL Server. Ha conseguito la certificazione MCP, MCTS SQL Server. Per il suo contributo nelle comunità tecniche e per la condivisione della propria esperienza con altri, dal 2010 riceve il riconoscimento SQL Server MVP (Microsoft Most Valuable Professional). Nel corso dell'anno 2011 ha contribuito alla scrittura del libro SQL Server MVP Deep Dives Volume 2 (http://www.manning.com/delaney/).

Leggi Anche

Unit testing: Come scrivere la tua prima unit test!

Nell’articolo precedente, il secondo di questa serie, abbiamo descritto come installare il framework tSQLt, il …