Deallocate cursor overhead

8 Sep 2006 8:07 AM

devjnr

Analyzing an environment I noticed that there are lots of udf and sp
that use cursors.

The only action that is done is "close cursor" but none deallocate.

Do you think would be godd or great idea to add deallocate at the end?

Thx.

8 Sep 2006 8:27 AM

David Portas

dev***@gmail.com wrote:

> Analyzing an environment I noticed that there are lots of udf and sp
> that use cursors.
>
> The only action that is done is "close cursor" but none deallocate.
>
> Do you think would be godd or great idea to add deallocate at the end?
>
> Thx.

The best policy is to deallocate explicitly so that resources are
released as early as possible. If you don't do that a cursor will get
deallocated only when it goes out of scope.

Heavy reliance on cursors is often an indicator of poor design or
coding. Cursors are a clumsy and inefficient solution for most database
tasks.

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