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.
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)?
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!
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.
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.
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.
Yup,
Cursors are useful when you dont have identity values or some values to reference for the while clauses..
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.
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.
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?
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.
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.