SQL 2005 introduced a lot of new functionality that greatly changed the way we write code. One of these code enhancements was the new function called Row_Number(), http://msdn.microsoft.com/en-us/library/ms186734.aspx. The Row_Number() function gives developers a mechanism to sequentially order data based on several factors. This post will demonstrate how to incorporate custom pagination using the Row_Number() function. For a more in-depth guide please look at the detailed article I wrote on SQL Server Central, http://www.sqlservercentral.com/articles/SQL+Server+2005/65256/.
Let’s have a look at how the Row_Number() function works.
DECLARE @Row_Number_Test TABLE( FNAME VARCHAR(25), LNAME VARCHAR(25) ) INSERT INTO @Row_Number_Test ([FNAME],[LNAME]) VALUES ('Adam','Haines'); INSERT INTO @Row_Number_Test ([FNAME],[LNAME]) VALUES ('John','Doe'); INSERT INTO @Row_Number_Test ([FNAME],[LNAME]) VALUES ('Mr.','Smith'); SELECT ROW_NUMBER() OVER(ORDER BY LName,FName) AS RowNbr, FNAME, LNAME FROM @Row_Number_Test
Results:
As you can see, the results render a sequential row number for each row, based on the value we specified in our order by clause. Now that we have our hands on the row number, we can effectively filter the data for a specific set of pagination rows.
Let’s filter the data by using a specific page and page size. I chose to use the first page, where each page contains 10 rows.
DECLARE @pgSize INT, @pgNbr INT SET @pgNbr = 1 SET @pgSize = 10 ;WITH cte AS ( SELECT ROW_NUMBER() OVER(ORDER BY LName,FName) AS RowNbr, FNAME, LNAME FROM @Row_Number_Test ) SELECT RowNbr, FNAME, LNAME FROM [cte] WHERE RowNbr >= (@pgSize * @pgNbr) - (@pgSize -1) AND RowNbr <= @pgSize * @pgNbr
Results:
The results show that the eleventh row was excluded from the result set. You can play with the page size and page number to see how it affects the results.
There you have it, custom pagination using the Row_Number() function. More a more detailed evaluation of this method, take a look at the article on SQL Server Central, http://www.sqlservercentral.com/articles/SQL+Server+2005/65256/
2 comments:
I have heard about another way of acrobat document recovery. Besides, you can visit my blogs at: http://daspeac.livejournal.com/ or http://daspeac.blogspot.com/ where I’m trying to share my experience with regard to data corruption issues.
Greaat share
Post a Comment