A very common request over the past, oh, 10 years, maybe more, has been better support for paging within T-SQL (this Connect request dates back to 2005).  Well, you will see some broader support for this within a year, give or take: the next version of SQL Server will offer OFFSET / FETCH syntax.  Tobias Ternstrom spilled the beans ...