#title Offset and fetch

그뎌 생겼다. 다음의 예는 처음 10개의 row는 결과에 포함하지 않고, 11~ 15까지 5개 Row를 리턴한다.
{{{
select productid, name    
from adventureworks.production.product
order by name 
offset 10 rows
fetch next 5 rows only
}}}

즉, 다음의 예와 결과가 같다. 
{{{
select productid, name 
from ( 
select row_number() over(order by name) as rowid, productid, name 
from adventureworks.production.product 
) x 
where rowid between 11 and 15 
order by rowid 
}}}

처음 5개 row를 가져와라.
{{{
select productid, name    
from adventureworks.production.product
order by name 
--offset 10 rows
fetch first 5 rows only
}}}

이런식으로 변수사용도 가능하다.
{{{
use AdventureWorks
go

declare @numberofrows int = 10 -- number of rows you want on a page
declare @desiredpagenumber int = 5 -- page number

select *
from(
    select rownum = row_number() over (order by employeeid), *
    from humanresources.employee
) as a
where rownum > (@numberofrows * (@desiredpagenumber - 1)) 
and rownum <= (@numberofrows * (@desiredpagenumber - 1)) + @numberofrows
order by employeeid

select *
from humanresources.employee
order by employeeid
    offset (@numberofrows * (@desiredpagenumber - 1)) rows
fetch next @numberofrows rows only;
}}}

==== 참고자료 ====
 * [http://www.mssqltips.com/tip.asp?tip=2420 SQL Server Denali Server Side Paging]