Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2003
    Posts
    3

    Unanswered: Interview Question

    Hello all,
    i'm aware of oracle, but i had a quesrtion in SQL Server today in an interview. Which i didn't understand well. But the question is "We have a cursor in SQL Server and what should we do to drop the cursor and keep the logic". Actually i didn't understand the question well, but those are the exact words. Please let me know if any of you have any idea about this.

    Actualluy when i was talking to some one he said that we need to use "temp tables" for that. I have no idea about that. I would really appreciate if someone can clear this for me.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    A short answer would be to change processing logic from row-based to set-based. However, if the requirement of logic was intended to be interpreted literally, then a cursor can be replaced with a WHILE loop where the key that uniquely identifies each row from the table (or tables) that you're processing can be represented in one field.

  3. #3
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    You would need to look at the requirement for the process and rewrite it as a set based solution.

    It may or may not benefit from a temp table or a loop.

    Would also review the rest of the processing, design, architecture as the presence of cursors usually indicates that someone who is not very experienced at develoiping on relationtional databases has been involved.

  4. #4
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    I'm still new to al this, but I was wondering:

    Won't using cursors be faster and saves database storage space sometimes during a calculation then having to go through creating, inserting ,selecting from a temp table and later droping it?
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  5. #5
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    cursors place locks on your tables and therefor cause waits for others users that want to modify data.
    Johan

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Cursors are very fast in Oracle (providing it's not sitting on Windows), but in MSSQL it's the method of "last resort".

  7. #7
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Originally posted by Patrick Chua
    I'm still new to al this, but I was wondering:

    Won't using cursors be faster and saves database storage space sometimes during a calculation then having to go through creating, inserting ,selecting from a temp table and later droping it?
    SQL Server is efficient at actions that work on sets of data which the sort of processing that sql and relational databases are built for.
    It is innefficient at procedural processing which is the sort of thing you would find in a client.

    Sometimes a cursor can be faster than the equivalent set based sql (especially with correlated subqueries) but I still wouldn't use one.

    With Yukon and the proposed common language runtime then maybe procedural code can be embedded in t-sql and things may change.

  8. #8
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    Thanks for the insights.

    Will try not to use cursors, but it always the easiest way out.

    Yukon!, almost didn't really get what u mean, but I remember reading it in SQL Mag as a code name for the next SQL Server edition ....just letting others who are as blur as I am know.
    er..right?
    Last edited by Patrick Chua; 08-27-03 at 14:12.
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  9. #9
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Yep.

    >> Will try not to use cursors, but it always the easiest way out.
    It's the easiest only if you think of processing row by row.
    I always ban cursors from code to force people to find a set based solution.

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    can't wait till yukon comes out.

    As for cursors, - their usage starts at design time. The old school designers are the ones to blame for what developers and DBA's have to live with

Posting Permissions

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