It amazes me how much easier certain tasks have gotten in SQL Server. I was watching the Nov 2014 SQL Skills insiders video on OFFSET and FETCH by Jonathan Kehayias (b/t) and remembered fondly an interview I did many, many years ago. As part of the technical interview I was asked to write a pagination query. A query that would pull, for example, the first 20 rows, then the second 20 rows, then the 3rd 20 rows etc. This type of query is frequently used for websites that display information one page at a time. The first 20 rows is pretty easy. That’s just a TOP operation. It gets considerably trickier when you want that second or third group of 20.

All those many years ago when I did that technical interview this was the method I used.

DECLARE @Offset INT = 100;
DECLARE @Fetch INT = 20;
SELECT EmailAddress
FROM
(SELECT TOP (@Fetch) EmailAddress
FROM
(SELECT TOP (@Offset + @Fetch) EmailAddress
FROM Sales.vIndividualCustomer
ORDER BY EmailAddress) x
ORDER BY EmailAddress DESC) y
ORDER BY EmailAddress;
GO

This was probably not the most efficient method even then, it was just what I came up with for the interview. Honestly I’d never encountered the problem before and in fact I still haven’t run into it more than a handful of times all these years later.

This methods biggest problem is that it gets significantly slower the farther into the list you get. In SQL 2005 things got significantly better with the introduction of ROW_NUMBER().

DECLARE @Offset INT = 100;
DECLARE @Fetch INT = 20;
WITH MyCTE AS (SELECT EmailAddress, 
ROW_NUMBER() OVER (ORDER BY EmailAddress) 
AS RowNumber
FROM Sales.vIndividualCustomer)
SELECT EmailAddress, RowNumber 
FROM MyCTE
WHERE RowNumber > @Offset
  AND RowNumber <= @Offset + @Fetch;
GO

This method is good. It is somewhat complicated and requires a windowing function but that isn’t really a big deal.

In the on-going quest to make things easier/better the OFFSET and FETCH options for the ORDER BY clause was introduced in SQL 2012.

DECLARE @Offset INT = 100;
DECLARE @Fetch INT = 20;
SELECT EmailAddress, 
ROW_NUMBER() OVER (ORDER BY EmailAddress) AS RowNumber
FROM Sales.vIndividualCustomer
ORDER BY EmailAddress OFFSET @Offset ROWS 
FETCH NEXT @Fetch ROWS ONLY;
GO

The timing on the last two versions is virtual identical as is the execution plan (if you remove the ROW_NUMBER() from the last one it does simplify the execution plan slightly) but the code is much easier and in a large query it could make a bigger difference.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, language sql, microsoft sql server, sql statements, T-SQL