When I'm running SQL Server Advanced T-SQL classes, we spend time discussing cursors. The general message is that most of the time, but not always, cursors are the wrong answer. The problem with cursors is that instead of telling SQL Server what you want it to work out (ie: declarative query), you are telling it how to work that out (ie: procedural query).
You'll hear people say to never use cursors. This is also incorrect. There are occasions where I do use cursors. Mostly this is when I'm building utilities that do things like scripting objects in the database. I'd almost never use a cursor though, in general data processing running against SQL Server. You don't want to be doing row by row processing against the server.
When we first review many systems, a strong code smell is lots of temporary tables and cursors. That tends to indicate that the author hasn't really understood set-based logic.
But if you have a situation where cursors are needed, it's important to know how the different types work. The different types of server cursor are:
Forward-Only Read-Only
This is the most basic type of cursor. When you open it, SQL Server executes the query and starts throwing data in the direction of the client as soon as it can. The client navigates by just asking for each row, one at a time. There's no option to go backwards, or to change the data (at least by using the cursor to change it).
Static
This option tells SQL Server to execute the query, throw a copy of all the data that it found into tempdb, and send a pointer back to the client. The client can then move forwards or backwards in the data, and it doesn't matter what other users do to the data, the client is using its own copy and is unaffected. This means that it doesn't suffer from Halloween-type issues. This is heavy work for the server and doesn't scale well.
Recently I saw a fellow MVP complaining about Halloween-type issues with other cursor types but thought he couldn't use Static cursors because he read that they don't support updates. It's true that you can't use a WHERE CURRENT OF clause to modify the data through the cursor, but there's nothing stopping you using a standard UPDATE based upon the primary key value if that was retrieved as part of the data.
Keyset
This is a lighter form of cursor. SQL Server executes the query, puts a copy of the keys into tempdb, and returns a pointer to the client. Much less data is going into tempdb. The client can then request forward or backward movement along the cursor, but each time they request data, SQL Server has to go to the original data to find the values to return, based upon the stored keys. If another user adds additional data, they won't see it because they already have the list of keys. If another user deletes data though, the client can have a problem. If they ask for the next row, the key might be in the cursor but the data might no longer exist. The client has to deal with that scenario. (This can cause errors on clients that don't cater for errors). Because this cursor is connected to the original data indirectly, it's considered an updatable cursor.
Dynamic
This cursor is the lightest from the point of view of the server. All that's stored at the server is the query and a pointer as to where it's up to. When the client asks to go forward, it looks up the next row and returns it. Same for backwards. The challenge with this type of cursor is that the client is exposed to all the vagaries of impacts from other users. As other users add or delete rows, interesting outcomes can occur. For example, you could be on one row, step forward once, then step backwards once, and not be on the same row that you started with, because someone else added or deleted a row. It's also an updatable cursor type.
