SQL Server – Use of Cursors
I have seen many different reasons and point of view on the use of cursors. Some people never used, some used at last resort and some used regularly. In each of these, they must have different reasons for their stand on cursor usage.
There must have a reason or place where we can use cursors in an efficient manner.
- Cursors are a bad choice, unless you understand enough about them to justify their use in limited circumstances. Cursors have their place but shouldn’t be used for set-based operations.
- When you open a cursor, you are basically loading those rows into memory and locking them, creating blocks. Then, as you cycle through the cursor, you are making changes to other tables and still keeping all the memory and locks of the cursor open. This may be the cause performance issues for other users.
- There are several types of cursors. Each one has the different performance characteristics and associated overhead. Make sure you use the correct cursor type for your operation. Forward-only is the default.
- Cursors are slower than set-based operations.
If anybody has any other ideas so please share as a comment.
Other Articles
Cursor Performance – http://www.sqlteam.com/article/cursor-performance
Read about Cursor- http://msdn.microsoft.com/en-us/library/ms181441.aspx
