Sql Server 2005 - Powerful paging using WITH statement

Source : mehfuz blog

Sql server 2005 formerly named "Yukon" , is bundled with loads of features that can rock a database developer's mind.Among many of features, one thing that is truly mind blowing is the mighty WITH statement. Previously , it is a real tedious task to do paging from database. Some of the option were creating temp table, using cursors,etc. But, now is the time to forget those tricky methodologies.

Lets consider a simple table called employee( emp_id, name, salary). Now, suppose that we need to create a gridview with paging. One option is to bring all the data from database and do the paging in client-side and another one is to do selective fetch. In both cases we can use a stored procedure that takes some parameter and returns a resultset.

Let's see the following...

CREATE PROCEDURE GetEmployees
@Status int,
@StartIndex int,
@PageSize int
AS

WITH FilteredList( [emp_id],[name], [salary], [RowNumber])
AS
(
SELECT
[emp_id],
[name],
[salary],
ROW_NUMBER() OVER ( ORDER BY [ID] DESC) AS [RowNumber]

FROM
Employee
)

SELECT
*
FROM
FilteredList
WHERE
RowNumber BETWEEN (@StartIndex + 1) AND (@StartIndex + @PageSize)


Actually , the WITH creates a in-memory data structure based on ROWNUMBER or any other identifier that we might give. Finally, we separate the result based on that identifier. But anyway in the end it makes our life more simple at the end of the day.

Here in Pageflakes, i use it almost everywhere possible, which surely saves my time and nothing else.