Question on recordset paging - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Question on recordset paging

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-12-02 : 11:17:02

I'm going to be writing some recordset pagination procedures, and of course I want to use best practices. I've read the articles on 4-Guys and 15 seconds websites. Very handy.

There's one comment in the 15 seconds article that left me wondering. I've snipped the code and the comment below for ease of use.

The comment below says SQL 2000 can enhance the code by using a local table. I'm not sure if that's really an enhancement in any way or not. What are the benefits of a local table as opposed to a temporary table?

Second, any idea how the UDF suggestion would work into the code using SQL 2000?

CREATE PROCEDURE "sprocInformationTechnologyProjects"

@Page int,
@Size int

AS

DECLARE @Start int, @End int

BEGIN TRANSACTION GetDataSet

SET @Start = (((@Page - 1) * @Size) + 1)
IF @@ERROR <> 0
GOTO ErrorHandler

SET @End = (@Start + @Size - 1)
IF @@ERROR <> 0
GOTO ErrorHandler

CREATE TABLE #TemporaryTable
(
Row int IDENTITY(1,1) PRIMARY KEY,
Project varchar(100),
Buyer int,
Bidder int,
AverageBid money
)
IF @@ERROR <> 0
GOTO ErrorHandler

INSERT INTO #TemporaryTable
SELECT ...
// Any kind of select statement is possible with however many joins
// as long as the data selected can fit into the temporary table.
IF @@ERROR <> 0
GOTO ErrorHandler

SELECT Project, Buyer, Bidder, AverageBid
FROM #TemporaryTable
WHERE (Row >= @Start) AND (Row <= @End)
IF @@ERROR <> 0
GOTO ErrorHandler

DROP TABLE #TemporaryTable

COMMIT TRANSACTION GetDataSet
RETURN 0

ErrorHandler:
ROLLBACK TRANSACTION GetDataSet
RETURN @@ERROR

With SQL Server 2000, the stored procedure for paging described above can be enhanced by using a local table and a "user-defined function."

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-02 : 11:34:09

quote:

The comment below says SQL 2000 can enhance the code by using a local table.
I'm not sure if that's really an enhancement in any way or not.
What are the benefits of a local table as opposed to a temporary table?

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q305977&

A alternative to the temp table method would be to create a base table, similair to #TemporaryTable
which includes a column for a unique id. Then perform an insert/select/delete to the base table.
This would avoid the #table creation, insertion, and drop statements.

I think this is best left as a SP. I cannot speak to any performance benefits of a UDF vs SProc

SamC
White Water Yakist

3467 Posts

Posted - 2003-12-02 : 11:54:16

Regarding Table variables (which is what I understood 15 seconds was calling a "local table")

quote:
A1: Table variables have the following advantages over temporary tables:

* As mentioned in the SQL Server Books Online "Tables" article, table variables, such as local variables, have a well defined scope at the end of which they are automatically cleared.
* Table variables result in fewer recompilations of a stored procedure as compared to temporary tables.
* Transactions that involve table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources. Because table variables have limited scope and are not part of the persistent database, transaction rollbacks do not affect them.

I had understood 15 seconds was suggesting there may be a way that using a UDF might benefit the coding of the SP. In retrospect, your suggestion that the UDF idea may have been to write a UDF that returns the recordset.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources