Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    370

    Unhappy Unanswered: How could I return the next row(Or 8th row for example)

    Hi,
    I want to return the next row in a select ... order by ... cursor.
    I don't want to loop,just SQL,Do you know any solution?
    For example: in Oracle we use rownum,is there any equivalent in SQL Server?
    -Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Why don't you explain why you think you need a cursor? In Oracle cursors are required, but with SQL Server they are considered inefficent and are rarely used.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2004
    Posts
    370
    Thanks blindman ,
    Typical scenario may be looks like this: I return the result of a heavy select(think about many joins and ...) to the client application: I want just one row at this stage. The application processes that row and then it needs the next row in that select to process.How should I say: "The next row in that select"?
    One solution: Use a table (maybe temporary table) to hold the result of that select with an identity column as the rownum of each row.
    Another solution: Hold the result of select in application layer and go through a loop in application to fetch the next row.
    I am just curious about how one could do this in an "on the fly" manner? You are right maybe it is not efficient in SQL Server but is there any way at all?
    -Regards

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, I think you should hold the data in the application layer if you want to ensure a static dataset for processing. Problem is, if you go back to SQL Server for your "next" record, the underlying data may have changed. Is your application modifying the data and sending it back to SQL server to be updated?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Mar 2004
    Posts
    370
    Is your application modifying the data and sending it back to SQL server to be updated?
    No,it is not.So there is no phantom read problem.I think this is so inefficient to pull all data to client-side only because you may want the next row.Are you in agreement? SQL Server should think about this carefuly,IMHO!
    -Thanks

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well you may think that, but it doesn't, not in SQL 2K.

    2k5 will have rownumber if I'm not mistaken....

    But this is the best server side paging articles I've seen

    http://weblogs.sqlteam.com/jeffs/arc...3/22/1085.aspx

    And

    http://weblogs.sqlteam.com/jeffs/arc...12/22/672.aspx
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Ach
    No,it is not.So there is no phantom read problem.I think this is so inefficient to pull all data to client-side only because you may want the next row.Are you in agreement? SQL Server should think about this carefuly,IMHO!
    -Thanks
    No, I'm not in agreement. How the data is displayed is up to the presentation layer and/or middle tier, and is not the database's responsibility. If the application demands paging, then the database needs to be DESIGNED to facilitate paging. Even ROWNUMBER is meaningless for repeated calls on dynamic datasets.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Mar 2004
    Posts
    370
    How the data is displayed is up to the presentation layer and/or middle tier, and is not the database's responsibility.
    OK,I am in agreement.Maybe this is about "Presentation of Data" but we know there is not a clear line between those layers: Many things can be done in Database layer but is implemented in "Application server or middle layer" and vice versa.
    Thanks to your posts but I still think it is inefficient to pull whole data between layers and I became so happy when I heard that Yukon will have rownum from Brett.Thanks!

  9. #9
    Join Date
    Feb 2005
    Location
    Utah
    Posts
    73
    I'd say use the system the way it was designed. Most of your time is not going to be pulling back an entire dataset, but cycling through it row by row. Databases are designed to work on results sets (i.e., not row by row) and thus aren't very efficient when you want to do that. However, procedural languages are designed to do that very thing. By way of example, I had a cursor running on the database when I first began posting here that took approximately 15 minutes to run. Through the help of those more knowledgable here, I was able to remove the cursor and get the same results in under 30 seconds. I guess the best way to test it is do it both ways, but I'm willing to bet you'll find that pulling the entire result set back and cycling in the app will be faster than coding a cursor in the database. Just my thoughts.
    Dandy
    Aspiring Database Dwarf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •