MSSQL: Is paging the right approach?

paging, SQL

[Raggi]

I want to display SQL-search results like I do with Indexserver. Show only 10 records, then offer page 2,3,4,...
MS SQL Server doesnt offer "LIMIT", so I found out that paging could be a helpful approach.
I found some nice articles about paging at 4guysfromrolla but they use ADODB-RS-and connection.

this is my code-snipplet:

DbConnStr = "127.0.0.1;initial catalog=DB;user id=sa; password =sa;"
Set objDbCon = New dbConnector
objDbCon.DbPath = DbConnStr
..
set iFields = objDbCon.GetRSRead(sqlQuery)
If iFields.EOF Then
Response.Write "<td>&nbsp;</td>"
Else
if Not iFields(0) is Nothing then
strHeadline = iFields(0)
else
strHeadline = ""
end if
.....

can I also use Paging? If yes, do I have to create StoreProcs?

[Spooky]

Probably topic 2120 over at aspfaq would give you the most options

[Spooky]

btw - SQL server uses "TOP" instead of "LIMIT"

[duckhunter]

I just had this same issue and came up with a great approach. You can do it 1 of 2 ways depending on your needs.

1) Temp Table
2) Table Variable

If your machine is low on RAM or you are returning a large number of fields, the Temp Table is the approach. I toyed with both and found the Table Variable is great for returning a limited number of records and columns.

You must run this as a Stored Procedure. Basically you create a temporary table in memory with an Identity column. The Identity column lets you specify ID > x and ID < y to get back the "page" worth of records.

Here's the psuedo code:

CREATE PROCEDURE dbo.pGetRecordsByPage
@startitem int,
@stopitem int,
@category int
as
--First Define the Table Structure. Note the lKeyID field 
--which is an Auto Identity giving you a basis for seletion.
-- ie: Rows 1-10 = page 1, Rows 11-20 = page2, etc.
DECLARE @TableVar table (
lKeyID INT IDENTITY PRIMARY KEY,
PRODUCTCODE varchar(15),
SUPPLIERID varchar(5),
PRODUCTDESC varchar(255))
--Second, Insert ALL records that satisfy your where clause
INSERT INTO @TableVar (PRODUCTCODE, SUPPLIERID, PRODUCTDESC)
select pp.prodcode, pp.supplierid, pp.productdesc
FROM 
PRODUCTS PP WITH (NOLOCK)
WHERE
 CATEGORY = @category
order by 
pp.DisplayOrder
--Finally, Select the page worth of records from the Temporary Variable
SELECT
PRODUCTCODE, SUPPLIERID, PRODUCTDESC
FROM
@TableVar
WHERE
lKeyID >= @startitem
AND
lKeyID <= @stopitem

Basically you create a table of ALL of your pages worth of records and only return the number of rows you want. The startitem and stopitem params are passed in from the webpage. Depending on how many records per page you are returning will define the start/stop point for each page.

NOTE: Notice the "Order by DisplayOrder" statement. It is important to specify an Order By clause to ensure the records are returned in the same order from page to page. If they were not consistent, it's possible the same product could show up on multiple pages.

[f00sion]

definitely go the stored procedure route, it will be much much quicker.

[Raggi]

Hi

thanks a lot for all your replies!

aspfaq TOPIC 2120 offers 10 (sic!) different approaches to produce paging.asp-pages based on StoredProcedures.
I am really impressed about the performance