Clean code, the SQL: Part 2: Electric Boogaloo

I enjoyed the conversation, because it really got us both thinking more deeply about which areas of our app landscape are in better/worse shape than others.

Advertisements

After the previous discussion about nested views, encapsulation & abstraction, I’d like to write about duplication specifically, and the distinction between actual lines of code being duplicated, versus functional duplication. Because the latter is not OK, but the former is generally acceptable when it’s boilerplate code, or done, again, in the name of performance and efficiency.

boilerplate with alphabet and stuff
letters and numbers and symbols!

 

So, to expand on last week’s “Encapsulation & Abstraction” segment.  The conversation with one of my favorite developers went something like this.

Developer:

While I agree that there’s some over-reliance on nested views, the reason they get implemented a lot is because there’s a particular problem they seem to easily solve: how to encapsulate business-data rules without violating DRY.

Let’s say we have a biz-rule for a “core segment” of data.  For simplicity’s sake, let’s call that rule “Widget A consists of a Widget record and a WidgetSupplement record joined by WidgetID, where Widget.WidgetType is ‘foo’.”  So it seems obvious to create a view WidgetFooComplete, which pulls in the data from both tables and applies the type condition.  This creates a sort of “atomic building block” of data, which can be consumed by apps & data-access methods repeatedly & consistently.

Now, most downstream apps will use that WidgetFooComplete data in its entirety (or nearly).  But let’s say there’s a hot new app that needs some more data about the Widgets, and it needs to go out to the WidgetMoarProperties table.  The natural inclination is to incorporate our existing “building block” view, WidgetFooComplete, into a new view for this app & its dependencies, and call it WidgetFooMoarComplete.

But what’s the alternative?  If we re-create the JOIN/WHERE conditions on the base-tables in this new view, it violates DRY and makes possible future refactoring difficult if that biz-rule changes.

Admittedly, most modern data-access technologies make it easier to create these “building blocks” of joined data entities.  And sometimes those biz-rules belong in the app’s lower layers, but this can lead to writing lots of little disparate queries/db-calls for what should have been one atomic operation.  That can be a maintenance headache, as could dozens (hundreds) of tailored stored-procs for every data-access scenario.

So it seems like nested views can have their place, but “deep” nesting is usually troublesome.  And to prevent the “slippery slope” effect, we have to practice diligence.

Me:

That’s pretty spot-on.  DBAs tend to criticize them (nested views) as a practice in general because of the tendency to over-use and over-rely on them, and because of that slippery slope, where “a little” use turns into “a lot”, and leads to troubleshooting headaches.  And generalizations are just that.

To take some examples in-hand: simple entity relationships, especially when biz-critical, should be A) obvious, and B) documented.  Unified views can serve this purpose, but should only be used where appropriate — i.e. to load an object that gets passed around/up the app stack.  They’re great “atomic building blocks” when you actually need the entire block of data.  But when you don’t — say in a stored-proc that’s doing some data flow operation and only needs a small subset of that data block — it’s probably better to get the relationship logic from the view and copy-paste it (but hopefully not all of it!), while omitting the stuff that’s not needed.

The main reason for this is usually index tuning.  If we’ve crafted some indexes to meet certain query patterns in certain troublesome procs, we want those procs to use those indexes, not just do a full table scan because they’re using a nested-view which does select * .

When we get to more complex business rules, we need to up our diligence game and be more mindful of dependency checking when planning for a rule change.  Proc comment-headers can be helpful here, as can tools that search thru SQL object meta-data and code-bases to produce dependency chains.

The main point is, duplication tends to be OK when it’s not functional duplication, i.e. when the SQL code is more-or-less similar in some places but it’s not exactly the same because the purpose (responsibility) of that module/stored-proc is not the same.

You’re right in that the “31-flavors of tailored procs for data-access” is a big maintenance headache, and sometimes that trumps even the performance concerns.  Again it’s about balance — we have to be mindful of both the biz-rule-maintenance concerns and the performance concerns.

Developer:

