Article: Paging Recordsets - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Paging Recordsets

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-02-20 : 11:43:33

If you want to retrieve a page of records, this link will show you how to do it using a T-SQL stored procedure. No ADO paging required!

Article Link.

SJ
Starting Member

2 Posts

Posted - 2002-03-12 : 04:32:23

make this procedure more flexable
old one create a temp. table with all the fields that exists in the pagging table
to make it work with any table fllow these guides:
1) make a view of the table , include the column that you want
2) the temp table colums:
ID:IDENTITY,
TID: a forgien key to the pagging table
modification:
1-Temp Table
CREATE TABLE #TempItems(ID int IDENTITY,Name varchar(50),Price currency)
make it
CREATE TABLE #TempItems(ID int IDENTITY,TID int)
2-Filling the Temp table
INSERT INTO #TempItems (Name, Price)
SELECT Name,Price FROM tblItem ORDER BY Price
make it
INSERT INTO #TempItems (TID)
SELECT Itentity_Column_Name FROM Your_Table_View_Name
3-- Select the result

make it
SELECT *,
MoreRecords = (SELECT COUNT(*) FROM #TempItems TI WHERE TI.ID >= @LastRec)
FROM #TempItems WHERE ID > @FirstRec AND ID < @LastRec
make it
SELECT *,
MoreRecords = (SELECT COUNT(*) FROM #TempItems TI WHERE TI.ID >= @LastRec)
FROM Your_Table_View_Name TBL
WHERE TBL.IDENTITY_Column_Name IN
(Select TID FROM #TempTopicView TI WHERE TI.ID > @FirstRec and TI.ID < @LastRec)
note:
replace Your_Table_View_Name with your view object name and
IDENTITY_Column_Name with the View IDENTITY Column Name

vipinspg
Starting Member

12 Posts

Posted - 2004-05-31 : 06:35:54

If more number of users accessing concurrently the system, how secure is the system if we use temp tables?
I think the better option will be using table variable(whose scope limited to that procedure only).

DECLARE @v_hold TABLE(ID int IDENTITY,Name varchar(50),Price currency)

INSERT INTO @v_hold (Name, Price)
SELECT Name,Price FROM tblItem ORDER BY Price

And finally we can go for the same select statement.

.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-31 : 09:55:51

Not keen on that article - it does give a method but perhps not a very good one.
The temp table creation could cause performance problems and it also orders the full resultset.
Would be better calculating the last recored first and using a set rowcount before the initial insert.

There's a discussion of various methods here
http://www.aspfaq.com/show.asp?id=2120

Note that the temp table method doesn't fare too badly but it's only in a mult-user environment that problems will show up.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jfuex
Starting Member

29 Posts

Posted - 2007-04-06 : 17:46:33

Why use a temp table at all? Wouldn't this be a lot more efficient method?

Assuming you want rows 10-20 of a very large query...

SELECT TOP 20 FROM MyTable
WHERE (somewherecondition) AND
NOT MyTablePK IN (SELECT TOP 9
FROM MyTable (samewherecondition)
ORDER BY MyTablePK)
ORDER BY MyTablePK

Assuming you are building the SQL in a script (might be tricky in a stored proc without using EXEC and dynamic SQL) you can requery the rows you want by adjusting the TOP statements. This performs fairly well even with huge results matching (somewherecondition)

Kristen
Test

22859 Posts

Posted - 2007-04-08 : 02:53:47

Its efficient, but a problem where you might want the Page Size to be a variable (although that's now possible using TOP in SQL 2005 I think)

An alternative along the lines you propose is discussed here:

http://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspx

Kristen

jfuex
Starting Member

29 Posts

Posted - 2007-05-02 : 19:14:11

quote:
Originally posted by Kristen

Its efficient, but a problem where you might want the Page Size to be a variable (although that's now possible using TOP in SQL 2005 I think)

An alternative along the lines you propose is discussed here:

http://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspx

Kristen

True on the variable point, but in most of the cases where I have needed to use this technique, the SQL statement was generated dynamically by code anwyay. I suppose it would be possible to modify this to build a string and use an EXEC statement, though a lot less efficient.

ooo
O

ooo
O

jfuex
Starting Member

29 Posts

Posted - 2007-05-02 : 19:16:32

quote:
Originally posted by Kristen

Its efficient, but a problem where you might want the Page Size to be a variable (although that's now possible using TOP in SQL 2005 I think)

An alternative along the lines you propose is discussed here:

http://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspx

Kristen

..further, the problem with the solution in the link you provided is that it doesn't work properly if you are ordering on non-unique fields.

ooo
O

ooo
O

Kristen
Test

22859 Posts

Posted - 2007-05-03 : 06:33:41

You won't have a repeatable paging solution unless your ORDER BY columns are unique! Just add the PK columns to the ORDER BY as a tie-break - such as the "MyTablePK" you used in your original example

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources