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.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.|||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.|||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?|||cursors place locks on your tables and therefor cause waits for others users that want to modify data.|||Cursors are very fast in Oracle (providing it's not sitting on Windows), but in MSSQL it's the method of "last resort".|||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.|||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?|||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.|||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 :)