Efficient data paging with SQL Server 2005 using ROW_NUMBER()
Paging large amounts of data in SQL Server 2000 used to be a real pain, creating temporary tables, suffering bad performance after the first few batches of data or using ROWCOUNT to ignore large numbers of rows. Luckily SQL Server 2005 delivers a couple of new functions to make efficient painless data paging a reality. Enter the ROW_NUMBER() function.
All ROW_NUMBER() and it’s partner OVER does is to add a virtual row number to the start of each row returned. That’s it. Nothing more dramatic than that. Take a look at this query:
SELECT CallID, ROW_NUMBER() OVER (ORDER BY ID) AS Row FROM CallRecords
which returns:
CallID Row 101 1 102 2 103 3 104 4 105 5 106 6 107 7 108 8 109 9 110 10 (10 row(s) affected)
As you can see, has created a new ‘virtual’ column named ‘Row’ and filled it with sequential row numbers. Breaking this query down, we can see what each individual part does:
SELECT CallID : Returns the data from the CallID Column from my table
ROW_NUMBER() : Works the magic to return a row number
OVER (ORDER BY ID) : This determines the order of the data that is returned BEFORE the row number is added
AS Row : gives the column generated by ‘ROW_NUMBER() OVER (ORDER BY ID)’ the name ‘Row’
FROM CallRecords : Get the data from my CallRecords tables
Now let’s expand this query to return just the rows between 5 and 9 by wrapping it in a bit of extra SQL:
SELECT CallID, Row FROM ( SELECT CallID, (ROW_NUMBER() OVER (ORDER BY ID)) AS Row FROM calldatarecords ) Rows WHERE Row BETWEEN 5 and 9
And that’s it! Have fun!
My name is Jon Paul Davies and I work for 
August 10th, 2007 at 7:46 pm
This is how the paging API in NHibernate works with the 1.2 production release and SQL Server 2005 provider. It’s so damn cool.
I was going to rewrite some old ADO code to leverage this and migrated to NHibernate in the process, thinking ‘I wonder how their paging works in relation to my planned use of ROW_NUMBER()’).
SQL trace later… And it is completely as good as it gets. Writing data access code is for suckers.
Damon Carr
August 11th, 2007 at 8:09 am
Thanks for the comment Damon. I’ve not used NHibernate at all, is it something worth investigating? I’ve used a few ORM mappers in the past like LLBLGEN etc., and found them a bit lacking. I use Subsonic to prototype ( which is killer! ) and sometimes Codesmith to generate SPROC wrappers if I’m feeling lazy, but not NHibernate.
August 11th, 2007 at 8:26 am
You can also take a look at Castle’s ActiveRecord project, which utilizes NHibernate as its backend engine. Couple that with the ActiveWriter VS tool, it can give you an amazingly rapid development setup.
August 11th, 2007 at 9:59 am
Thanks Knave. In that case I’ll be sure to check it out.
August 11th, 2007 at 3:55 pm
What is the last “Rows” keyword for?
August 11th, 2007 at 5:40 pm
Basically we throw the first query inside some brackets and put whats called a ‘table alias name’ after it. This means the second query thinks it is pulling records from a table called ‘rows’. The query result inside the brackets is called ‘Rows’ as far as the outer query is concerned. It’s just a technique to simplify and break down queries. You can also use the new WITH command in sql 2k5 to do a similar process. Hope this helps!!
August 11th, 2007 at 10:06 pm
jdee,
To be honest, I cannot remember developing without NHibernate (OK I can, but I just choose not to (grin))…
Are there certain apps it might not work for? Like reporting or working with some old funky legacy database where nothing makes any sense? (like 10 column keys, etc.)? Sure…
I just choose to never work on those crappy projects. NHibernate is…. the sh*t. It saves me so much time, improves my quality by 2x, etc. I know there are no silver bullets, but this is as good as I have found in 18 years of development.
ActiveRecord is a ligher intro, but NHibernate is my preference. As for learning curve, it depends of how much you ‘think’ Objects instead of DataSets.
You need to forget DataSets as everything is object based (almost). It’s like turning your RDBMS into an Object database…
Polymorphic queries, no joins required, direct SQL (stored proces if you must), blazing speed in almost all cases, and a masssive community to help if needed. This is the most fun I have had in a while.
Damon
September 12th, 2007 at 12:18 am
Concerning NHibernate, I have the same experience as Damon
Thanks for the post!
November 29th, 2007 at 1:52 pm
Its a great post.
Came to know the easiest way of paging in SQL Server 2005.
Can you please suggest an efficient way of paging in SQL Server 2000
November 29th, 2007 at 3:09 pm
Hi subodh, I honestly don’t think there *is* an efficient way of paging sql 2000. You can google for something, but I’ve never found a solution that I think fits the bill as far as performance goes, sorry. Thanks for the comment!
June 18th, 2008 at 9:59 am
Relieving to see it can be this easy SQL server 2005, thanks for this.
I do also have a comment for Damon, as great as a article it is, if that’s the most fun you’ve had in a while I think you need to take a short break from your computer.