To kick off my TIL Holiday SQL Learning Series, I decided to watch Jeremiah Peschka’s (b|t) Summit 2014 Session – Dynamic SQL: Build Fast, Flexible Queries. I’ve been using Dynamic SQL for a terribly long time now, so wanted to refresh & validate what I already knew.
NOTE: In the interest of not resummarizing each presenter’s entire session, I’m not going into great detail or documenting everything I learned. After all, I want to encourage everyone to view the session for themselves.
Interesting notes & tidbits:
- Knew about QUOTENAME() but never about PARSENAME(). The latter is pretty neat and can be useful for other object validation scenarios.
- Never thought of using Dynamic SQL to add/omit JOINs, based on the existence of data. This can aid in elimination of LEFT OUTER JOINs & reduce unnecessary I/O & streamline an execution plan.
- Use a @NewLine variable to format your Dynamic SQL. CHAR(13) & CHAR(10). After all, we format our normal code (hopefully), for readability.
- Validation – I’ve used a @Debug variable flag for a while, which is a tip Jeremiah recommended too.
- Troubleshooting Tip – Start using comments INSIDE of your Dynamic SQL strings, particularly listing the “source.” Also use slightly different variable names inside Dynamic SQL vs outside to aid diagnosis. Ex: @MyVariableOne vs @my_variable_one.
- In the final demo, Jeremiah shares an awesome approach to making PIVOT much less painful. Watching the entire presentation is worth this demo alone!
I really enjoyed Jeremiah’s presentation. Thrilled that I was able to glean some new insights, while validating techniques I’ve already been using. If you ever use Dynamic SQL, be sure to check it out!