Brent Ozar takes us through a realistic but nasty scenario:
When I do performance tuning for clients, I really pride myself on making as few changes as possible in order to make a tremendous difference. I consider it a failure when I have to tell someone to rewrite a bunch of queries from scratch.
However, there are some cases where I just can’t work around a perfect storm of anti-patterns. To understand why, let’s take an example. I’ve kept the general idea the same, but I’ve rewritten the entire example in the Stack Overflow database to protect the innocent.
I’ve seen cases similar to what Brent has. Developers understand encapsulation and minimizing code repetition, so they naturally want to do that with SQL, but the optimizer eventually gives up and picks a terrible plan. DRY is great for application code and normalization, but unfortunately, it’s not always great for T-SQL.