q; using cursors

Hello,
Can anyone direct me a good article that is about why we should try avoiding
using cursors and what are the alternatives?You can Google "cursor" and get many hits in this newsgroup. The short
answer is that in the majority of cases, cursors run slower than
(properly-written) set-level SQL. What you should be searching for are good
SQL texts. I like Joe Celko's SQL Puzzles and Answers. A lot of the
examples in there look like you'd need to use a cursor - but you don't. In
"advanced Transact-SQL for SQL Server 2000", we have chapter on cursors in
the latter part of the book. We show examples of set-based code that
obviated the need for cursors.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:F6EF4FBC-39D3-4D91-BCDD-E5B5F7B93EF7@.microsoft.com...
Hello,
Can anyone direct me a good article that is about why we should try avoiding
using cursors and what are the alternatives?|||JIM.H. wrote:
> Hello,
> Can anyone direct me a good article that is about why we should try avoiding
> using cursors and what are the alternatives?
There are plenty of good reasons to use declarative, set-based SQL code
instead of cursors. The reason most usually given is performance. SQL
Server, like other SQL databases, is designed primarily for
set-at-a-time rather than row-at-a-time operations. Cursors are
typically very slow compared to the alternatives, although performance
obviously varies considerably depending on what you are doing.
Paradoxically, it is also true to say that there is a small class of
problems for which cursors are faster than any set-based solution. In a
well-designed database those situations are uncommon in my experience
and you would be well-advised to get a second opinion if you think you
have come across such a case. Performance and locking issues with
cursors tend to mean they are far less scalable than the set-based
alternatives so even if they work for you today they may not be a
viable solution in future.
Besides performance there are other good reasons to use set-based code
rather than cursors: The declarative code is usually much more concise
and therefore easier to develop, inspect, test and maintain; It's more
likely to be portable to other database platforms; SQL professionals
(good ones using T-SQL anyway) tend to write cursors seldom and so are
likely to be more comfortable and more productive writing set-based
code; Set-based code avoids or tends to show-up some of the logical
anomalies and design problems that can lie hidden and unnoticed in
procedural cursor code.
A legitimate place to use a cursor is for something inherently
procedural (typically admin and control tasks such as managing backups,
working with metadata or invoking other external processes) but in
general you should assume the solution to any data-manipulation problem
will be set-based unless expert analysis proves otherwise. That's why I
would class cursors as a feature for advanced users only. If you find
yourself writing cursors regularly then it's time to re-think what you
are doing or maybe go on a course to learn grown-up SQL. Too many
cursors are written by inexperienced programmers who don't know better
techniques.
As for giving alternatives. There isn't a generic answer to that
question. Loops and cursors are just programming constructs not actual
problems to be solved, so there is no general solution or example to
show how to avoid them. The alternative is simply to use standard
set-based SQL DML: SELECT, UPDATE, DELETE and INSERT. In other words,
statements that operate on SETS of rows rather than one row at a time.
The actual details will depend on exactly what you want to achieve.
If you require hlep with a specific problem then please post more
details as described in:
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--