This is a tip for web application developers who want to page through results coming back from a MS SQL database. If there are 100 or 500 records coming back and you just want to show 25 at a time to the user then you can achieve that through your application code (CFML, PHP, etc). But what if there are 1000’s of records, it’s deeply inefficient to request that many records from your database if you only want to display 25 every time the user requests the next page, or jumps forwards several pages. If you want the results to be ordered by a user-defined column and not by the Row ID then the solution may not be obvious.
MySQL can use limit(start,count) but MS SQL doesn’t support that. Someone suggested using a memory table, someone else suggested a cursor. A bit of Googling revealed MS SQL 2005 onwards has a new function called row_number(). Here’s how it works in this example:
SELECT * FROM (
SELECT row_number() over (
order by users.surname, users.firstname
) AS rowNumber, users.id, users.firstname, users.surname
WHERE table1.rowNumber BETWEEN 150 AND 175
It will only return records 150 to 175 and importantly it will order it by surname then firstname. And it’s very, very fast.