It's hard to come up with a precise definition of what production code looks like. As with a lot of things, "I know it when I see it," but that isn't a very useful thing to share. This month, Tom Zika invites us to talk about production code for T-SQL Tuesday #156. Specifically, Tom asked:

Which quality makes code "production grade"?

In a lot of programming languages, efficiency is almost always the guidepost. Sometimes, minimizing character count or line count is a "fool's gold" measure of the quality of the code itself. Other times, unfortunately, engineers are judged not by quality at all, but rather the sheer number of lines of code produced – where more is, somehow, obviously better. Over my career, "how much code there is" has never been a very meaningful measure in any language.

But I'm here to talk about T-SQL, where certainly efficiency is a good thing to measure – though there are some caveats to that:

• What efficiency means can differ – does it run fast? does it use a minimal amount of memory? does it cause the least I/O? does it take advantage of parallelism? does it minimize logging? does it allow for high concurrency? Speed is often important, but not always – each of these might be the most important measure for any given query.
• Does it do what's expected? – of course code that makes it to production should do what it's supposed to. This is really only an issue where you develop in production.
• Efficiency and accuracy aren't the only thing to measure – for me, production-grade code must be readable and self-documenting, and I can share many examples (and counter-examples) that can make it so (or not). Styles can vary so don't use my "better" example as what you have to do; just take a subjective look at the differences between these 5 examples of "bad" and "better."

Is it smart about white space?

SELECT a.OrderID, ProductID FROM OrderDetails a INNER JOIN Orders b ON a.OrderID = b.OrderID WHERE OrderDate >= '2022-01-01' AND Quantity >= 32

SELECT 
  o.OrderID, 
  od.ProductID 
FROM dbo.OrderDetails AS od 
INNER JOIN dbo.Orders AS o 
  ON od.OrderID = od.OrderID 
WHERE o.OrderDate >= '20220101' 
  AND od.Quantity >= 32;

SELECT 
  a.OrderID, 
  ProductID 
FROM dbo.OrderDetails AS a
INNER JOIN dbo.Orders AS b 
  ON a.OrderID = b.OrderID 
WHERE a.Quantity > 32;

SELECT 
  o.OrderID, 
  od.ProductID 
FROM dbo.OrderDetails AS od
INNER JOIN dbo.Orders AS o
  ON od.OrderID = o.OrderID 
WHERE Quantity > 32;

SET LANGUAGE British;
GO 
 
-- fails:
DECLARE @d datetime = '1999-05-13';
SELECT DATEPART(MONTH, @d);
GO 
 
-- wrong month:
DECLARE @d datetime = '1999-05-09';
SELECT DATEPART(MONTH, @d);
GO

SET LANGUAGE British;
GO 
 
/* succeeds: */
DECLARE @d datetime = '19990513';
SELECT DATEPART(MONTH, @d);
GO 
 
/* succeeds: */
DECLARE @d datetime = '19990509';
SELECT DATEPART(MONTH, @d);
GO

SELECT DATEPART(Y, '20221108'), 
       DATEPART(W, '20221108');

SELECT DATEPART(YEAR, '20221108'), 
       DATEPART(WEEK, '20221108');

Is it cute or clever for the sake of it?

I see PARSENAME – a handy function against actual metadata and IP addresses, for sure – misused all the time. People use it to split strings in other forms as well, by replacing some delimiter with . first. It is dangerous to recommend without disclaimers about the length supported; as soon as one of the elements exceeds 128 characters, PARSENAME returns NULL. I'm guilty of shoehorning PARSENAME where it doesn't belong to be clever, but it's actually only useful in a very limited number of cases, and wouldn't put it in production without comments/disclaimers.

Basically, if you have to explain a piece of T-SQL to someone who isn't brand new to T-SQL, it's probably too cute, too clever, or too complicated. If it's complicated out of necessity – or you're doing something unpopular, like starting a CTE with a statement terminator – comments are your friend.

Conclusion

These are just a few of the kinds of things I consider during code review before letting T-SQL head to production. I have plenty more of these that, admittedly, not everyone agrees with; for further reading, see my Bad Habits index.