Fragmented Thought

Paging at the database in MSSQL

By

Published

Lance Gliser

Heads up! This content is more than six months old. Take some time to verify everything still works as expected.

Lacking limit and offset, modern MSSQL can still perform acceptable paging calls, it's just a bit more syntax to make it work. Here's a basic syntax run down you can start with:

WITH paging AS ( SELECT --return record count so we know how many pages are possible in resultset (redundant, I know) COUNT(*) OVER (PARTITION BY '') AS totalRecords --get row number, this allows us to do paging (The ORDER BY in this statement replaces the SELECT's ORDER BY) ,ROW_NUMBER() OVER ( ORDER BY A, B ) AS ROW_NUM ,TABLE.* FROM dbo.Table_Name TABLE ) SELECT * FROM paging WHERE ( @PageSize IS NULL OR ROW_NUM BETWEEN (@PageIndex - 1) * @PageSize + 1 AND @PageIndex * @PageSize ) ORDER BY ROW_NUM