Simple query for ms sql server 2000

Question

Post reply

Simple query for ms sql server 2000

naeem aslam

Newbie

Points: 9
More actions
September 13, 2006 at 12:21 am

#91253

I wants to do paging in ms sql server 2000.As ms sql server 2000 does not support limit clause.How I can get specific records from start index to end index.I want sql query to do so.I shall be thank ful to you.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mikael Åkerblom Grasshopper Points: 19 More actions · Answer 1

Hi!

There are lots of ways to do that. Look in this article for samples and comparison.

http://www.sqlservercentral.com/columnists/jwiner/moreonreturningasubsetofarecordset_1.asp

Good Luck

Thysie Old Hand Points: 341 More actions · Answer 2

Hi Naeem,

As Mikael said, there are many ways. I'm not sure exactly what you want, but you can just use the BETWEEN statement, for example using Northwind:

--These 2 variables are in theory the start & end indexes you spoke about

DECLARE @iStart INT, @iEnd INT

SELECT @iStart = 20, @iEnd = 40

select * from products where productID BETWEEN @iStart and @iEnd

Hope it helps,

Cheers

Jeff Moden SSC Guru Points: 1003888 More actions · Answer 3

Despite the fact that it recompiles every time it is used, I've found nothing faster than the following especially since page size and page number can be parameterized in a sproc... in this example, we have a table called "Customer" and it's non-clustered primary key is "CustID". It has over 1.2 million records in it. If page size is set to 100, it will return page 0 in 1 to 32 milliseconds... it will return the 12,000 page in less than a second... AND, the CustID's can have gaps with no effect on performance or function...

declare @start datetime

set @start = getdate()

--===== Declare the local variables

DECLARE @PageSize INT --How many rows to appear per page

DECLARE @PageNum INT --What page number to appear

DECLARE @Skip INT --Working variable for where to start for page

DECLARE @SQL VARCHAR(8000) --Holds dynamic SQL

--===== Set the local variables for pagesize and page

-- PageSize and PageNum would be parameters in a stored proc

SET @PageSize = 100

SET @PageNum = 4000

SET @Skip = @PageSize*@PageNum

--===== Create the dynamic SQL statement that will produce the page

SET @SQL =

'

SELECT c.*

FROM dbo.Customer c,

(

SELECT TOP '+STR(@PageSize)+ ' *

FROM dbo.Customer WITH (NOLOCK)

WHERE CustID NOT IN (SELECT TOP '+STR(@Skip)+' CustID

FROM dbo.Customer

ORDER BY CustID)

ORDER BY CustID

) d

WHERE c.CustID = d.CustID

'

--===== Produce the page

EXEC (@SQL)

print CONVERT(varchar(30),getdate()-@start,114)

The print statements and the @start variable are just to measure the duration of the code and should be removed from production code. This script may easily be turned into a stored procedure and it should be to help protect the dynamic SQL from injection attacks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003888 More actions · Answer 4

Just curious... did my post help at all?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)