tags:

views:

47

answers:

2
+1  Q: 

when should we use sql cursor

hi all tell me when should i use cursor?

thanks saj

+3  A: 

Ideally as little as possible. Cursors have quite a high inherent processing overhead

Either when

  1. The process you are doing can not be rewritten as a set based operation. (For example invoking DBCC DBREINDEX in turn on a list of tables)
  2. The set based operation has worse asymptotic complexity. For running aggregates the set based solution has quadratic complexity whereas the cursor workload grows linearly.

For this last case using a SQL CLR solution is significantly faster than using a standard cursor.

Martin Smith  2010-08-28 10:38:06
+1  A: 

Please read: http://wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them

You should be able to go your whole career without ever using a cursor unless you are a database admin.

HLGEM  2010-08-31 18:00:00
While on principle I agree, I wouldn't say "whole career". Some times its just easier and faster to use a cursor than to find a way around them. If you can do it with a cursor in fifteen minutes instead of spending half a day for other alternatives, why wouldn't you? Of course providing that the cursor implementation is fast enough, but so far that has never been a problem for me.
Carlos  2010-08-31 18:05:51

related questions

*= in Sybase SQL
sql missing rows when grouped by DAY, MONTH, YEAR
Adapt Replace all strings in all tables to work with text
Can you call a webservice from TSQL code?
How to tell the data types after executing a stored procedure?
C# - SQLClient - Simplest INSERT
SQL Server: Get data for only the past year
Get last item in a table - SQL
Is there a way to make a TSQL variable constant?
Create a database from another database?
SQL Server PIVOT examples?
What are some references, lessons and or best practices for SQL optimization training.
What's a good way to check if two datetimes are on the same calendar day in TSQL?
Trigger without a transaction?
How do I do an Upsert Into Table?
What is the easiest way using T-SQL / MS-SQL to append a string to existing table cells?
How do I avoid using cursors in Sybase (T-SQL)?
Best .NET Solution for Frequently Changed Database
T-Sql date format for seconds since last epoch / formatting for sqlite input
HOWTO - Compare a date string to datetime in SQL Server?
Differences Between MySql and MS SQL
Best method for varchar date validation in Sybase (T-SQL)?
interrogating table lock schemes in T-SQL
Testing for inequality in T-SQL
T-Sql Remove Decimal Point From Money Data Type