T-SQL Tuesday #195 – How Has My Code Aged?

This month, Pat asks us to consider how our code has aged. Like fine wine, or old milk?

A common time to revisit old code is when something breaks. I was contemplating Pat’s question this week when a field length change had caused a truncation issue in an old SSIS data flow. Some code doesn’t age badly because it’s wrong, but because it didn’t expect to fail.

It’s all too easy to write a piece of code which ‘does the thing’, run a few variant tests, and send it on its way. But will it stand the test of time? That’s where my good and bad code diverge, and I usually revisit the code and find the old milk.

If we’re dealing with scripts for SQL Server (this is T-SQL Tuesday, after all), we have an exceptional reference in the form of Erland’s extensive articles around error handling.

Where exception handling becomes trickier is when we have solutions which aren’t as atomic as a stored procedure – for example the SSIS package above, a .NET function, or a pipeline in Data Factory or Fabric. In these examples, it’s the design of the solution which makes the handling robust.

Historically I struggled when starting to learn SSIS where there wasn’t a simple rollback mechanism. A package would fail and I’d be left with a partially complete process which needed manual intervention or rollback before it could be restarted. The impact was much greater than it should have been.

Simply introducing logging and state-tracking frameworks meant “it’s ok to fail”, and elements like staging tables and validation with explicit exceptions meant “sometimes, it should fail”. That’s not a bad baseline to start with.

Thankfully these same patterns graduate nicely into more modern solutions such as data pipelines.

Looking back, the code that aged the best wasn’t the cleanest or fastest, it was the code which was prepared to fail. I think a degree of that can only come from experience – seeing how things fail, and understanding how to recover that.

When it comes to fixing up broken code, it’s not enough to make it work correctly. It needs to fail correctly too.