Вопрос порционной(постраничной) выборки тесно связан с вопросом нумерации строк в запросе (см. FAQ.Нумерация записей в запросе). Другими словами, для того, чтобы выбрать N-ую порцию из результатов запроса, нужно сначала пронумеровать результаты этого запроса.
Отсюда и похожие методы решения
Вариант 1 «Классический».

SELECT TOP 100 * FROM MyTable

WHERE id NOT IN (SELECT TOP 100 id FROM MyTable ORDER BY id) ORDER BY id




Главный недостаток этого метода в в том, что т.к. TOP n записей выбираются уже из конечного результата запроса, то проверка условия WHERE будет выполняться для каждой строки главного запроса. При этом, время выполнения этой проверки будет расти вместе с номером выбираемой порции(страницы). Если, например, таблица содержит 100 записей и необходимо выбирать данные порциями по 10 записей, то
для 2-ой порции нужно будет будет проверять подзапрос из 10 записей
для 3-ей порции нужно будет будет проверять подзапрос из 20 записей
для 4-ой порции нужно будет будет проверять подзапрос из 30 записей и т.д.
Достоинство метода в его универсальности, академичности. Он не требует специфики T-SQL, этот метод можно применить практически на любом SQL-сервере.
Вариант 2 «Эффективный, специфический для T-SQL».
Как и в случае нумерации строк данный метод основан на использовании временной таблицы. Для удобства оформим наш запрос как хранимую процедуру, возвращающую n-ую порцию(страницу), содержащую m записей

CREATE PROCEDURE dbo.get_this_page (@rec_per_page int, @page_num int) AS

SELECT identity(int, 1,1) AS RowNum, MyId AS OrigId INTO #tmp FROM mytable

SELECT b.* FROM #tmp AS a

INNER JOIN mytable AS b on a.OrigId = b.MyId

WHERE a.RowNum BETWEEN (@rec_per_page * @page_num + 1)

AND (@rec_per_page * (@page_num + 1))

DROP TABLE #tmp




Прмечания.
— предложенный вариант процедуры будет блокировать базу tempdb на все время выполнения 1-го запроса. Если время блокировки становиться неприемлимым, то необходимо разбить этот запрос таким образом

CREATE TABLE #temp(RowNum int identity, OrigId int)

INSERT INTO #temp(OrigId) SELECT MyId FROM mytable




— Если, поле MyId было создано признаком «IDENTITY», то это поле в запросе необходимо «завернуть» в функцию «CONVERT», иначе будет сообщение об ошибке.
Glory