Coding standards or values?

As a consultant, I see code written in plenty of different organisations. Some of them have rules about their code, and many don’t. I have to work within their standards, rather than my own. This has made me quite flexible in what I tolerate. And as such, I have found that I’m most interested in what you value, rather than what rules you adhere to.

I mean, I can spot coding styles that I don’t like, but it’s rarely related to how it’s formatted, or whether they use COUNT(*) or COUNT(1) and so on. Mala Mahadevan (@sqlmal) has prompted a T-SQL Tuesday event about coding standards, so let me explain this ‘values v standards’ thing.

As far as T-SQL is concerned, I’m more interested in how their execution plans look than their T-SQL code, or whether there are potential errors in their code as a result of type conversions, or ambiguity because aliases aren’t using effectively. These kinds of issues are rarely solved by insisting on coding standards, but rather by making sure that the people writing the queries understand the impacts of their choices. I value these things a lot more than which line the ON clause appears on or whether someone prefers to write DELETE FROM or just DELETE.

On this last point, I remember a situation where someone wouldn’t approve code for production if it used the pattern:

You know – with two FROM clauses. The one that defines where the rows that are being deleted are, and the one that defines the other objects involved in the selection of said rows.

The issue wasn’t the fact that the word “FROM” appeared twice – that could be solved by simply leaving out the first one, which is really only there for the sake of readability (I don’t think I ever write ‘DELETE FROM’ these days, nor ‘INSERT INTO’). The issue was about the internationally recognised ANSI-SQL standards that said it wasn’t part of the pattern. T-SQL allowed it, but ANSI-SQL didn’t.

Following this standard for DELETEs makes life harder. But if it’s a client’s standard, then I’ll follow it. If I need to create a temporary table with the primary key values that I need to delete, then fine. Then I can follow the pattern of:

My personal preference for DELETE statements (and also UPDATEs) is to always use a FROM clause, and to use an alias for the table (or view or function or subquery) upon which the operation is to apply. Like this:

To me, this feels a lot more natural. I can start with a SELECT query, and without ambiguity (such as when a table is listed multiple times in the FROM clause), indicate which one is the target for my operation.

Naturally there are plenty of recommendations that good people make about writing code. Aaron Bertrand (@aaronbertrand) has plenty in his Bad Habits series. I don’t agree with all of them, but it’s good to be aware of what people recommend, because I definitely agree with most of them.

But the only ones that I really value are the ones that have potential logic issues such as using BETWEEN with datetime values when the intention isn’t inclusive at both ends, or ones where there are performance implications, such as non-sargable predicates. But I’ve been known to deliberately break sargability to discourage certain execution plan shapes (combined with a comment explaining why), because even my own standards should not be enforced so rigidly that they are a problem.

Coding standards are not a bad thing. But I think values are better. Valuing readable code, valuing comments that explain the reasons for coding decisions, valuing patterns that avoid problems, can raise the quality of the coding culture.

I mentioned earlier that I’m a consultant. We provide consulting services at LobsterPot Solutions. That often involves working with other coders who work with our customers (sometimes their employees, sometimes from third parties), and one of the most significant things we do is to try develop a healthy culture around data. If people within the organisation don’t believe the numbers in a report, or if it takes a long time to get information, or if people are feeling forced into maintaining their own local copies of ‘the truth’… these types of situations tell me that the data systems are not of a high-enough standard and are therefore not valued.

So I say that we should raise our standards. We should behave in ways that demonstrate what we value, and that includes how we write our code, how we treat our data systems, and especially how we treat each other. Rules can help us develop habits around these things, but we need to be teaching the values, not just the rules.

@rob_farley@twitter