Efficient Paging (Limit) Query in SQLServer 2000?

问题:

What would be the most efficient way to do a paging query in SQLServer 2000?

Where a "paging query" would be the equivalent of using the LIMIT statement in MySQL.

EDIT: Could a stored procedure be more efficient than any set based query in that case?

回答1:

Paging of Large Resultsets and the winner is using RowCount. Also there's a generalized version for more complex queries. But give credit to Jasmin Muharemovic :)

DECLARE @Sort /* the type of the sorting column */
SET ROWCOUNT @StartRow
SELECT @Sort = SortColumn FROM Table ORDER BY SortColumn
SET ROWCOUNT @PageSize
SELECT ... FROM Table WHERE SortColumn >= @Sort ORDER BY SortColumn

The article contains the entire source code.

Please read the "Update 2004-05-05" information. !

回答2:

I think a nested SELECT TOP n query is probably the most efficient way to accomplish it.

SELECT TOP ThisPageRecordCount *
FROM Table
WHERE ID NOT IN (SELECT TOP BeforeThisPageRecordCount ID FROM Table ORDER BY OrderingColumn)
ORDER BY OrderingColumn

Replace ThisPageRecordCount with items per page and BeforeThisPageRecordCount with (PageNumber - 1) * items-per-page.

Of course the better way in SQL Server 2005 is to use the ROW_NUMBER() function in a CTE.

回答3:

The efficiency of the query really depends on how the underlying table is structured. If, say you have a primary key called ID which is an IDENTITY, and it's a clustered index, and you can assume that nobody's been doing IDENTITY_INSERTs on it, you can do a query like:

SELECT TOP XXX FROM table WHERE ID > @LastPagesID;

That will get you results as fast as possible. Everything else that's going to be really efficient is some variant on this -- maybe it's not an ID -- maybe what you're using to page is actually a date which you know to be unique, but you get the point... The IN () based queries shown here will probably work, but they won't touch the performance of a partial clustered or covering index scan.

回答4:

I think what you really have here is a compelling reason to upgrade to SQL 2005.

In SQL 2005 this can be done quickly and easily with:

select ROW_NUMBER() over (order by [MyField]) as rowNum, *
from [MyTable]
where rowNum between @firstRow and @lastRow

If you're really stuck with SQL 2000 I'd worry - Microsoft aren't going to fully support it much longer given that it is now two generations back.

There isn't going to be one best way to do this I'm afraid - all solutions are kinda hacks.

@Petar Petrov's answer is probably the most consistent, however:

• If you're dealing with a clustered index on a smaller table for your sort then the ASC-DESC method (dynamically building two sorts each way using TOP) is probably quicker.
• If your data is relatively static and your sort is fixed then you can add your own rowNum field that you update when you change the sort order (sounds horrible but will be quick for large tables).

I think you're looking at a few hours tweaking with query analyser each time. A stored proc won't make much difference either way - the caching of the query plan is not likely to be the bottleneck.

回答5:

This is a generic SQL Server 2000 stored procedure that will perform pagination on any table. The stored procedure accepts the name of the table, the columns to output (defaults to all columns in the table), an optional WHERE condition, an optional sort order, the page number to retrieve and the number of rows per page.

    CREATE PROCEDURE [dbo].[GetPage]
    @pTableName VARCHAR(30),
    @pColumns VARCHAR(200) = '*',
    @pFilter VARCHAR(200) = '',
    @pSort VARCHAR(200) = '',
    @pPage INT = 1,
    @pPageRows INT = 10
    AS

    SET NOCOUNT ON
    DECLARE @vSQL VARCHAR(4000)
    DECLARE @vTempTable VARCHAR(30)
    DECLARE @vRowStart INT
    DECLARE @vTotalRows INT

    SET @vTempTable = '##Tmp' + CAST(DATEPART(YYYY, GETDATE()) AS VARCHAR(4)) +
    CAST(DATEPART(MM, GETDATE()) AS VARCHAR(2)) +
    CAST(DATEPART(DD, GETDATE()) AS VARCHAR(2)) +
    CAST(DATEPART(HH, GETDATE()) AS VARCHAR(2)) +
    CAST(DATEPART(MI, GETDATE()) AS VARCHAR(2)) +
    CAST(DATEPART(SS, GETDATE()) AS VARCHAR(2)) +
    CAST(DATEPART(MS, GETDATE()) AS VARCHAR(3))

    SET @vSQL = 'SELECT ' + @pColumns + ', IDENTITY(INT, 1, 1) AS ROWID INTO ' + @vTempTable + ' FROM ' + @pTableName

    IF @pFilter != '' AND @pFilter IS NOT NULL
    SET @vSQL = @vSQL + ' WHERE ' + @pFilter

    IF @pSort != '' AND @pSort IS NOT NULL
    SET @vSQL = @vSQL + ' ORDER BY ' + @pSort

    EXECUTE (@vSQL)

    -- Get the total number of rows selected
    SET @vTotalRows = @@ROWCOUNT

    -- If page number = 0, set it to the first page
    IF @pPage = 0
    SET @pPage = 1

    -- If page number is beyond the last page, set page to the last page
    IF (@pPage * @pPageRows) > @vTotalRows
    BEGIN
    SET @pPage = @vTotalRows / @pPageRows
    IF (@vTotalRows % @pPageRows) != 0
    SET @pPage = @pPage + 1
    END

    SET @vRowStart = ((@pPage - 1) * @pPageRows) + 1
    SET @vSQL = 'SELECT * FROM ' + @vTempTable + ' WHERE ROWID BETWEEN ' + CAST(@vRowStart AS VARCHAR(10)) +
    ' AND ' + CAST((@vRowStart + @pPageRows - 1) AS VARCHAR(10)) + ' ORDER BY ROWID'
    EXECUTE (@vSQL)

    SET @vSQL = 'DROP TABLE ' + @vTempTable
    EXECUTE (@vSQL)

GO

Here's a few examples on how to use it using the Northwing database:

EXECUTE [dbo].[GetPage] 'Customers', '*', '', '', 1, 10
EXECUTE [dbo].[GetPage] 'Customers', '*', '', 'CustomerID DESC', 1, 10

To confirm, this is not my work but is courtesy of http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=1055

Cheers, John