Equivalent of LIMIT and OFFSET for SQL Server?

Paging in SQL Server 2012 and beyond

In the world of SQL Server 2012, pagination got an upgrade with the arrival of OFFSET and FETCH NEXT commands. These simplified the pagination process, thereby saving developers from the nuances of older, more convoluted methods:

-- Paging party!
DECLARE @PageSize INT = 10, @PageNumber INT = 2;

SELECT *
FROM YourTable
ORDER BY YourOrderColumn
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;


Remember, the placeholders YourTable and YourOrderColumn should be replaced by the real table and column names.

Backward compatibility: SQL Server 2005 to 2008

When dealing with SQL Server 2005 to 2008 R2, OFFSET and FETCH NEXT don't exist. We resort to utilizing the ROW_NUMBER() method for pagination logic:

-- Backward compatibility isn't so bad!
WITH NumberedTable AS (
    SELECT
        ROW_NUMBER() OVER(ORDER BY OrderColumn) AS RowNum,
        *
    FROM YourTable
)
SELECT *
FROM NumberedTable
WHERE RowNum BETWEEN @Offset AND (@Offset + @Limit - 1);


Using a Common Table Expression (CTE), assign each row a unique number based on the desired ordering and then fetch the specified range.

Dealing with large datasets: Performance considerations

Handling large datasets implies performance matters. As the ROW_NUMBER() method can be slower on large tables, optimize by:

• Applying additional WHERE filters to restrict result set before assigning row numbers.
• Deploy appropriate indexes to enhance performance on ordering and filtering columns.

Deprecated methods and impact on performance

If you are dealing with SQL Server versions prior to 2012, SET ROWCOUNT was the way to go for limiting pagination. However, flagged for deprecation, this method steers clear of modern and sustainable solutions:

-- Deprecated method!
SET ROWCOUNT @Limit;
SELECT *
FROM YourTable
ORDER BY YourOrderColumn;
SET ROWCOUNT 0;


Instead, upgrade your skill set by mastering the use of OFFSET and FETCH NEXT or opt for the ROW_NUMBER() alternative to maintain forward compatibility.

Crafting dynamic pagination

In time-critical applications, giving users the power to control both the page size and the page number in pagination is crucial. This can be done using variables in the query to modify the offset and limit conveniently:

-- SQL Server 2012+
-- Hang on! This is gonna be dynamic!
DECLARE @PageSize INT = 10, @PageNumber INT = 1;

SELECT *
FROM YourTable
ORDER BY YourOrderColumn
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;


In older versions, apply the same concept to your ROW_NUMBER() query:

-- SQL Server 2005 to 2008 R2
-- Who said old versions can't be dynamic?
DECLARE @PageSize INT = 10, @PageNumber INT = 1;

WITH NumberedTable AS (
    SELECT
        ROW_NUMBER() OVER(ORDER BY OrderColumn) AS RowNum,
        *
    FROM YourTable
)
SELECT *
FROM NumberedTable
WHERE RowNum BETWEEN (@PageNumber - 1) * @PageSize + 1 AND @PageNumber * @PageSize;


Pagination and sorting: A better user experience

Proper pagination is not just slicing data; it's also about meaningful sorting. Enhance the utility of your pagination by:

• Implementing different ORDER BY clauses based on user preferences.
• Using CASE statements or dynamic SQL queries to allow sorting by multiple columns.
• Crafting indexes to support sorting operations and sustain performance.

References