In Chapter 6, Easily-Identified T-SQL Anti-Patterns, we covered some anti-patterns that may impact query performance that should be obvious just by reading the T-SQL code itself. Now we will move on to some anti-patterns that may require some more in-depth analysis to be identified. These often involve T-SQL that at first glance seems straightforward, but when we dig into the query plan, there may be hidden performance pitfalls, such as expensive operations or hidden practices that prevent predicate SARGability.
In this chapter, we will cover the following topics:
- Implicit conversions
- Avoiding unnecessary sort operations
- Avoiding UDF pitfalls
- Avoiding unnecessary overhead with stored procedures
- Pitfalls of complex views
- Pitfalls of correlated sub-queries
- Properly storing intermediate results