Category: T-SQL Tuesday

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.

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.

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.

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.

Steve Jones checks the list:

It’s time to look back at the 155th blog party. I was the host this month, asking about Dynamic SQL. I got quite a few responses, which I’ve gone through and summarized below. If I’ve missed someone, please ping me.

Read on for a variety of thoughts on the topic.

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.