Pagination / Offset (MSSQL)

Help for the MS SQL traveler

I see this question come up quite a bit on the Bubble forum regarding how to use pagination. The simple solution below will hopefully make your life easier.

SELECT TOP 200 * 
FROM 
    (SELECT *, ROW_NUMBER() OVER (ORDER BY Name DESC) AS RowNum 
    FROM dbo.TableName WHERE Country LIKE @Country) AS newTableName 
WHERE newTableName.RowNum BETWEEN @start AND @end

The @start and @end are parameters that you would specify in your query. In this specific query @Country is also a parameter. However this query can be customized by changing the inner portion of the query. I'll provide one more example so you can see how to replace the inner content with your own query.

SELECT TOP 200 * 
FROM 
    (SELECT *, ROW_NUMBER() OVER (ORDER BY Email DESC) AS RowNum 
    FROM dbo.Customer WHERE Name LIKE @Name) AS newTableName 
WHERE newTableName.RowNum BETWEEN @start AND @end

One important detail to note. The ORDER BY specifies the order of the query. So in this second query I'll get the records order by email.