What is the problem of cursor

Tridip Bhattacharjee

8y2.6k

i heard often people said do not use cursor. so i like to know what is the down side of cursor usage in sql server store procedure and trigger.

share some points for which we should avoid cursor used in sql server store procedure and trigger. thanks

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Shahriar Nikkhah8y ago
1- No: if working with data
In general if you are working with your data, thing like INSERT UPDATE and DELETE, never use a Cursor.

2- Yes: if you are creating you own tools or framwrork tool.
If you are NOT working with the data but creating SQL strings or creating a dynamic SP that needs to go through the metadata , yes use a cursor.
Bhavesh Jadav8y ago
Hello Tridip,

As per my experiance and knowledge,
Advantage :
Cursor is faster than while loop operation and many more.

but here you need to pay your attention on below disadvantage of cursor,
Disadvantage :
During execution of cursor it loacks the all tables which are connected with it.

Example :
if we are update 10000 records from one table to another table using cursor, then cursor locks both table.
and during execution of cursor, no another request can access these two tables.

Hope it helps you somehow.

Thanks.
Afzaal Ahmad Zeeshan8y ago
That depends entirely on the use case. Do not expose the cursors beyond the point until where they are needed. If you use them properly, they are good and allow a good way to iterate the collection of records.
Also, cursors can also lead to memory leaks and locks the resources, if you do not properly dispose them and clear them out, or release the locks on the resources. But once again, it is based on the use case. Please provide a scenario and then we would be able to guide whether you need to use cursors to load the data or not.
https://stackoverflow.com/questions/58141/why-is-it-considered-bad-practice-to-use-cursors-in-sql-server
Manav Pandya8y ago
Hello

Personally i feel the advantage using cursor is Performance , which is faster than while loop operation

Cursor is alternative of subquery , and you can process row-by-row operation

Types : like Static , dynamic , keyset-driven etc ...

For complete reference you can follow few links :

http://www.webcodeexpert.com/2013/11/what-is-cursor-advantages-and.html

https://www.sqlshack.com/using-sql-server-cursors-advantages-and-disadvantages/

https://stackoverflow.com/questions/9319918/pros-and-cons-of-using-a-cursor-in-sql-server

http://www.dotnet-tricks.com/Tutorial/sqlserver?id=RLID060512&title=SQL%20Server%20-%20Types%20of%20Cursors

Thanks