Cursors in SQL Server enable you to work with each of records in a record set, so that you can sequentially work with those records and carry out the required task.

The best example that I can recollect to quote here is when you have a table with 100 rows, with one of the columns as email address. You are required to send an email to all valid records in that column and each record is some way or the other has its own condition, then a Cursor would be a good way to implement this.

What cursor does is, it enables you to fetch the data into the memory and then for each of the row in the data set, you can look at the row, can implement any custom logic that is needed and move on to the next row until you reach the end of the records.

There are different types of cursors available and based on the one you choose, you can actually move back and forth with the records.

One other example I can give is, when you need to run a backup for all the databases in a SQL Instance, it is very easy to implement using cursors and has been used by me and many SQL DBA’s before the introduction of the DTS and SSIS technologies.

That is the main benefit of cursors, but the flip side to it is, they are avoided in situations where the dataset is large, due to performance issues with cursors. Since they have this unique ability to traverse through the dataset, they occupy a high amount of memory in the Server.

You should be able to find many examples on this topic and I shall try to publish an article with more details in one of my next blogs.

–Bru Medishetty