What is the problem of cursor

Question

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

Bhavesh Jadav · Answer

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 Zeeshan · Answer

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 Pandya · Answer

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

Shahriar Nikkhah · Answer

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.