Hello Coders,
As we all know Sql Cursors, We used them for performing a certain operation in each row of record set, but why should we avoid using them?
Yes, Cursor hampers the performance.
They can lock the “Tables” which are used for populating it, so other users can not be able to update those tables while the Cursor is open.
So considering a long running cursor operation, situation can be very worst.
Some times we may get better execution time in cursor as compare to complicated T-Sql commands which we used to avoid cursor, but still cursor will lock the tables for other users so it may increase the execution time for other users.
We should considering all scenarios before opting for Cursor.
Here are some tips on avoiding Cursors:
• Do not write cursor for performing the same task on each row. Use T-SQL queries, even we may have to write many. It still gives far better performance.
• We can use "Case" statements for conditional updating in records.
• Use Temporary table "#TableName" for some subset specific changes in a table. We can also use "TABLE" variable type which available in Sql Server 2000 and upper version.
• Make use of "Derived Tables", which are also temporary tables.
• Use the "While" loop, because unlike cursors "while" loop doesn't lock the tables while looping through.
Here in this post I am not using any code sample or Syntax being a lazy bum. ;)
But these leads are more then enough for a smart coder like you.
Happy Coding
Cheers,
Nitin
