Posted by Vakul Kumar More on September 9, 2010
A conventional idea every experienced developer projects is : CURSOR is evil. Recently, I had a situation where I had to look into different options for achieving a task and many have said Cursors would the best option.
I started researching more on Cursors, as I was bit confused on contradictory ideas about cursors. A cursor is a memory resident set of pointers that reference data in your result set which has the data from the base tables. Cursors have a bad reputation because they are typically used by every Junior SQL Developer in search of an easy solution and they do not perform well and they deplete systems resources.
I personally feel SQL Server by default have a direct performance degradation on the usage of cursors. SQL ServerEngine processes records or data in such a way it will have a performance impact when cursor is used. All other parallel processes will suffer when cursor is in action and they are the SLOWEST way to access data as it operates by checking row by row operations. Cursors are 30 times slower than any other alternatives.
I adopted an alternative way to accomplish my task.
Then why is Cursor needed ? There are few scenarios where we can use Cursors:
- When you need to fire a stored procedure once per row in a table. In other words, if we need to perform row-by-row operations rather than set based operations
- Query optimizer helps in achieving Adaptability and Efficiency by adapting itself.
- When row wise validations are needed for less data.
I will keep on adding any new observations about Cursors in next few days.