I figured.  Sometimes I see DBAs criticize developers’ work without seeming to understand that it doesn’t always come from sloppiness or laziness (although sometimes it does!).  Often, we’re trying to thread that needle of performance vs. maintainability.  In Dev-land, “lazy” is good in the sense of aiming for simplified logic, for ease of both maintenance and understanding.  Painstakingly tailoring each data-access call (stored-proc), while good for performance, is kinda opposite of that.  But, admittedly, we do fall back on SELECT * all too easily.

Mostly, we try to avoid code duplication because it leads to heavier maintenance overhead.  When some modules may perform similar operations, functionally, they will often re-use the same “core” logic, which we in turn encapsulate into its own ‘thing’.  But in SQL modules, as you say, that’s not always performant, so it’s definitely a tightrope-walk.

The “Clean Code” school of thought says, if it’s obvious, it’s “self-documenting”.  I don’t always agree with it, but it comes from maintenance concerns again.  We don’t like situations where someone tweaks the code but doesn’t update the comments, and you end up with misleading comments.  Unfortunately, it does come down to diligence again, and even “good” developers will easily fall back to rarely including comments just to avoid this situation.  Of course, another potential pitfall of supposedly self-documenting code is, what’s “obvious” to one person isn’t necessarily so to everyone else!

(We both enjoy writing, can you tell?)  =P

So basically we agreed to “moderation in all things” and exchanged Buddha statues and sang Kum-Bay-Yah.  I enjoyed the exchange because it really got us both thinking more deeply about which areas of our business/app landscape are in better/worse shape than others.

developer-dba-harmony-buddha
yay collaboration!

To conclude this part.  You will continue to see DBAs rant and rail against nested views and other “sins against SQL”, but:  Developers, don’t take it personally — we’re just trying to eek the most performance-per-$3k-core-license out of our precious servers, and spend less time chasing the white rabbit down the nested-views-hole.  And DBAs, go easy on your Devs — they still outnumber you, and they can whip out a complete web-app using the hottest JavaScript framework and a cloud-of-the-month service, faster than you can tune a server.  Everybody’s valuable, and everybody works toward the same goal: solving the business’s problems thru technology.

Moving on…

Part 3: Misusing & Abusing Datatypes

Because I’m getting long-winded again, let’s wrap up with a final “Clean SQL Code” topic that’s short & sweet.

Well, not really.  There are entire presentations dedicated to this topic.  But I’ll try to keep it condensed.

A date is not a datetime is not a time​ is not a time interval.  Okay?  For the third time, stop interchanging them!  Yes I know, SQL Server is a bit behind some other RDBMS platforms when it comes to this stuff.  Sorry, I don’t work for Microsoft.  I just deal with their tech.

Deep breaths…

More to the point, know your data.  Understand that there can be consequences to repeatedly casting types, or losing precision during conversion, sometimes exponentially so.  Yes I know, we all love loosely-typed (sometimes stringly typed) languages like JS & Python.  Those are wonderful tools for certain jobs/problems.  Again, be mindful and know your flows.

flow with the chart yo
I’m not sure what’s more disturbing.. the fact that this was the first image search result for “flow meme”, or the fact that it’s actually quite appropriate.

Thanks for reading, as always!

Clean Code, the SQL

as a developer, DBA, or hybrid “DbDev”, you’re often tasked with writing or improving the stored procedures which house that complex logic.  And that’s my topic today: being clean about your SQL code.

Get it? It’s just too punny! … Ok I’m done.

The Coding Blocks guys did a series of episodes about the perennial favorite Clean Code book.  If you haven’t subscribed to their podcast…

do it do it now -Arnold
What are you waiting for?!?!

And it’s a great book, no doubt. But those guidelines for application code are not 100% directly applicable to database code.

wait... what?
Srsly?

Let’s back up a second. Why? That sounds about counter-intuitive, no?  Ok, more context. See, the traditional (“legacy”?) app consists of about 3 layers, the bottom one being the database. And it’s usually relational, and is usually responsible for far more than simple data access and persistence.  Read: complex business rules and process logic.  Data flow, not just getters and setters.

So that means, as a developer, DBA, or hybrid “DbDev”, you’re often tasked with writing or improving the stored procedures which house that complex logic.  And that’s my topic today: being clean about your SQL code.

