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

Sergio Govoni 2 Gennaio 2011 Senza categoria 2,942 Views

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

UGISS User Group Italiano SQL Server

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

Chi è Sergio Govoni

Leggi Anche

Azure Key Vault e certificati code-signing: Strategie per la conformità post 1° Giugno 2023!