So what is a Cursor?

A Cursor is basically a dataset (the result of a Select Statement) put in memory. You can loop through the dataset row by row and perform any action you want. So basically you get a set of rows and then perform what needs to be done one row at the time.

Why the bad reputation?

Cursors are slow

This one is true. Because you do things one row at the time a cursor is not always the fastest way. So yeah, if your dataset has thousands of rows a Cursor might not be the best choice.

Cursors take up a lot of memory

This one is also true. SQL Server puts the dataset in memory. For a dataset with thousands of rows again, the Cursor might not be the best choice.

There are better ways

This one is not true. Well, at least not always. Yes, sometimes I see Cursors being used that could be done in a different and better way. But there are more than enough cases where a Cursor is the only option.

So don't believe the hype. Cursors have been in SQL Server for as long as I can remember and they certainly aren't going away any time soon. Simply because in many cases they are the only solution.

What does a Cursor look like?

First let's have a look at how a Cursor is handled in SQL Server. Basically it follows the following steps:

Declare the Cursor
The first step is to let SQL Server know you want to work with a cursor. And this is where you set the Select Statement to get the rows you want to work with.
Open the Cursor
Once the dataset is in memory, you need to open it to work with it.
Fetch a single row
Now the Cursor is open, you can fetch the first row from the dataset.
Do stuff and fetch next
Here's where you can do whatever you need to do with the data from the row. Once you are done, you can fetch the next row. Repeat this until the rows are all handled.
Close the Cursor
Once you are done handling all the rows, you can close the Cursor.
Deallocate the Cursor
This is an important step because it deletes the Cursor from memory. So if you forget this one, it might impact your performance since some memory cannot be used for other tasks. Always be sure to do this one.

So basically a Cursor looks like this:

Let's see it in action

In this example the dataset for the Cursor are the Table Names from Sysobjects. For each Table Name, it is checked in which Views it is used.

declare @TableName nvarchar(150)

declare cTables cursor for
select Name
from sysobjects
where xType = 'U'

open cTables
fetch next from cTables into @TableName
while @@FETCH_STATUS = 0
begin
  declare @Views nvarchar(500)

  select @Views = isnull(@Views + '; ', '') + so.Name
  from syscomments sc
  inner join sysobjects so on sc.ID = so.ID
  where text like '%' + @TableName + '%'

  if @Views is null
    set @Views = 'Not used in Views'

  print concat(@TableName, ' is used in the following Views: ', @Views)

  set @Views = null

  fetch next from cTables into @TableName
end

close cTables
deallocate cTables

Let's break it down a bit

As shown in the steps a Cursor follows, first thing to be done is to declare the Cursor and fill it with the data that is going to be used. In this example I gave it the name cTables:

declare cTables cursor for
select Name
from sysobjects
where xType = 'U'

Once that is done, the Cursor should be opened:

open cTables

So now that it is open, we can fetch a single row from the Cursor:

fetch next from cTables into @TableName

The Table Name goes into the variable that was declared earlier. If you need more than one Column, just list the Columns in the Select Statement. For each Column, you need to declare a variable and then fill them up like this:

fetch next from cTables into @Column1, @Column2, @Column3

After fetching the first row, we need to let SQL Server now that it should be doing this until all the rows are processed. This in done with a While loop. In other words, while there are rows to be fetched from the Cursor, please do so. If no rows are there, stop the action.

That looks like this:

while @@FETCH_STATUS = 0
begin
-- This is where the magic happens...
end

And never forget to Close and Deallocate the Cursor once you are done:

close cTables
deallocate cTables

Cursor Best Practices

Be careful with large datasets

As said, the dataset is put in memory. So you don't want really large datasets in memory for to long during office hours. Especially if you do time consuming actions while looping through the rows. But for smaller datasets of just a few hundred rows, your SQL Server should be quite able to cope with that.

And do note that you can use a Cursor inside a Stored Procedure. So if you need to handle a bigger dataset, do it within a Stored Procedure and then schedule it with the SQL Agent to be done outside office hours.

Use unique names

Avoid the use of general names like cCursor or cDataset. A Cursor can only be declared once with the same name inside a database. This is especially important when you use them in scheduled procedures.

Let's say you have a Cursor called cCursor inside a Stored Procedure. While that Procedure is running, another Procedure is starting with a Cursor by the same name. Now your second Procedure will fail because a Cursor with the name cCursor already exists.

Therefor, always use names that are unique and recognizable. Even if you don't plan to do many Cursors in your Database. You never know what you might add later. And remember, the next guy who has to maintain your database will thank you for it.

Feel free to use them!

Yeah, so some of the Cursors I wrote could be done in a different way with better performance. But sometimes I prefer to use a Cursor, especially when the alternative becomes difficult to read and understand. Remember, you don't just write is, you will have to maintain it too.

And yes, performance is important. But when it runs outside of office hours for just a few minutes? Then I prefer readability and maintainability.

That's it for Today!

Hopefully this gave you more insight in what Cursors are and how and when you might use them. Yes, you have to be a bit careful using them, but in no way you should avoid them all together.

In some cases there isn't even a different option. So play around with them and use them to your advantage!

You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!