Efficient Paging (Limit) Query in Sqlserver 2000

Efficient Paging (Limit) Query in SQLServer 2000?

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. !

What is the best way to paginate results in SQL Server

Getting the total number of results and paginating are two different operations. For the sake of this example, let's assume that the query you're dealing with is

SELECT * FROM Orders WHERE OrderDate >= '1980-01-01' ORDER BY OrderDate

In this case, you would determine the total number of results using:

SELECT COUNT(*) FROM Orders WHERE OrderDate >= '1980-01-01'

...which may seem inefficient, but is actually pretty performant, assuming all indexes etc. are properly set up.

Next, to get actual results back in a paged fashion, the following query would be most efficient:

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 20
ORDER BY RowNum

This will return rows 1-19 of the original query. The cool thing here, especially for web apps, is that you don't have to keep any state, except the row numbers to be returned.

SQL limit query

Now the subquery is only run once

where im2.id is null will skip the first 40 rows

SELECT top 25 im1.* 
FROM images im1 
left join ( select top 40 id from images order by id ) im2
on im1.id = im2.id
where im2.id is null
order by im1.id

Equivalent of LIMIT and OFFSET for SQL Server?

The equivalent of LIMIT is SET ROWCOUNT, but if you want generic pagination it's better to write a query like this:

;WITH Results_CTE AS
(
    SELECT
        Col1, Col2, ...,
        ROW_NUMBER() OVER (ORDER BY SortCol1, SortCol2, ...) AS RowNum
    FROM Table
    WHERE <whatever>
)
SELECT *
FROM Results_CTE
WHERE RowNum >= @Offset
AND RowNum < @Offset + @Limit

The advantage here is the parameterization of the offset and limit in case you decide to change your paging options (or allow the user to do so).

Note: the @Offset parameter should use one-based indexing for this rather than the normal zero-based indexing.

SQL Server paging query

This is how I would do it in SQL Server 2005+:

SELECT ID, Name, Photo, CreatedDate, rowNum, (rowNum / 20) + 1 as pageNum
FROM (
    SELECT a.ID, a.Name, b.Photo, c.Created_Date
       , Row_Number() OVER (ORDER BY c.Created_Date ASC) as rowNum
    FROM a
       JOIN b ON a.ID = b.ID
       JOIN c ON c.photo = b.photo
    WHERE c.Created_Date BETWEEN '2012-01-1' AND '2012-10-30'
) x
WHERE (rowNum / 20) + 1 = 1

Note that I'm using a little integer division trickery to calculate page number.

Since pre-2005 sadly doesn't have row_number(), I'd use an intermediate table with an identity column:

    SELECT a.ID, a.Name, b.Photo, c.Created_Date
       , identity(int,1,1) as rowNum
    INTO t
    FROM a
       JOIN b ON a.ID = b.ID
       JOIN c ON c.photo = b.photo
    WHERE c.Created_Date BETWEEN '2012-01-1' AND '2012-10-30'
    ORDER BY c.Created_Date ASC
    GO

    ALTER TABLE t ADD pageNum AS rowNum / 20
    GO

    SELECT ID, Name, Photo, Created_Date, rowNum
    FROM t
    WHERE pageNum = 1 

What's the best method to do paging in my ASP page

Whats the best method to do paging in my ASP page when displaying a list of items ?

I just want to add one more feature to Mr. Wheat's answer. Why not u are trying to use the Take () and Skip() feature of linq(obviously if u are using dotnet framework 3.5+)

It is indeed helpful while working with large datasets.

Have a look
Using Take and Skip method in LINQ queries

I knew that there is no MySQL LIMIT clause present in SQL server(both 2000 and 2005 should support).how can i retrieve some particular records (Record 20 -30) from DB ?

You can do this in SQLSERVER 2005+ by using ranking function Row_Number() among other alternatives. A sample example is included herewith

First I am creating a dummy table and inserting some 50 records

declare @tbl table(name varchar(50),age int)
;with num_cte as
(   select 1 as rn
    union all
    select rn+1 from num_cte where rn<50
)  
insert @tbl
select  names ,rn + 20 ageval                
from num_cte
cross apply( select  'name' + CAST(rn as varchar(2))  AS names) names
select * from @tbl

Now by using the Row_Number() function I am picking up records between 20 & 30

select name,age from(
select ROW_NUMBER()over (order by age) as rownum,name,age from @tbl) X
where X.rownum between 20 and 30

However, for achieving the same in SQL SERVER 2000 the below query will help

select name,age from(
select t1.name,t1.age,
(select count(*)+1 from @tbl where name<>t1.name and age<=t1.age) rownum
from @tbl t1
)X(name,age,rownum)
where rownum between 20 and 30

LIMIT command SQL Server

Try changing your query into this:

WITH paging AS (
    SELECT
        ,Character.AccountID
        ,Character.Name
        ,Character.CtlCode
        ,AccountCharacter.Number
        ,AccountCharacter.ID
        ,memb___id
        ,memb_name
        ,memb__pwd2
        ,mail_addr
        ,ROW_NUMBER() OVER (ORDER BY Character.AccountID) AS RowNr
    FROM
        Character, 
        AccountCharacter, 
        MEMB_INFO
    WHERE
        Character.AccountID = AccountCharacter.ID
        AND AccountID=memb___id 
        AND AccountCharacter.ID=memb___id 
)
SELECT TOP ({$per_page}) *
FROM paging
WHERE RowNr > {$page} * {$per_page}
ORDER BY RowNr

Note that page 0 is the first page, 1 is the second, etc.

This uses Common Table Expressions introduced in MSSQL 2005, for earlier versions, something like this should probably work (source Efficient Paging (Limit) Query in SQLServer 2000?):

DECLARE @Sort int

SET ROWCOUNT {$page} * {$per_page}
SELECT @Sort = AccountID FROM Character ORDER BY AccountID

SET ROWCOUNT {$per_page}    
SELECT
    ,Character.AccountID
    ,Character.Name
    ,Character.CtlCode
    ,AccountCharacter.Number
    ,AccountCharacter.ID
    ,memb___id
    ,memb_name
    ,memb__pwd2
    ,mail_addr
    ,ROW_NUMBER() OVER (ORDER BY Character.AccountID) AS RowNr
FROM
    Character, 
    AccountCharacter, 
    MEMB_INFO
WHERE
    Character.AccountID > @Sort
    AND Character.AccountID = AccountCharacter.ID
    AND AccountID=memb___id 
    AND AccountCharacter.ID=memb___id
ORDER BY
    Character.AccountID

How to implement LIMIT with SQL Server?

Starting SQL SERVER 2005, you can do this...

USE AdventureWorks;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
) 
SELECT * 
FROM OrderedOrders 
WHERE RowNumber BETWEEN 10 AND 20;

or something like this for 2000 and below versions...

SELECT TOP 10 * FROM (SELECT TOP 20 FROM Table ORDER BY Id) ORDER BY Id DESC

---

Related Topics