Part 1: Comments

There’s a fairly famous quote from the book about comments:

Comments are always failures.

He’s using hyperbole, but for a purpose.  While his views on comments may be extreme, most programmers tend to realize the core essence of that chapter, which is that comments only serve to express something in plain English that the code has failed to express clearly enough to be easily and immediately understood.

With SQL scripts, and in particular with stored-procedures, I’m taking a somewhat opposite stance:

Comments are always appreciated, even if they’re potentially outdated or inaccurate.

There are two types of comments in SQL, the --inline and the /* block */.  Different people have their preferred flavors of block — sometimes it’s just several lines prefaced with the double-dash --.  And that’s fine, whatever floats your comment-boat.

hms commentus comment-boat
I made my own!! (most copied from an example at http://www.asciiworld.com/-Boats-.html)

In particular, I always encourage a comment block at the top of ever stored-proc & other user-defined programmable objects (function, types, etc).  Just a small example for illustration:

/*
Location: Server.Database
Author: NateTheDBA
Created: 2012-12-21
Description: Gets users who have not logged in since the given date.
Consumers: MyCoolAppName, MyReportServer
Revisions
2015-05-15, Nate: removed archive (never used after archive-date)
2017-06-07, Nate: fixed formatting for blog post
*/
CREATE PROCEDURE GetUsersNotLoggedInSince
    @SinceDate datetime2
BEGIN
    --some clever stuff goes here...
END

“But wait”, you say, “what about source control?”  Yes, all your programmable objects (and even, arguably, your reference data) should be in source control.  There are tool-vendors aplenty to help you with that.  But guess what?  Budgets.  Time & effort.  Oh, did I mention, legacy legacy legacy?  Yes, dear reader, the average business has years (decades) of organically evolved relational databases and processes.  Are you the guy or gal to swoop in on your unicorn and seamlessly convert their entire data tier infrastructure to a beautiful DevOps pipeline with shiny rainbows and kittens for all?  No?  Okay then.  Baby-steps.

devops-unicorn-dba-cleaning-up
Not that I’m bitter or anything…

Yes, my procs are in source control.  It’s called “daily automated script-out-objects-to-files which are then committed to SVN”.  It’s not built-in to SSMS.  Which means that I, or another DBA, or a potential consultant, or a Dev who gets enlisted to help improve a proc that runs for hours when it should only take minutes, would be inconvenienced by the extra trip to a separate tool/system to fetch some change-history just for context.  And really, that’s all this is for — CONTEXT.  We like to know what it is we’re working on when we start to work on it, without having to traverse a change-tree or go bug 3 other people who “might” have touched it last.  I’m not asking for a detailed log of every single time someone touched the thing; just give me the overview, the milestones and significant changes to functionality/features/scope so that I have a jump-off point for troubleshooting/testing/reasoning about it.

“But wait”, you say again, “shouldn’t your name be a sufficient description of what the proc does?”  Sure, in theory.  Until you have dependencies which need that name to stay the same even after an update or logic-change.  Like reports.  Or data-connected Excel workbooks.  Which are used daily by managers, who will come yelling at you if their worksheets suddenly stop functioning.

end rant

Back to comments in general.  The reason they’re helpful (besides documentation-headers for objects) is that they provide context and explain intent.  Half the time, my job as a DBA is improving or fixing someone else’s code.  Therefore, I want to see, in plain English, what it is they’re trying to accomplish, notes about attempts and failures, and the like.  Yes, I could have a discussion with them.  And I will.  But if I’m working on it asynchronously and they’ve moved on to something else, or our hours are different, I want those little nuggets of context and intent to be right there in the script, because that’s where I’m working!

What about queries that get passed-down from the app to the DB?  ORMs don’t support pre-pending a comment to their data calls, do they?  I wish.  Maybe some do, I haven’t researched it, but I know for sure that LINQ doesn’t.  But then again, when I’m using a query-capture tool (like DMVs, Profiler, X-events, or a vendor monitoring tool), ORM queries are so painfully obvious in comparison to hand-crafted SQL that I can usually spot them from a mile away, and go bother the app-devs for some context & conversation.  If you’re one of the poor unfortunate souls who still passes ad-hoc generated SQL statements down thru ODBC to your DB, then sure, a little comment won’t hurt anybody.

you poor unfortunate soul
it’s sad, but true…

So do your DBAs a favor, comment your SQL code, at least in terms of programmable database objects and ad-hoc scripts.  I promise, it’ll make them hate you less.  It might even make you love yourself more, because 3 months down the road when you revisit that proc, and you need to remember what it was for and why you did it that way, you’ll see it right there in your very own writing!  (OK, typing.)

Part 2: SRP, Encapsulation, and Abstraction

A bit of paraphrase of one of the book’s key points:

A reusable module (function, method) should do one thing, and do it well.

Also, the DRY principle:

Don’t repeat yourself.

When building SQL modules, we’re usually concerned with performance and accuracy, over abstraction and composability.  Therefore, repeating oneself is not necessarily a bad thing, when done for the right reasons.  Diligence is a big factor here — if there’s a non-trivial relationship between some entities that you’re repeating in several places, and you know that it could become a maintenance headache if that relationship’s definition has to change later, do as much as possible to mitigate the risk of dependency/consistency-loss.  This can be documentation, comments, and/or building that relationship into a view.

Make-Time-for-Due-Diligence
It’s important.

The latter brings up an interesting topic, one which I had a lively discussion about with a colleague recently (he’s a developer, and a dang good one) — nested views.  Because inevitably, the encapsulation of those relationships & business-rules into things like views or ITVF’s can and will lead to nesting those objects into other objects.  And troubleshooting many-level-nested views is a particularly frustrating exercise; in fact they’re what some DBAs call one of the “deadly sins of SQL“.  But there are perfectly valid reasons and uses for them, sometimes, and I really enjoyed the discussion thread we had on it, so I’ll have to expand on that in another post.

Anyway, I’m already getting long-winded and well over 1k words, so I’ll wrap it up for now, and continue this topic next week.

Thanks for reading, stay tuned!

Reports for Auditors

This is how code quality dies…

Look, I get it, audits and auditors are a necessary evil.  But the sheer volume of crappy code, hack-y scripts, man-hours, and alcohol, dedicated to meeting the often arbitrary, sometimes ridiculous, and possibly even downright obscene requirements, of said auditors, is staggering.  There are entire product suites dedicate to help you “report on” or “meet/pass” audits of various kinds.  And thank god for those.  Because without them, we’d all be slightly more insane than we already are.

I’m not crazy; my mother had me tested.  And my rubber duck agrees.

Here’s an example.

In one of our main applications, at the database level (SQL), we’ve implemented poor-man’s data-change-tracking via table triggers.  In a past audit, there was a “flag” on an unauthorized data change.  As a result, an “exception report” was created, which basically produces a daily CSV dump of those changes and emails it to a few managers.  Now, because the tracking table includes “who” as well as “when” & “what”, and there are certain privileged accounts that can be ignored for auditing, and because there are dozens of application users whose app-controlled changes are also logged in said tracking table (and need to be ignored), the “report” writer at the time decided it was a wonderful (read: terrible) idea to hard-code a list of usernames into the query that produces this data-dump.

That is horrible.  Never do that!

using hard-coded values is bad
Mr. Garrison knows…

This query, the job that scheduled it, the data that it produced, and the emails that it sent, were subsequently filed away and silently forgotten about.  Until we did some environment cleanup and discovered “Oh look, here’s a thing that’s not doing anything” (because the hard-coded list of users was now woefully out of date), and the same managers who received the reports for years past, gave the OK to disable the job!

What could possibly go wrong?

Then the next audit season comes around.  And guess who comes running back to the DBA having changed his mind?  Why yes, that very same manager!

i dont always say i told you so
Stay shortsighted, my friends…

So I tell him what we need to do to fix it — remove the hard-coded list, replace it with an AD group or at least a look-up table that can be periodically updated with “current” users in the desired departments/teams.  We do that, and the CSV dump goes from zero to huge.  As in, too huge for email.  So we try to pare it down.  We get it to a manageable size.  Then we get ready to deploy the changes and confirm the desired email recipients.

  • Boss: “Oh it doesn’t really matter, we just ignore the emails anyway… it’s just to satisfy an audit requirement.”
  • Me: “So why did we even bother fixing it?”
  • Boss: “Well, it was broken!”
  • Me: “…”

I’d like the last hour of my life back please.

stress-reduction-kit-bang-head-here
It works, I sw…

Here’s another fun one.

The ERP system has a rickety tack-on audit-trail system involving some SQL Agent Jobs and an obscenely large data repository for storing all the changes that the ERP system users make every hour of every day.  Thankfully, somebody along the way was prescient enough to partition those storage tables using a partitioned view scheme.  But apparently there were NOT smart enough to realize that the clustered index needs to be the datetime column.  Yes, it was in the primary key, but that PK was not clustered (nor should it have been, since the actual unique keys were GUIDs, but this is a prime example of making the clustering key different and separate from the PK.

Obviously enough, in a date-driven partitioned view scheme, that date does need to be involved in the PK also.  But again, the best clustered index is just that date, because A) it’s the partition divider, and B) it’s always your queries’ main filter-predicate (which is a fancy way of saying “the thing in your JOIN/WHERE clauses”).

