TIL #1 – Dynamic SQL w. Jeremiah Peschka

To kick off my TIL Holiday SQL Learning Series, I decided to watch Jeremiah Peschka’s (b|t) Summit 2014 SessionDynamic 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!

One thought on “TIL #1 – Dynamic SQL w. Jeremiah Peschka

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.