-- 2005 及之后的版本
SELECT * FROM(
   SELECT rowid = ROW_NUMBER(ORDER BY GETDATE()), * FROM 你的表
    WHERE name = 'test')
)A
WHERE rowid BETWEEN 2 AND 10

select top 8 * from (select top 10 * from tb where name='test')b order by id desc

-- 2005 及之后的版本
SELECT * FROM(
   SELECT rowid = ROW_NUMBER()OVER(ORDER BY GETDATE()), * FROM 你的表
    WHERE name = 'test'
)A
WHERE rowid BETWEEN 2 AND 10

CREATE PROCEDURE [GetCustomersDataPage1] 
         @PageIndex INT,                             --页面索引,从datagrid中获取
         @PageSize INT,                              --页面显示数量，从datagrid中获取
         @RecordCount INT OUT,                --返回记录总数
         @PageCount INT OUT,                   --返回分页后页数
         @strGetFields nvarchar(1000),         -- 需要查询的列
         @tableName nvarchar(500) ,           --表名称
         @ID nvarchar(100),                          --主键,（为表的主键）
         @strWhere nvarchar(1000) ='',        -- 查询条件(注意: 不要加where)
         @sortName nvarchar(50) =' asc ' ,     --排序方式
         @orderName nvarchar(100)              --父级查询排序方式
 
AS
declare @countSelect nvarchar(2000) 
--设置统计查询语句
if len(@strWhere) =0 
--如果没有查询条件
    begin
        set @countSelect=N'SELECT @CountRecord = COUNT(*) FROM '+@tableName
    end
else
--否则
    begin
        set @countSelect=N'SELECT @CountRecord = COUNT(*) FROM '+@tableName+' where '+@strWhere
    end
--执行并返回总数
exec sp_executesql @countSelect,N'@CountRecord int output',@RecordCount output
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize) 
 
SET NOCOUNT ON
 
DECLARE @SQLSTR NVARCHAR(3000)
--实际总共的页码小于当前页码或者最大页码
if @PageCount>=0
    --如果分页后页数大于
    begin
        if @PageCount<=@PageIndex and @PageCount>0   --如果实际总共的页数小于datagrid索引的页数
            --or @PageCount=1
            begin
                --设置为最后一页
    set @PageIndex=@PageCount-1
            end
        else if @PageCount<=@PageIndex and @PageCount=0
            begin
                set @PageIndex=0;
            end
    end
 
IF @PageIndex = 0 OR @PageCount <= 1 --如果为第一页
    begin
        if len(@strWhere) =0
            begin
                SET @SQLSTR =N'SELECT TOP '+STR( @PageSize )+@strGetFields+' FROM '+@tableName+' ORDER BY '+@orderName+@sortName
            end
        else
            begin
                SET @SQLSTR =N'SELECT TOP '+STR( @PageSize )+@strGetFields+' FROM '+@tableName+' where '+@strWhere+' ORDER BY '+@orderName+@sortName
            end
    end
ELSE IF     @PageIndex = @PageCount - 1 --如果为最后一页           
    begin
        if len(@strWhere) =0
            begin
                --SET @SQLSTR =N' SELECT '+@strGetFields+' FROM '+@tableName+' where '+@ID+' not in ( SELECT TOP '+STR(/*@RecordCount - */@PageSize * @PageIndex )+@ID+' FROM '+@tableName+' ORDER BY '+@orderName+@sortName+' )   ORDER BY '+@orderName+@sortName
                 SET @SQLSTR =N' SELECT '+@strGetFields+' FROM '+@tableName+' 

                 where '+@ID+' < ( SELECT min('+@ID+') from ( select top '+STR(@PageSize * @PageIndex )+@ID+' FROM '+@tableName+' ORDER BY '+@orderName+@sortName+' ) as i )

                 ORDER BY '+@orderName+@sortName
            end
        else
            begin
                --SET @SQLSTR =N' SELECT '+@strGetFields+' FROM '+@tableName+' where '+@ID+' not in ( SELECT TOP '+STR(/*@RecordCount - */ @PageSize * @PageIndex )+@ID+' FROM '+@tableName+' where '+@strWhere+' ORDER BY '+@orderName+@sortName+' ) and '+@strWhere+' ORDER BY '+@orderName+@sortName
                 
                 SET @SQLSTR =N' SELECT '+@strGetFields+' FROM '+@tableName+' 

                 where '+@strWhere+ ' and '+@ID+' < ( SELECT min('+@ID+') from ( select top '+STR(@PageSize * @PageIndex )+@ID+' FROM '+@tableName+ ' where '+@strWhere+'  ORDER BY '+@orderName+@sortName+' ) as i )

                 ORDER BY '+@orderName+@sortName
           end
    end
ELSE                                                              --否则执行
    begin
         if len(@strWhere) =0
            begin
                --SET @SQLSTR =N' SELECT TOP '+STR( @PageSize )+@strGetFields+' FROM '+@tableName+' where '+@ID+' not in ( SELECT TOP '+STR( /*@RecordCount - */@PageSize * @PageIndex )+@ID+' FROM '+@tableName+' ORDER BY '+@orderName+@sortName+' ) ORDER BY '+@orderName+@sortName
           
                 SET @SQLSTR =N' SELECT Top '+STR( @PageSize )+@strGetFields+' FROM '+@tableName+' 

                 where '+@ID+' < ( SELECT min('+@ID+') from ( select top '+STR(@PageSize * @PageIndex )+@ID+' FROM '+@tableName+' ORDER BY '+@orderName+@sortName+' ) as i )

                 ORDER BY '+@orderName+@sortName
                 
           end
         else
            begin
                --SET @SQLSTR =N' SELECT TOP '+STR( @PageSize )+@strGetFields+' FROM '+@tableName+' where '+@ID+' not in (SELECT TOP '+STR(/*@RecordCount - */ @PageSize * @PageIndex )+@ID+' FROM '+@tableName+' where '+@strWhere+' ORDER BY '+@orderName+@sortName+' )and '+@strWhere+'ORDER BY '+@orderName+@sortName
               
                  SET @SQLSTR =N' SELECT TOP '+STR( @PageSize )+@strGetFields+' FROM '+@tableName+' 

                 where '+@strWhere+ ' and '+@ID+' < ( SELECT min('+@ID+') from ( select top '+STR(@PageSize * @PageIndex )+@ID+' FROM '+@tableName+ ' where '+@strWhere+'  ORDER BY '+@orderName+@sortName+' ) as i )

                 ORDER BY '+@orderName+@sortName  
           end
    end
EXEC (@SQLSTR)
set nocount off
GO
 
 
declare @RC nvarchar(20)
declare @PC nvarchar(20)
exec  GetCustomersDataPage1 15,10,@RC output,@PC output,'id,aa,bb,cc,'tb',id,'Area=''广东'' ', ' desc ' , id 
go