A Case FOR Cursors...

,

We've all heard that definitive statement: "Avoid using cursors."  But, in my opinion, what really should be conveyed is: "Avoid using row-by-row operations when possible."  I understand this is a "hot" topic, so before going any further, please run the following:

SELECT  N'master' AS [Database], COUNT(o.[object_id]) AS [MS Objects Using Cursors]
FROM    master.sys.all_objects AS o
            INNER JOIN master.sys.all_sql_modules AS m
            ON  o.[object_id] = m.[object_id]
WHERE   o.is_ms_shipped = 1 AND
        m.[definition] LIKE N'%DECLARE%CURSOR%OPEN%FETCH%CLOSE%DEALLOCATE%'
UNION
SELECT  N'msdb' AS [Database], COUNT(o.[object_id]) AS [MS Objects Using Cursors]
FROM    msdb.sys.all_objects AS o
            INNER JOIN msdb.sys.all_sql_modules AS m
            ON  o.[object_id] = m.[object_id]
WHERE   o.is_ms_shipped = 1 AND
        m.[definition] LIKE N'%DECLARE%CURSOR%OPEN%FETCH%CLOSE%DEALLOCATE%';
GO

On my SQL Server 2014 instance, I receive the following results:

Microsoft Uses Cursors

OK, so there are quite a few objects written by Microsoft that use cursors.  And guess what - they're running on your production servers!  (Gasp!)

If you'd like to see which MS objects are actually using cursors, run the following:

SELECT  o.[name], o.type_desc
FROM    master.sys.all_objects AS o
            INNER JOIN master.sys.all_sql_modules AS m
            ON  o.[object_id] = m.[object_id]
WHERE   o.is_ms_shipped = 1 AND
        m.[definition] LIKE '%DECLARE%CURSOR%OPEN%FETCH%CLOSE%DEALLOCATE%'
ORDER BY
        [name];
GO

I've heard and read a lot of debates on the use of cursors, and I have not come across an official statement from Microsoft recommending against the use of cursors.  And, I'm not sure I see the difference between using a LOCAL FAST_FORWARD cursor and a custom WHILE loop.  I believe the more appropriate debate should be: "Can my row-by-row operation be streamlined into a more efficient set operation?"

The Benefit of Cursors Yes, that's right, "benefit".  In trying to avoid using cursors, I've seen a lot of different methods for performing row-by-row operations.  The benefit of cursors is that it provides a standard format for these operations, which can make deciphering others' T-SQL code much easier.

Keeping an Open Mind

Obviously, I don't fall in the die hard, anti-cursor camp.  That doesn't mean I can't be converted, but again I haven't heard or read a definitive reason why cursors are so evil.  Maybe it's time to change the mantra from "avoid cursors" to "avoid row-by-row operations".

Rate

3.24 (109)

Share

Share

Rate

3.24 (109)