Once again, audit season comes around, and someone needs those reports driven by these audit-trail repos.  And guess what?  They’re slow as molasses.

you don't say
Nicholas Cage is apparently quite the flexible meme fodder..

So we get to spend another few hours applying the correct clustered indexes, waiting for them to rebuild, testing the queries for the report, and finally running the report itself.  Oh and don’t forget the down-time of the jobs, and having to “catch up” with all the changes that were made in the ERP system while we were working on all this.

Is it 5 o’clock yet?

In conclusion.

Audits suck.  But please, stop producing crappy code in response.  It just makes the next guy/gal that comes after you even more frustrated than he/she already is with the whole process.  Practice the “boy scout principle” — leave things at least a little better than you found them.  And if you’re forced to produce a ridiculous “exception report” that nobody will ever read, spend as little time on it as possible while still making it less heinous than the last terribly designed monstrosity.

oh my look its beer-o-clock
That clock might be slightly ahead… but we’re gonna go with it.

SQL Server for the Developer

And now for a brief interlude while I work on my next “real” post… continuing with the same theme… SQL Server for the developer/programmer!  Warning: potentially excessive snark ahead.

caution-sarcasm-ahead

SQL Server is a mystical fairy-tale land where you can store all your application data, and never have to worry about it again!  Sure, it “should” be relational, but you also “should” drive the speed limit.. PSHH!  Let’s throw blobs in there too — HTML, XML, hell, even JSON now that 2016’s got nice happy JSON-centric system functions.  And files?  Heck yes, what else is FILESTREAM for?  Sure, performance may be abysmal, but that’s somebody else’s problem, so why should we care?

