Is there a ROW ID function

  • 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?

  • 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

  • ADO has built in paging as well.

    Andy

  • 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.

  • 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

  • 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