Jon Paul Davies

Microsoft SQL Server Data Services

The big news from Mix08 seems to be the public beta of Microsoft SQL Server Data Services, cloud based super scalable data storage. This has big implications for the way that web applications can be designed and deployed, not least the reduced cost of not having to splash out for infrastructure and expensive licenses. Mixed with seamless access via SOAP and REST it makes a compelling option for persisting in the cloud. Linkage here, here and here.

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!