TIL #2 – T-SQL Refactoring w. Mike Donnelly

Today for my TIL Holiday Learning Series selection, I decided to watch Mike Donnelly’s (b|t) Summit 2014 Session – Twice in a Lifetime: T-SQL Refactoring 101. He’s become a good friend over the years, but I’ve yet to see one of his sessions at a SQL Saturday/User Group.  So I thought watching his Summit Session would be fun. As a hardcore T-SQL developer myself, I was really curious to see what insights he had to offer.

DISCLAIMER: In the interest of not “exposing” each presenter’s entire session, I’m not going into great detail or documenting “everything” I pulled out. After all, I want to encourage everyone to view the session for themselves.

Here’s my list of interesting notes & tidbits:

  • Mike opens by telling a personal story, which frames the remaider of the session. A story that many of us can relate to.  He then shares the process of what he had to go through.  Great technique as a presenter!
  • While discussing T-SQL anti-patterns, Mike brings up Table Variables. As an addendum, he mentions Trace Flag 2453, which aids in addressing what I argue is the biggest weakess of Table Variables – nonsense cardinality estimation of 1. There are trade-offs to using this Trace Flag, which has been blogged about elsewhere.
  • Mike presents a list of common anti-patterns.  Idea came to me, to write up a Quick Reference/Cheat Sheet, the next time I am faced with auditing a large codebase for refactoring.  While many of the anti-patterns are now ones I recognize immediately, others are more uncommon/obscure, that I don’t always remember immediately.  A cheat sheet or checklist would be a useful personal tool, or one to share with a team.  And one which I’ll add to my personal backlog of things to do (and probably blog about).
  • Mike discusses Deprecated Features and one I wasn’t aware of was Three (or more) part COLUMN naming.
    Example:

    SELECT
    schemaName.tableName.columnOne,
    schemaName.tableName.columnTwo,
    schemaName.tableName.columnThree
    FROM databaseName.schemaName.tableName

    In the above example, the column references will be limited to two parts only: object name & column name. But in non-column references, multi-part naming is still allowed.  I find this one interesting in particular, because a former colleague was using a refactoring tool, which was reformatting his code into the 3 part column name format!  Whoops!

I really enjoyed Mike’s presentation.  Instead of taking the angle of refactoring existing code, it could easily also be presented in the context of practices when developing new code.  It is definitely a solid session that I’d recommend to anyone who finds themselves writing, auditing, and/or refactoring T-SQL code.

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s