SQL 2005 ROW_NUMBER()

SQL Server 2005 no makes it easy to do paging of results and every n rows queries now that the row number can be obtained without using a temp table.

The new functionality is given by ROW_NUMBER(). Here is an example that returns every other row regardless if rows have been deleted, missing pks in the sequence, etc:

select Employee_number, myrownum from

(

select

Employee_number,

row_number() over (order by sc.FIRST_NAME asc) as myrownum

from

person

) as temp_person

where

myrownum % 2 = 0

order by myrownum asc

NOTE: the LAST line it is very important if you want to see the rows returned in the order of the row numbers.