| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
SJ
Starting Member
2 Posts |
 Posted - 03/12/2002 : 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
India
12 Posts |
Posted - 05/31/2004 : 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
United Kingdom
12543 Posts |
Posted - 05/31/2004 : 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
USA
29 Posts |
Posted - 04/06/2007 : 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
United Kingdom
22191 Posts |
Posted - 04/08/2007 : 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
USA
29 Posts |
Posted - 05/02/2007 : 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
USA
29 Posts |
Posted - 05/02/2007 : 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
United Kingdom
22191 Posts |
Posted - 05/03/2007 : 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 |
|
|
| |
Topic |
|
Article: Paging Recordsets
