>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
>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.
I'm a complete novice as far as SQL is concerned (have been for years :-{ ) but just to say I found this info (and your other post on sequential IDs) useful.
Thx. Keep em comin'