It’s the only database platform your company invested in, so it must be good for everything.  Document storage?  Sure, not like there’s anything else better suited for the job, especially for ‘Free’!  Ooh, how about key-value data, and volatile temporal data like .NET Session-State?  Obviously!  Nothing else exists, and Microsoft makes it so simple to set it up in SQL.

sarcasm-sign-everybody-needs-one
Here’s your sign…
It’s that wonderful database system where we send all our Entity Framework queries and data just magically appears!  We don’t need to worry about how nasty and convoluted those queries turn out when they finally get parsed down to actual TSQL code.  It was only 3 nice simple lines of LINQ, why should it be any more complex than that?  Oh, and we can write all sorts of abhorrent ad-hoc queries against it too, nobody will even notice.  Let that code build all sorts of IN SELECTs & sub-queries, chains of unreadable JOINs, and Inception-level nested VIEWs, it’s perfectly fine!  Performance, shmerformance!  That’s what we have DBAs for.  Not that they’ll be able to help you when you refuse to refactor or rewrite your code that’s responsible for these abominable queries.

what has been seen cannot be unseen
Your terrible queries are scaring the dog…
SQL Server is so easy!  Microsoft gives me a free Developer Edition, I can code for all these awesome features that I know we’ll have in production, right?  Oh, we only run Standard Edition?  Woops.  Ooh, I know!  Let’s put everything in Azure.  It can do everything and make us breakfast!  Then we won’t need to worry about feature differences, backups, RTO/RPOs, index maintenance, performance tuning, scaling, or even performance in general!  Wait, no?  Oh well, that’s what we have DBAs for!  Besides, it’s so easy to move everything over, we just take our SQL backups and upload them to blob storage and then restore them on Azure SQL Database, right?  No?

