Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Stairway to Database Design Level 8: Cursors Expand / Collapse
Author
Message
Posted Thursday, November 3, 2011 12:09 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 4:23 PM
Points: 415, Visits: 2,333
Cadavre (11/3/2011)
patrickmcginnis59 (11/3/2011)
So one use of cursors I would consider would be running totals based on a date ordering of a result set. It seems to me that using the set oriented solutions I've would get progressively slower as the result set got larger. Are there set oriented solutions that wouldn't increase in execution time at more than a linear rate in proportion to the number of records in the set getting the running total calculated?

I can't conceptually figure out how to accumulate and list a total for each record without constantly recalculating it against all the previous records preceding it in the ordered result set.


Jeff's quirky update solves the running total issue, and "Denali" offers new options.


That was an interesting read, but it sort of looks like it works by accident, would it work on my table without an index? I'm hoping Denali has some designed solutions.
Post #1200129
Posted Tuesday, November 8, 2011 12:51 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 18, 2014 10:57 AM
Points: 56, Visits: 335
On the one hand I agree that most cursors, especially those used inside 1 database at a time should probably be rewritten. Most reporting oriented cursors can be rewritten to use joins with a dramatic increase in speed.

That being said, I find it hard to program some of my server wide queries and updates not using a cursor. Sp_msforeachdb is great for a lot of database manipulation jobs, but when you want to find a list of all of the tables, primary keys, check constraints, and secondary indexes on a server where the tablename is like '%_2011_%' and store the results in a table in order to generate the list for the next year, a cursor comes in very handy.

Cursors are powerful tools that are still in our toolbox, but probably the most misused, especially the newer programmers, especially if they have experience with other, older programming languages.
Post #1202414
Posted Sunday, March 24, 2013 9:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 20, 2013 6:23 AM
Points: 1, Visits: 19
Is the article's description of the DECLARE CURSOR statement strictly accurate? I'm referring to the sentence that says: "The <query expression> is executed and the results are saved, so they can be sorted." According to MSDN, "DECLARE CURSOR defines the attributes of a Transact-SQL server cursor.... The OPEN statement populates the result set,...". So the query expression isn't actually executed until the OPEN statement is issued.
Post #1434679
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse