分页

--方法一：sql2000/2005
SELECT TOP pagesize *
FROM table1
WHERE id >
          (
          SELECT ISNULL(MAX(id),0) 
          FROM 
                (
                SELECT TOP pagesize*(pageno-1) id FROM table1 ORDER BY id
                ) A
          )
ORDER BY id

--方法二
DECLARE @pagenum AS INT, @pagesize AS INT
SET @pagenum = 2
SET @pagesize = 3
SELECT *
FROM (SELECT ROW_NUMBER() OVER(partition by cateid ORDER BY newsid DESC) AS rownum, 
        newsid, topic, ntime, hits
      FROM news) AS D
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize
ORDER BY newsid DESC

--方法三(top过滤要稍优于between过滤)
SELECT TOP (@pagesize) *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY customerid DESC) AS rownum,* FROM Customer) AS Dd
WHERE rownum > (@pagenum-1)*@pagesize
ORDER BY dd.customerid DESC

--使用Top的分页存储过程
Create proc [dbo].[test_PageById]
(
@pageIndex int,
@pageSize int
)
AS
SELECT TOP(@pageSize) *
FROM test
WHERE UserId<(
        SELECT MIN(UserId) FROM (
            SELECT TOP ((@pageIndex-1) * @pageSize) UserId 
            FROM test 
            ORDER BY UserId DESC
        )B
    )
ORDER BY UserId DESC

--使用Row_number的存储过程
CREATE proc [dbo].[test_PageByRowNumber]
(
@pageIndex int,
@pageSize int
)
AS
DECLARE @startRow int, @endRow int
SET @startRow = (@pageIndex - 1) * @pageSize +1
SET @endRow = @startRow + @pageSize -1 
SELECT *
FROM (
    SELECT *,ROW_NUMBER() OVER (ORDER BY UserId DESC) AS RowNumber
    FROM test ) T 
WHERE T.RowNumber BETWEEN @startRow AND @endRow