this is why we can't have nice things
Exactly.
Hmm.  Maybe we should hire a DBA.  Or four.

That’s all, see ya next time!  XD

What’s in a Name?

a thing by any other name… is a different thing.

Let’s just start with a universal truth:

Names matter.

What do I mean by that? In technology, specifically. Well, let me explain.

The name you choose for a thing – whether it’s a server, a database, an application, a service, a class, a file, a method, a function, a procedure, ad nauseum – that name is immediately and irrevocably in love with Edward associated with that thing. Even if you miraculously get to change that name somewhere down the road, it will still be (at least for a while) “formerly known as” the original name. Legacy teams will continue to use that old name until they’ve convinced everybody the new name is worth the trouble of switching all dependency references & documentation. Managers will continue to use the old name even longer because they’re not close enough to the tech to be aware of, let alone understand, the full implications of said name change.

you-keep-using-that-name
Enigo Montoya prefers easy and descriptive names, like “Six-Fingered-Man”.

So that’s why names matter. They’re a unique and semi-permanent identifier of something; they encapsulate the essence of that thing in a single word or phrase. And they become embedded in the business jargon and technology team lore. Such lofty expectations of such a simple (hopefully), short (usually), and often under-appreciated element of our field.

It’s unbelievably easy to find bad examples of IT naming schemes, too. Just look:

I don’t know about you, but I like my names to be pronounceable. I don’t want to need dozens of syllables and two extra breaths just to say a couple server names in conversation. It’s not hat hard to create meaningful multi-part names that are still phonetic. We speak these things just as much as we type them, let’s make them speak-able!

Thus, once again,

Names matter. REALLY.

And so, dear reader, choose them wisely, with care and consideration. With lots and lots of context. Maybe even some conventions, or at least conventional wisdom. And consistency. Plan for the future, while being aware of the past. Don’t let legacy remnants stand in the way, but don’t let delusions of grandeur force you into a naming scheme that breeds confusion or resentment.

dilbert-namingconventions

Allow me to illustrate. We have a handful of SQL servers as part of our core IT infrastructure. Let’s call them FooDB, BarDB, and CharDB. Now, they each serve a fairly distinct and semi-isolated set of apps/websites. In other words, they each fill a role. (Of course, there are cross-dependencies and integrations, but nothing extreme.) Now, we want to migrate them to new hardware and plan for scale, assuming the business keeps growing. So what do we name the new servers? We know that SQL Server doesn’t really scale horizontally, at least not very easily & not without major application rewrites. But we don’t want to draw ourselves into a corner by assuming that we’ll “never” scale out. Ok, how about this: DC-FooDB-P1, DC-BarDB-P1, etc. The P stands for production, because, along with the fancy new hardware, we’ve got some additional infrastructure room to build a proper Dev & QA environment.

 

dev_test_prod_white
Seriously. You need tiered environments. That’s a whole ‘nother blog post.

So what have we planned for? Tiered environments, as well as a bit of scale-out room – 9 servers in each “role”, so to speak. What if we grew to the point where we needed 10 servers? Well, as a DBA, I’d argue that we should consider some scale-up at that point; but also, to seriously start looking at a broad infrastructure revamp, because that kind of growth should indicate some serious cash influx and a much higher demand on IT-Dev resources.

will-this-scale
Don’t ask me what happened to rules #1-5. I don’t know, I didn’t read the article.

