Thanks to Ben Reichelt over at CodeBetter.com blogs for pointing out the new Row_Number() function in SQL Server 2005. We finally have an easy way to page data in SQL Server.
In Sql Server 2005, there is a Row_Number() function. You can specify this Row_Number function in your column list of your select statements, like this:
SELECT *, Row_Number() over (ORDER BY FirstName) AS RowNumber
FROM Customer
ORDER BY FirstName
This will add a column called ‘RowNumber’ to the result set, and all RowNumber will be is a sequential number that numbers the rows, and it will number them using the FirstName column. So even if your query doesn’t order by FirstName, you can still number the rows by FirstName, make sense?
So now, what you can do, is add to your query to get a subset like this:
SELECT *, Row_Number() over (ORDER BY FirstName) AS RowNumber
FROM Customer
WHERE RowNumber > 1000 AND RowNumber < 1051
ORDER BY FirstName
This query is going to retrieve the rows numbered between 1000 – 1051, allowing us to page through the data just like we could in MySql. This is awesome, I’m so glad they put this into Sql Server, its something that I’ve been wanting ever since I’ve used MySql.
Sounds like an easy-cheesy way to get paging into SQL Server, finally.