If you haven't seen the Simple-Talk newsletter (it's free, so sign up!) you may not have seen my article on their site. http://www.simple-talk.com/sql/performance/writing-efficient-sql-set-based-speed-phreakery/  Writing this article has been probably the most fun I have had writing. Basically, I had to take the winning solution to a T-SQL programming challenge and try to figure it out and explain how it works. I also compared the winning solution to three other solutions that are easier to write and understand. If you are having a hard time convincing yourself that row-by-row processing isn't that bad, this article should convince you otherwise.

In my job, I inherited several stored procedures that run once per night that would take 10 minutes or more to run. Besides the time they took to run, they could also leave some of our applications in non-working order if the job happened to fail. For example, the proc to add new users to our document management system (DMS) first would disable all users. Then set up a cursor that updated each user with current information and re-enabled the active employees. If anything went wrong in the job, for example an unexpected NULL value in the data from our HR system, the entire office could be locked out of the DMS. The the proc could never be run during business hours and there was lots of hard-coding that made the proc difficult to maintain.

So, within a few months I rewrote these problematic procs using set-based code instead of cursors. I also corrected the faulty logic so the the procs could run anytime if we needed them to and set up a table to hold the data that used to be hard-coded. A stored procedure that used to take 10 minutes, now runs in about 10 seconds. I didn't use any of the phreaky techniques that the winner of the contest used, but just eliminating the row-by-row processing made my process very efficient!