Press "Enter" to skip to content

Category: T-SQL Tuesday

Production Code by Audience

Published 2022-11-14 by Kevin Feasel

Kenneth Fisher thinks about the audience:

I’ll be honest, all of that is pretty variable. It depends. There are lots of different types of code used in production.

For example, if your code is something you’ll be using yourself, you may not care about how robust it is. I mean you need to make sure it won’t do any harm if it fails, either through restartable steps, rolling back transactions, etc. But if it fails you’re right there to fix whatever went wrong and move on. You can almost think of it as a set of notes on how to do a process. Maintenance is also less of an issue because no one else is likely to be looking at it but you. You still probably want some documentation in case it’s been a while since you used the code and you’ve forgotten how that tricky bit works. And performance? Well, how patient are you? I’m not overly patient so performance is pretty important for code I’m running manually.

Read on for Kenneth’s take but also check out Jeff Moden’s response in the comments for the contrary view.

Comments closed

Defining Production-Grade Code

Published 2022-11-11 by Kevin Feasel

Deb Melkin puzzles on a topic:

To me, the most obvious part is that code going out into production can pass code review and testing. You don’t want to send out code that has obvious bugs – like having incorrect joins or inaccurate calculations or poor query plans, etc. Code should also go through reviews, not just to make sure the obvious bugs are there but other basic development standards, like naming conventions and proper commenting, have been followed.

There’s also an interesting bit about proofs of concept that I wanted to expand upon a little. The terms “pilot” and “proof of concept” sound interchangeable (and people switch the terms around quite often) but mean rather different things. A proof of concept is an attempt to see if some concept actually works: for example, can I have my .NET-based web app send 5,000 messages per second to an Azure Event Hub without things falling apart? Proofs of concept are intended to be quick-and-dirty code which do not take into account security, robustness, or code quality. This is throw-away code and should never go into production.

By contrast, a pilot is intended to be production-ready but serves as a scaled-down version of a product. A pilot data warehouse might only include data for one department in the organization but the design accommodates future growth and the people developing it do everything they would do to make the warehouse robust and production-ready.

The really tricky part is that companies will often ask for a proof of concept and then treat it like a pilot, which is where they go wrong and end up spending years on awful maintenance of an awful product.

Comments closed

A Checklist for Code Quality

Published 2022-11-11 by Kevin Feasel

Deepthi Goguri has a list and is checking it twice:

3. Check for the execution times based on the DEV, TEST data. I have seen scenarios where the data size in the DEV and TEST environments are not same as production data. This can cause the difference in the execution times from lower environments to production. It is always suggested to test your code in lower environments having same amount of data as production.

Read on for the full list.

Comments closed

Commenting on Actions in Production

Published 2022-11-11 by Kevin Feasel

Andy Leonard wants to know whodunnit:

While “Commands completed successfully” is helpful to know – and definitely better than an error message – it does not inform the person executing the command precisely what just happened. Rather, “Commands completed successfully” informs the user that “something happened,” and as far as SQL Server Management Studio is concerned, whatever it was succeeded.

I prefer more details.

Andy also casually drops the concept of idempotence, which I define as saying that the end state is the same no matter how many times you run a specific process. My phrasing of the term is a little different though we get to the same point. Which is itself kind of an explanation of idempotence if you squint a little bit.

Comments closed

Defining Production-Grade Code

Published 2022-11-09 by Kevin Feasel

Chad Callihan takes a detour:

You understand what you’re thinking when you write code but will you remember it in a few weeks or months? What about the new associate that has to work with your code? Will they be able to decipher what you were thinking based on code alone?

If code isn’t documented, it can make work unnecessarily difficult. For production-grade code, I would expect it to be well documented so even someone with minimal knowledge can get an idea of what the code is doing. It’s not necessary to write a novel for documentation but having something is better than nothing.

Read on for more thoughts of what makes code production-grade.

Comments closed

Qualities of Production-Grade Code

Published 2022-11-09 by Kevin Feasel

Aaron Bertrand pulls out the list:

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:

Read on for those caveats and what Aaron considers to be the hallmarks of high-quality code.

Comments closed

Production-Grade Code

Published 2022-11-08 by Kevin Feasel

Rob Farley has seen some things:

I’m a consultant, so I see quite a lot of organisation’s “production code”. The quality they have in common is that it’s code that’s in production. That’s pretty much it.

I know in the days when I used to code JavaScript occasionally, there was the concept of “minifying” the code, which made a tighter version of it all, so that it would load better. This was code that was good for production. Hopefully most of the tests were happening on the minified code, because if that’s what was going into production, then you’d want to be sure that it worked, even if the pre-minification code worked fine. If you deployed code that wasn’t minified, then that wasn’t right.

But SQL doesn’t really have that concept.

Rob hits on the fundamental subjectivity of the concept and it’s worth a read.

Comments closed

Code is for Humans

Published 2022-10-14 by Kevin Feasel

Shane O’Neill covers an important topic:

There are a myriad number of uses for Dynamic SQL – I’ve already read some of the published posts and I’m impressed with the range. (Yeah, I’m writing this late; I’m aware).

I’m aiming for something different. I want to talk about the things I believe Dynamic SQL should have. You can disagree with me if you’d like; I’d welcome it (as long as you can justify it) but here are my thoughts on writing Dynamic SQL.

Improving the readability of code is probably the most important important things we could do at the margin. There are certainly trade-offs here: some patterns of code can be significantly more efficient at the cost of a minor readability loss and, if you need that to reach some expected performance threshold, fine. But ceteris paribus, the more human-readable code is the better code.

Comments closed

Dynamic SQL and String Data Types

Published 2022-10-13 by Kevin Feasel

Erik Darling theorizes about strings:

If you write the good kind of dynamic SQL, that is:

1. Parameterized

2. Executed with sp_executesql

You’ll probably have run into some silly-ish errors in the practice. Namely, that sp_executesql expects your SQL string and your Parameter string to be NVARCHAR(…).

Read on for some thoughts on data types, max-ness, and the like.

Comments closed