Why should the breaking point be 10 and not 100? or 1000? Well look, it definitely depends on the technology we’re dealing with. SQL server is not Mongo, or Hadoop, or IIS, or Apache. Certain tech was made to scale-out; other tech is more suited for scale-up. And there’s nothing better or worse about either approach – both have their place, and both are important. Scale-out is arguably more important, but for traditional IT shops, it’s also more difficult.

This is where that big C-word comes in. Context. If we’re talking about a tech that scales-out, sure, leave room for 100 or 1000 of those cookie-cutter instances that you can spin-up and shuffle around like cattle. But if it’s a scale-up tech, don’t kid yourself by thinking you’re going to get to the triple-digits.

star-wars-episode-vi-han-solo
Delusions of grandeur… we all have them!

The point is, if you’re starting at 1, it’s not too difficult to scale-out to a few more “nodes” with simple tricks and tweaks; i.e. without drastically changing your IT-Dev practices and environment. But when you start talking multi-digits, of redundant nodes fulfilling the same role, you need to seriously PLAN for that kind of redundancy and scale. Your apps have to be aware of the node topology and be concurrency-safe, meaning “no assumptions allowed” about where they’re running or how many other instances are running simultaneously. And since we’re talking about database servers, that means replication, multi-node identity management, maybe sharding, data warehousing, availability groups, and other enterprise-level features. We’re talkin’ big $$$. Again, it stands to reason that if the business is seeing the kind of growth that leads in this direction, it can afford to invest in the IT-Dev improvements required to support it.

show-me-the-money

I’d love to know your thoughts! Leave me a comment or two. Until next time!

Header image: Keira loved her little pet penguin… and then later she destroyed it and literally ate its face off.

Drafted with StackEdit, finished with WordPress.

Is Coding Style still a thing?

Make it readable, minimize scrolling.. provide context, clarify complexity.

I can hear it now.. the moans and groans, the wailing and gnashing of teeth.. “Another post about coding style / formatting? REALLY!? Why can’t we all just get along read/write/paste code in our own environment and let our IDE/toolset worry about the format?” I hear you, really I do. But you’re missing the point. The single most important trait of good code is readability. Readability should be platform-independent, i.e. it should not vary (too greatly) depending on your viewing medium or environment.

The ratio of time spent reading (code) versus writing is well over 10 to 1 … (therefore) making it easy to read makes it easier to write.

-Robert C. “Uncle Bob” Martin, Clean Code

And yet, all around the web, I continue to find terribly formatted, nigh-unreadable code samples. Specifically, SQL scripts. That’s me; that’s my focus, so that’s what I pay the most attention to. But I have no doubt that the same applies to most other languages that Devs & IT folks find themselves scouring the tubes for on a daily basis. There is just an excessive amount of bad, inconsistent, language-inappropriate formatting out there. And honestly, it’s not that surprising — after all, there are millions of people writing said code — but it’s still a source of annoyance.

Clean code always looks like it was written by someone who cares.

-Michael Feathers, Clean Code

Don’t you care about your fellow programmers?

Maybe it stems from my early days working in a small shop, where we all had to read each other’s code all the time, and we didn’t have these fancy automated toolsets that take the human eyeballs out of so much of the workflow/SDLC. So we agreed upon a simple set of coding style/layout rules that we could all follow, mostly in-line with Visual Studio’s default settings. And even though we occasionally had little tiffs about whether it was smarter to use tabs or spaces for indentation (protip: it’s tabs!), we all agreed it was helpful for doing code reviews and indoctrinating onboarding new team members.

My plea to you, dear reader, is simple. Be conscious of what you write and what you put out there for public consumption. Be aware of the fact that not everybody will have the exact same environment and tooling that you use, and that your code sample needs to be clean & structured enough to be viewed easily in various editors of various sizes. Not only that, but be mindful of your personal quirks & preferences showing through your code.

That last part is really difficult for me. I’m a bit of an anal-retentive bastard a stickler for SQL formatting, particularly when it doesn’t match “my way”. So that’s not the point of this post – I don’t mean to, nor should I ever, try to impose my own personal style nuances on a wide audience. (Sure, on my direct reports, but that’s why we hire underlings, no? To force our doctrine upon them? Oh, my bad…)

 

