Also from an article I wrote in CoDe over a year ago on new features in T-SQL 2012:
http://code-magazine.com/articleprint.aspx?quickid=1203051&printmode=true

When Microsoft introduced the new ROW_NUMBER function in SQL Server 2005, developers used it (in conjunction with the new common table expression capability) to generate an internal row number for paging result sets. While this worked quite well, it meant using either a subquery or common table expression, since a ROW_NUMBER (windowing) function cannot be used in the WHERE clause.

SQL Server 2012 now contains a new set of statements: offset and fetch next.

OFFSET (@PageNumber-1) * @RowsPerPage ROWS
   FETCH NEXT @RowsPerPage ROWS ONLY

So let's say I want to skip beyond the first 40 rows (based on Vendor name) and fetch the next 10...

 use AdventureWorks2012
 go
 declare @rownum int = 40

 SELECT Vendor.Name, row_number() over (order by Name) as RowNum
FROM Purchasing.Vendor
ORDER BY Name
OFFSET (@RowNum) ROWS  -- at row #41, grab the next 40 + 10 rows
FETCH NEXT (@RowNum + 10) ROWS ONLY;

For those who want to pass in a "page number" (say, jump to page 5 on a web page that shows X number of rows at a time)...

declare @PageNumber int =5
declare @RowsPerPage int = 10

select BusinessEntityID, AccountNumber, Name, 
       ROW_NUMBER() OVER (ORDER BY Name) as RowNum
       from Purchasing.Vendor Order by Name
       offset (@PageNumber-1) * @RowsPerPage rows
   fetch next @RowsPerPage rows only

It won't generate an execution plan that's any better than techniques prior to SQL 2012, but (IMO) makes the approach more straightforward.