ROW_NUMBER in SQL Server 2005
This is yet another cool feature in SQL Server Yukon
.It has been in the
SQL Server 2000 wish list for quite sometime. But I am excited to see this
T-SQL enhancement make it into the SQL Server 2005 version for sure. What is
this requirement? It is to generate a pseudo column as the query optimizer sends
the output. This generated column can be used for various requirements and used
in the UI layer for some identifier purposes. Lets take a sneak preview to this
feature and look at some interesting implementation too:
Syntax: ROW_NUMBER() OVER (
Order_by_clause )
This is the typical syntax we might use. The Orde by clause
dictates the order by clause for which the pseudo column would be generated
using. Using this syntax, a typical query would look
like:
select row_number() over (order by name) as
ROWNumber, Name
from
sys.all_objects order by
name
But this would be a simple implementation. Lets take this to the
next level and use this to generate a stored procedure that will use this
row_number feature and develop the paging feature. For this example, I will use
the CTE Feature that will help us use this row_number functionality
for filtering based on the block size.
Use
NorthWind
GO
CREATE Proc
Employee_Loop
(@seq Int =
1 ,@BlkSize Int = 2)
As
BEGIN
WITH
EmployeesCTE
AS
(SELECT row_number() OVER (ORDER BY Lastname) AS ROWNumber ,
*
FROM Employees)
SELECT * FROM EmployeesCTE
WHERE ROWNumber BETWEEN ((@blkSize*@seq) - (@Blksize - 1))
AND
(@blkSize*@seq)
END
GO
As you can see the CTE helps us generate the rownumber and the
outside where clause helps us eliminate the rows or help us enable the paging
aspect of the query. So under typical use, the scenarios would
be:
-- Using the
defaults: Returns rows 1 and 2
Exec
Employee_Loop
-- Using the
CTE to get the second set:
Returns rows 3 and 4
Exec
Employee_Loop 2
-- Using the
block and second set: Returns rows 4 to 6
Exec
Employee_Loop 2,3
The above code is an simple representation to how we can use
row_number in a practical requirement of paging in SQL Server 2005. The amount
of features we can capitalize in SQL Server 2005 are enormous and we will take a
look into some more in the future articles.
Do pass your comments to us
anytime.
|