October 25, 2001 at 2:14 am
An ORACLE programmer has told me that there is a ROWID property for a returned recordset, i.e. each row within a recordset is numbered sequentially.
He uses this a great deal in web applications for paging through search results.
Is there an equivalent simple function or property within SQL Server?
October 25, 2001 at 3:01 am
If you're using ADO, then Recordset.AbsolutePosition will be:
a valid recordnumber
EOF
BOF
Unknown (empty recordset).
This could be used with the recordcount.
Paul Ibison PhD, MCSD
email: Paul.Ibison@btinternet.com
Paul Ibison
Paul.Ibison@replicationanswers.com
October 25, 2001 at 5:07 am
ADO has built in paging as well.
Andy
October 25, 2001 at 5:20 am
Thanks Guys
But what I was after was something like
SELECT @@ROWID,*
FROM Tbl_
WHERE @@ROWID BETWEEN 10 AND 20
I've seen your select TOP queries where a sub-query also has a TOP clause in order to produce the same effect.
My solution used
CREATE PROC usp_Page @lPageSize Int=10 , @lPageNo Int=1 AS
DECLARE @lMaxRows Int
SET @lMaxRows= @lPageSize * @lPageNo
SET ROWCOUNT @lMaxRows
SELECT *
INTO #Tbl_Temp
FROM Tbl_
WHERE SomeColum = 'SomeValue'
ORDER BY MyColumn
SET ROWCOUNT @lPageSize
SELECT *
FROM #Tbl_Temp
ORDER BY MyColumn DESC
SET ROWCOUNT 0
GO
The only thing the developer has to do is pass the page number and number of records per page.
On receiving the recordset back they have to sort the returned recordset in reverse order.
October 25, 2001 at 7:59 am
Have you compared IO and CPU usage between your solution and the top using solution? I would expect (but never know) that using Top would be faster since it doesn't require a temp table.
Andy
October 25, 2001 at 8:10 am
I haven't checked either because I'm working in a pure development environment and at this stage is is all pretty hypothetical.
I will do some testing at a later stage to see if the dynamic SQL variation works quicker than the compiled temp table solution.
I would probably pre-create the temp table rather than use SELECT INTO as I have noticed a definite performance benefit in doing so.
I also tend to set the size of TEMPDB so it doesn't have to resize during an operation.
I have found that too small a TEMPDB detracts from performance.
Also, I put TEMPDB on a local fast hard drive rather than on a RAID.
Incidentally, my ORACLE contact says there isn't a TOP clause or JOIN clauses in ORACLE and that is why they started looking for an @@ROWID property.
They also say that CURSORS are recommended in ORACLE.
Has anyone ever done a comparison between SQL Server and ORACLE from functionality perspective?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply