Evaluate the Use of Row-Based Operations vs. Set-Based Operations

(This is part of the Study Guide series, 70-457)

Microsoft’s Measured Skill description: This objective may include but is not limited to: when to use cursors; impact of scalar UDFs; combine multiple DML operations

What I see:

·         when to use cursors

·         impact of scalar UDFs

When to Use Cursors

                Cursors are a funny thing in SQL Server.  Many times, data professionals come from software development backgrounds.  And as programmers, we like to think row-by-row with data.  We are extremely comfortable with for loops, while loops, and other cursory language features.  And then we step into the world of the RDBMS.  We try to transfer our programming knowledge directly to database development and administration.  So often times, in the infancy of our data careers we opt to go with cursors because they are familiar ground.  While this is an understandable route, it is often the wrong one.  We need to think of data as a set-based entity, as opposed to a collection of rows.  Cursors treat the data just like that…row-by-row.  But the optimizer and SQL Server in general are much more streamlined to deal with sets instead of looping through individual rows.  As a general rule of thumb, I tend to only use cursors when set-based operations and DML statements are absolutely impossible, or when the set-based workaround is so cumbersome and unmaintainable that it because of SQL nightmare.  There is no hard and fast rule, as there is definitely going to be a time when you run into a situation when a cursor is appropriate, but it definitely should not be a daily occurance.

Impact of Scalar UDFs

                The performance impact of scalar UDFs is the performance implication that comes along with the optimizer calling the UDF each time for ever row returned.  This could lead to a notoriously bad performance problem that often comes with scalar UDFs.  For further information, read this informative post on SQL Blog by Alexander Kuznetsov.

References

·         Blog post on why Scalar UDFs can hurt performance, by Alexander Kuznetsov

If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.