Why are cursors "more intuitive"

Question

Why are cursors "more intuitive"

Xedni

SSCertifiable

Points: 6951
More actions
March 4, 2015 at 12:42 pm

#315164

Hi All,
This is NOT a question about when to or not to use loops and cursors, but I have heard from several places that using cursors seems "more intuitive". I have never run across a situation where I couldn't use a loop, nor one where it was harder to understand than a cursor. Is there some history to cursors which lead people to lean towards them over loops?
Executive Junior Cowboy Developer, Esq.[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sql-lover SSCoach Points: 18530 More actions · Answer 1

JeeTee (3/4/2015)
Hi All,
This is NOT a question about when to or not to use loops and cursors, but I have heard from several places that using cursors seems "more intuitive". I have never run across a situation where I couldn't use a loop, nor one where it was harder to understand than a cursor. Is there some history to cursors which lead people to lean towards them over loops?

Most programmers are coming from C#, C++ world, where imperative code is the rule. SQL is declarative, not an imperative language. So when you're coming from that mind set, it looks "easier" to write a cursor in SQL. The problem is that SQL server does not want you to tell "how to get" the data, but what to get, which is the same definition of a declarative language.

Does that help?

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

JeeTee (3/4/2015)
Hi All,
This is NOT a question about when to or not to use loops and cursors, but I have heard from several places that using cursors seems "more intuitive". I have never run across a situation where I couldn't use a loop, nor one where it was harder to understand than a cursor. Is there some history to cursors which lead people to lean towards them over loops?

When they say that cursors are more intuitive, they're usually not talking about when compared to temp tables and WHILE loops. They're talking about when compared to "set based" programming. Now that's a real problem for some because front-end Developers are used to writing a loop (which is also a cursor to me) and don't understand that, behind the scenes, every SELECT is virtually a loop (which I refer to as a "Pseudo-Cursor" with full credit to R.Barry Young for the term). Cursors (and While loops) also support the more common programmatic method of doing everything for a row before moving to the next instead of processing things by whole columns like most set based code does.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

I personally hate cursors because the syntax is bloated, counter-intuitive and just plain ridiculous. I have been writing SQL for 15+ years and still need to reference Books online or a previously written cursor when I wrote them in the past. Loops, on the other hand are very simple; I could write a loop in my sleep. If I am reverse engineering someone's code I find loops easy to understand and get sick to my stomach when I need to reverse engineer a cursor. :sick:

So why would someone go through the added frustration of writing a cursor vs a loop? Performance is a huge reason. If you had a requirement for a running total you will find that a well-optimized cursor (e.g. using the FAST_FORWARD option) will blow the socks off a loop. That's really it as far as I'm concerned.

All that said (and I know that you said "This is NOT a question about when to or not to use loops and cursors") I have have found that, once you have practiced it for awhile, the set-based approach is actually very intuitive; usually more so that even a loop. The code is often more concise and so is the query plan it produces.

That's my 2 cents.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Xedni SSCertifiable Points: 6951 More actions · Answer 4

Thanks, all! This gave me some useful insight into where that sentiment was coming from.

Executive Junior Cowboy Developer, Esq.[/url]