This month's T-SQL Tuesday is hosted by Aaron Bertrand, MS-MVP and SentryOne Product Manager, and he gives us the potentially dangerous choice of writing about our non SQL Server passions or writing something about his favorite T-SQL Bad Habits.
Surprisingly I'm taking the option of writing about my favorite of Aaron's T-SQL rants. This is one of those things that I knew somewhere deep in my head, but I just never thought about it much. A few years back I went to one of Aaron's sessions at SQL Intersection. He was talking about his arguments for using stored procedures over ad hoc queries (his blog post about this can be found here ). I am potentially one of the worst when it comes to writing well formatted T-SQL. If I'm creating queries that I'm going to post or that other people will see, I try to use standard T-SQL formatting, but if I'm just querying for data, I don't pay a lot of attention to capitalization and white space. Any change to a query – say SELECT vs select – will (EGADS!) generate a new execution plan. Now if you have
optimize for ad hoc workloads enabled, the cost is less since only a stub of the plan is saved, but I tend to keep my SSMS tabs open and run queries multiple times and that's when it gets expensive. In Aaron's blog post, he shows examples of the different queries and the execution plans that are created.
While I'm sure that I could logically have deduced that, hearing it and seeing the different execution plans that are generated were a good wake-up call for me. I work on trying to be a little better about formatting, but I also have a better understanding about all of those single use plans in my plan cache. Thanks, Aaron!