Stairway to Database Design Level 8: Cursors

  • Comments posted to this topic are about the item Stairway to Database Design Level 8: Cursors

    Books in Celko Series for Morgan-Kaufmann Publishing
    Analytics and OLAP in SQL
    Data and Databases: Concepts in Practice
    Data, Measurements and Standards in SQL
    SQL for Smarties
    SQL Programming Style
    SQL Puzzles and Answers
    Thinking in Sets
    Trees and Hierarchies in SQL

  • This was removed by the editor as SPAM

  • Nice article! However, it would have made it complete if terminating condition and LOOPing would have also been included.

  • Nice article on cursors.  I agree that cursors should be avoided when a simple set based operation can accomplish the task.  By that I mean use INSERT INTO/UPDATE...SELECT ... FROM.  But occasionally  you will  want to process data and perform multiple operations on the returned data and cursors should be used instead of retrieving the same data multiple times.

    I have seen some code written by developers who believe the rule that you should only write 5 cursors in your entire career.  So they imitate a cursor using a TSQL loop.  They create a temporary table based on the query they would use in the cursor and add a bit column named Processed which is set to 0.  They then set up a loop and as long as a SELECT TOP 1 with WHERE Processed =0 returns rows, they process the row including setting the Processed flag to 1.  I have never benchmarked a native cursor against this psuedo cursor but I would think that the psuedo cursor would be slower.  Often, this psuedo cursor can be accomplished with INSERT INTO/UPDATE ... SELECT ... FROM Table1 JOIN Table2.

    My advice is to always look at your procedural code and make sure there is no way to do the same thing with set based code.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply