Whine

December 10, 2008 at 5:12 pm (SQL Server 2008, TSQL) ()


I’m working on the chapter on cursors. My editors thought my plan for the chapter was less than optimal. I wanted to have the chapter head

Cursor Cost Analysis

And then this would be the entire chapter:

They cost too much. DO NOT USE THEM!

Ah well. Guess I have to go through it and create all the silly examples.

End of whine.

11 Comments

  1. Gail said,

    Can you have a note about the default options for a cursor been the worst there are, leading to many of the ‘while loop better than cursor’ ideas?

    Are you allowed to add a section on where they are useful – admin tasks that are carried out one object at a time (backup, checkdb, reindex, etc)?

  2. scarydba said,

    Yes to both and I intended to go over those ideas. I’m going to cover the chapter with as much detail & diligence as I can, but I’m also going to lace it with “this can be done better” comments.

    This chapter and two more. 1st draft is almost behind me. WOO HOO!

  3. Andy Warren said,

    Grant, I’d challenge you to cover the places where the cursor does make sense (admin tasks), the differences between while loops and cursors (cursors provide some pretty cool services if you know the options vs rolling your own), and maybe even a few cases where a cursor is believable if not the best case (my own favorite being to calculate the score of a bowling game) because we’re not all TSQL rocket scientists.

  4. scarydba said,

    Right.

    I will make the attempt. Do understand that I only have a week to write the whole chapter. That includes coming up with the working examples as well as the text. I’ll work the admin stuff in, but I won’t guarantee the bowling solution (I failed last time I tried to solve that anyway).

    I still don’t like cursors.

  5. Gail said,

    I don’t like them either for data manipulation, for obvious reasons.

    They’re one of the easier ways to do things like backups of databases based on conditions though, where lots of data is not been manipulated, and the overhead of the cursor will be negligible compared to the time of the operation itself.

  6. Vivek said,

    Yup,

    Cursors are useful when you dont have identity values or some values to reference for the while clauses..

  7. scarydba said,

    Gail,

    Exactly. The one place they are useful is where the behavior of them doesn’t impact what you’re doing. Which is why so frequently it makes sense to use them in maintenance.

  8. Gail said,

    Vivek: “Cursors are useful when you dont have identity values or some values to reference for the while clauses..”

    Um, no. While loops and cursors are both equally bad for data manipulation. They’re both row-by-row processing, which SQL does poorly.

  9. Michael O'Neill said,

    Perhaps cursors in SQL Server mean something completely different than they do with Oracle?

    In Oracle they are memory pointers. The .NET analogy would be that they are reference type to a collection or array’s value type.

    Passing around a reference is an excellent practice when compared to barging about a ginormous value. There’s no reason you can’t effeciently bulk collect them into an array when desired (you don’t have to process “row-by-row” as Gail indicates is necessary in SQL Server.)

    So, is there a fundamental difference between what a cursor is in SQL Server and Oracle?

  10. Jack D Corbett said,

    Wow, only a week to write the whole chapter and examples. I’ve learned to avoid cursors and loops, except in admin tasks and one time things.

    Vivek, you should always have a PK that you can reference, using a cursor shouldn’t be based on that.

    Michael, my understanding is that cursors are different. I’ve never worked with Oracle, but the little reading I’ve done indicates that Oracle handles them differently, maybe better.

  11. scarydba said,

    Sorry I wasn’t responding to these guys. I lost internet connectivity for a while because of the ice storm (but not electricity I’m happy to say.) I worked on the book, played Fallout 3, and split wood.

    Anyway, thanks for the response Jack. Yeah, only a week per chapter. It’s nuts. I know I’m probably missing a few things or short-changing topics… What are you going to do?

    Vivek, Jack and Gail nailed it for me. While I do find cursors useful for walking through processes that are really expensive, all by themselves, such as CHECKDB or backups or things like that, where the cursor is effectively free, I really shy away from using cursors within TSQL code as part of selecting data. Same with WHILE loops.

    Michael, I too am not an Oracle guy, but yeah, from my understanding of cursors in Oracle, we’re talking about a whole different world, not the same in any way.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: