How to use the row_number function in SQL Server 2005 and 2008 for server side data paging in T-SQL

This technique is fairly handy for server side data paging, it essentially executes the statement server side, then grabs a subsection of the rows (that you specify) and only returns those to the client.  This will reduce network traffic pretty considerably, and subsequently speed up your applications response times if network chatter is a problem.

with employeeDataSlice as
(
      select *, row_number() over(order by EmployeeID asc) as displayRowNum
        from [HumanResources].[Employee]
)
select * from employeeDataSlice where displayRowNum between 1 and 5