March 26, 2019 at 2:27 pm
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
November 19, 2020 at 4:25 am
This was removed by the editor as SPAM
November 19, 2020 at 6:02 am
Nice article! However, it would have made it complete if terminating condition and LOOPing would have also been included.
November 19, 2020 at 2:55 pm
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