Stairway to Database Design Level 8: Cursors

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Joe Celko » Stairway to Database Design Level 8: Cursors

23 posts, Page 3 of 3

«««1 2 3

Stairway to Database Design Level 8: Cursors

Rate Topic

Display Mode

Topic Options

Author

Message

patrickmcginnis59

Posted Thursday, November 03, 2011 12:09 PM

SSC-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

john.campbell-1020429

Posted Tuesday, November 08, 2011 12:51 PM

Valued Member

Group: General Forum Members
Last Login: Yesterday @ 11:38 AM
Points: 56, Visits: 319

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

alistairgrieve

Posted Sunday, March 24, 2013 9:46 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, March 24, 2013 12:39 PM
Points: 1, Visits: 15

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 »

23 posts, Page 3 of 3

«««1 2 3

Permissions