If you ask any SQL Server DBAs about cursors, they will tell you it’s a big no-no. If you search for T-SQL best practices on the web, in all the results that you will find, there will always be a section similar to this: “Avoid using cursors as much as you can”.
I agree that in many cases cursors should be avoided and can be replaced by more efficient set-based programming alternatives. But, in some cases, using a cursor is the only option or the most efficient option. Otherwise, if cursors weren’t good for anything, why would they still exist?
But the general avoidance of cursors leads to lack of knowledge and understanding of how cursors really work. And, then, when the DBA does need to use a cursor, he will usually simply use the default cursor options. Not knowing what you’re doing is not a good practice.
I am one of those DBAs that have never had the motivation to understand how cursors really work. And today I had the “pleasure” of fixing a bug that I created due to my lack of understanding. There’s no better motivation than that. So now that the bug is fixed and I’ve learned something new, I would like to share it with you.
There are 4 types of cursors in SQL Server:
Static
Keyset
Dynamic
Fast Forward
When using a static cursor, SQL Server takes a snapshot of the data once the cursor is opened and stores it in tempdb. Then, all the following fetch operations retrieve the data from that snapshot. This is why it is called “Static”. If you modify data in the underlying tables, the cursor will not retrieve those changes.
A keyset cursor also takes a snapshot of the data and stores it in tempdb. But in this case, only the set of columns that uniquely identify each row are stored in the snapshot table. When you fetch a row from the cursor, SQL Server retrieves the set of columns from tempdb, and according to their values, retrieves the full row from the base tables. It means that the rows (identified by the set of keys) are static, but the contents of the rows are dynamic. If you modify a non-key value, it will be visible by the cursor. If you insert new rows, they will not be visible by the cursor. If you delete a row and then try to fetch it, @@FETCH_STATUS will return -2.
Dynamic cursors, on the other hand, expose any modifications to the underlying tables. There is no snapshot in tempdb. Instead, during every fetch operation, SQL Server retrieves the relevant row from the base tables. It means that if you insert a row that complies with the SELECT statement used by the cursor, while the cursor is open, you can later fetch that row with the cursor. Yes, it’s dynamic.
Now, let’s examine the fourth cursor type – “Fast Forward”. According to Books Online, it “specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled”. OK, so we know it’s forward only and read only, but these are options that you can specify anyway regardless of the cursor type. You can specify whether a cursor is “FORWARD_ONLY” or “SCROLL”, and also whether it’s “READ_ONLY”, “SCROLL_LOCKS” or “OPTIMISTIC” for any of the three cursor types mentioned above. So what is the behavior of “FAST_FORWARD” cursors as opposed to the other cursor types?
This is what I wasn’t aware of. When I need to use a cursor, I usually specify “FAST_FORWARD”. This is because I usually need a forward only cursor and a read only cursor. And “with performance optimizations enabled” sounds like a good thing, although I have no idea what it means.
What I have just learned is that a “FAST_FORWARD” cursor is actually a dynamic cursor with the options “FORWARD_ONLY” and “READ_ONLY”. So why does this cursor type exist at all? What is the difference between this cursor type and a dynamic forward only and read only cursor? The answer probably lies, if at all, with the “performance optimizations”, although I still have no idea what it means. And, of course, the next question is: “If a FAST_FORWARD cursor is simply a dynamic forward only and read only cursor with performance optimizations, why would I want to use a dynamic forward only and read only cursor without the performance optimizations?”
I learned it the hard way. I used a “FAST_FORWARD” cursor, as usual, in a stored procedure I wrote a few weeks ago. Inside the cursor I inserted rows into the same table that the cursor was retrieving from. I didn’t expect the cursor to fetch these new rows as well, because I didn’t know that the cursor is actually dynamic. It caused a bug, that wasn’t so easy to debug, but it was worth it. Now I know how cursors really work.
ความคิดเห็น