All I’m saying is, try to match the most widely accepted & prevalent style guidelines for your language, especially if it’s largely enforced by the IDE/environment-of-choice or platform-of-choice for said language. And I’m not talking about syntax-highlighting, color-coded keywords, and that stuff – those are things given to us by the wonderful IDEs we use. Mostly, I mean the layout stuff – your whitespace, your line breaks, your width vs. height ratio, etc.

For example, Visual Studio. We can all agree that this is the de-facto standard for C# coding projects, yes? Probably for other MS-stack-centric languages as well, but this is just an example. And in its default settings, certain style guides are set: curly braces on their own lines, cascading smart-indents, 4-space tab width, etc. Could we not publish C# code samples to StackOverflow that have 7 or 8 spaced tabs, curly braces all over the place (some on the same line as the header code or the closing statement of the block; some 3 lines down and spaced 17 tabs over because “woops, I forgot a closing paren. in a line above, I fixed it but I was too lazy to go down and fix the indentation on the subsequent lines!”).  GRRR!

Always code as if the [person] who ends up maintaining your code will be a violent psychopath who knows where you live.

John Woods

Now, on to SQL. Specifically, TSQL (MS SQL). Okay, I will concede that the de-facto environment, SSMS, does NOT have what we’d call “great” default style/layout settings. It doesn’t even force you to use what little guidelines there are, because it’s not an IDE, so it doesn’t do smart auto-formatting (or what I’ve come to call “auto-format-fixing, in VS) for you. And sure, there are add-ins like RedGate SQL Prompt, SSMSBoost, Poor Man’s T-SQL Formatter, etc. But even they can’t agree on a nice happy compromised set of defaults. So what do we do? Maybe just apply some good old fashioned common sense. (Also, if you’re using such an add-in, tweak it to match your standards – play with those settings, don’t be shy!)

I can’t tell you how frustrating it is to have to horizontally-scroll to read the tail-end of a long SQL CASE expression, only to encounter a list of 10+ literal values in an IN () expression in the WHERE clause, that are broken-out to 1-per-line! Or even worse, a BETWEEN expression where the AND <end-value> is on a brand new line – REALLY?? You couldn’t break-up the convoluted CASE expression, but you had to line-break in the middle of a BETWEEN? Come on.

youre-killin-me-smalls
You’re killin’ me, Smalls!

Or how about the classic JOIN debate? Do we put the right-hand table on the same line as the left-hand table? Do we also put the ON predicates in the same line? Do we sub-indent everything off the first FROM table? Use liberal tabs/spaces so that all the join-predicates line up way over on the right side of the page? Again, common sense.

Make it readable, minimize scrolling, call-out (emphasize) important elements/lines (tables, predicates, functional blocks, control-flow elements, variables). It’s not revolutionary; just stop putting so much crap on one line that in a non-word-wrapped editor, you’re forced to H-scroll for miles. (FYI, I try to turn word-wrap on in most of my editors, then at least if I have some crazy-wide code, I can still read it all.)

SWYMMWYS. Say what you mean, mean what you say. (“Swim wiz”? “Swimmies”? I don’t know, I usually like to phoneticize my acronyms, but this one may prove troublesome.)

swimmies
swimmies!

Modern languages are expressive enough that you can generally make your structure match your intended purpose and workflow. Make it obvious. And if you can’t, comment. Every language worth its salt supports comments of some kind – comment, meaning, a piece of text that is not compiled/run, but rather just displayed to the editor/viewer of the code. Comments need to provide context, convey intent, and clarify complexity.

So what’s my point? Basically, let’s all try to make our code samples a bit easier to read and digest, by using industry-standard/accepted best-practices in terms of layout & style.

Drafted with StackEdit, finished with WordPress.


PS: Just to eat my own words, here’s an example of my preference to how a somewhat complex FROM clause would look, if I was king of the auto-formatter world:

SQL-sample-dark-code
Hipster dark theme FTW!

Send comments, thumb-ups, and hate-mail to my Contact page. 😛

Header image: our husky Keira at 2.5 months, being a sleepy-head!