Question

How do cursors differ from set-based solutions? What are your thoughts on the use of cursors in T-SQL? Describe a specific scenario where you would use cursors over set based solutions.

Answer 1

Please find the response below:

The basic and most important difference between a cursor and set-based solution is the number of rows affected at a given time. A cursor can process only one row at a time. On the other hand, a set based solution can perform an action over an entire data set. In other words, the set-based solution works on a resultant set of data which could be a table/view or a join of both.

Cursors in used inT-SQL are called Transact-SQL cursors. They are used from stored procedures, batches, functions, or triggers. They are used to repeat custom processing for each row of the cursor.

Sometimes, the set-based solution gets so complex and unmaintainable that we prefer to use cursors. Consider a scenario:
You have 1000 records in a table that define the names of tables(metadata) that you want to copy or edit or perform any operation on it. In this case, we will use a cursor to iterate through this resultset and use dynamic-SQL to perform the operations. The above objective cannot be achieved using set-based solution/QSL.