Press "Enter" to skip to content

Category: T-SQL Tuesday

Defending Less-than-Ideal Practices

Published 2023-01-12 by Kevin Feasel

Deborah Melkin has a confession and a defense:

I feel like this is where I should say something like, “Hi, my name is Deborah and I’ve used nolock in production.” I would also have to confess to doing things like using correlated sub queries, not using a foreign key, implemented complicated triggers, etc. I often talk about how the first real SQL script I wrote had cursors running over temp tables in SQL Server 6.5, which I’m fairly certain was one of the first thing I read you were NOT supposed to do. And oh, hello there, denomalized table and dynamic SQL! I’m sure I’ve done more things than this too. These are just the ones I can remember doing, or at least I’m willing to admit in public.

With some of these, the answer is “that’s the best alternative I had at the time.” With correlated sub-queries, I wouldn’t even consider that a bad thing. Granted, I personally prefer a combination of common table expressions and the APPLY operator but that’s usually not for performance reasons.

Comments closed

Against the Grain: Heaps, Clustered Indexes, and Fragmentation

Published 2023-01-10 by Kevin Feasel

Rob Farley cautions against rash decisions on three topics:

We’ve already mentioned how clustered indexes can help avoid some of the problems with heaps, but I’m rarely in favour of the standard clustered index key choice – that of an ever-increasing identity column. The arguments people make for this are typically around avoiding page-splits, and the smallness of the data type. But I see tables where the clustered index is on a unique ID column and nothing ever refers to it.

Read on for a dose of caution on three popular beliefs.

Comments closed

When Shrinking Makes Sense

Published 2023-01-10 by Kevin Feasel

Eitan Blumin reminds us that database shrinking isn’t always bad:

Shrinking databases and database files in SQL Server is a widely known “worst practice”.

Usually, it’s because it’s assumed that the database files are expected to auto-grow again after the shrink.
So, in truth, it’s not the shrink itself that’s the problem… It’s the auto-growth!

But… What if you DON’T expect the database file to auto-grow back to what it was before?
For example, what if you truncated/deleted/migrated/archived huge chunks of data from your database, which you don’t expect to be returned later? Or what if you performed a massive data compression or migrated to clustered columnstore indexes, which reduced your data size significantly?

Read on for more thoughts along these lines, problems you might run into, and scripts to help you along the way.

Comments closed

Properties of Production-Grade Code

Published 2022-11-14 by Kevin Feasel

David Wiseman has a list:

Security

Production code needs to be secure, following industry best practices. A security incident can cause severe reputational damage and financial impact.

Maintainability

Production code should be maintainable. Avoiding duplication and striving for simplicity.

Read on for a list of properties which production-grade code should have.

Comments closed

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

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

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