Easy way to page data in Sql Server
Ah, finally, Sql Server has an easy way to page through the result set without getting the entire set. In MySql there is a LIMIT keyword that you can append to your select statements to select only a subset of the query, for instance:
SELECT * FROM Customer LIMIT 0, 50
This query will return 50 rows, starting at row 0.
SELECT * FROM Customer LIMIT 1000, 50
This query will return 50 rows, starting at row 1000.
Getting data like this is nice because with large result sets, you can select just the rows you want to display. Sql Server didn’t have this functionality until now with Sql Server 2005.
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.
johnpapa said,
Wrote on January 26, 2006 @ 2:34 pm
Yes, this is a very cool feature that has been missing! Nice post.
Anonymous said,
Wrote on January 26, 2006 @ 3:10 pm
Am I doing something wrong, but I can not get this to work….I get:
Msg 207, Level 16, State 1, Line 3
Invalid column name ‘RowNumber’.
Msg 207, Level 16, State 1, Line 3
Invalid column name ‘RowNumber’.
Thanks
Anonymous said,
Wrote on January 26, 2006 @ 3:13 pm
Thanks to Ben Reichelt over at CodeBetter.com blogs for pointing out the new Row_Number() function in…
Anonymous said,
Wrote on January 26, 2006 @ 3:20 pm
You don’t have to specify tht ORDER BY FirstName at the end of the query. It’s important that if you write paging code like this, you formulate your complete ORDER BY clause in the OVER clause for ROW_NUMBER().
Why is this important? Because the ORDER BY in the select will be executed AFTER the row_number() function has been applied. This means that the ROW_NUMBER() column doesn’t have to be 1, 2, 3, 4, 5 etc.
You should therefore use these rules of thumb:
- place the SELECT’s ORDER BY clause in the ROW_NUMBER’s OVER clause
- instead add an ORDER BY clause to the SELECT to sort the ROW_NUMBER column ASC. This is necessary because by default a SELECT isn’t guaranteed to be sequential, it can be that the rows aren’t in the order in which you would expect. This is by definition of SELECT.
So then, use a WITH statement to construct a CTE:
WITH __actualSet
(
SELECT C.*, ROW_NUMBER() OVER (ORDER BY CompanyName ASC) AS RowNumber FROM Customers C
ORDER BY RowNumber ASC
) SELECT * FROM __actualSet WHERE RowNumber BETWEEN 1 AND 10
breichelt said,
Wrote on January 30, 2006 @ 3:06 pm
Damon, I’m not sure what’s causing the problem that you’re having, I would make sure that you are including the ‘AS RowNumber’ in the select statement.
breichelt said,
Wrote on January 30, 2006 @ 3:06 pm
Frans, you clearly know more about the feature than I do, thanks for the insight
Anonymous said,
Wrote on February 7, 2006 @ 12:19 am
Nice feature, but it still seems more complicated than it needs to be. LIMIT works beautifully as is. I guess there is some other crazy use for the resulting Row Number column that I’m not considering?…
breichelt said,
Wrote on February 7, 2006 @ 9:03 am
carter, I agree that LIMIT is very simple and easy to use, but I think that Row_Number could be used for some interesting scenarios, because you can set th Row_Number based on a field, and then order the actual result set by a different column. I can’t think of anything offhand that you would use that for, but the fact that its there leaves open the possibility.
Roopa said,
Wrote on February 28, 2007 @ 4:37 am
me too, got the Error
Msg 207, Level 16, State 1, Line 5
Invalid column name ‘RowNumber’.
Here is my query
select Enty_inid ‘StoreID’, Enty_vcName ‘StoreName’ , ROW_NUMBER() OVER (order by Enty_inSalesRank desc ) as ‘RowNumber’ from tb_entity and RowNumber between 1 and 10