Press "Enter" to skip to content

Category: T-SQL Tuesday

Defending (Certain) Bad Practices

Published 2023-01-13 by Kevin Feasel

Aaron Bertrand considers the trade-offs:

For the first T-SQL Tuesday in 2023, Raul Gonzalez invites us to talk about cases where we have knowingly implemented worst practices.

Well, I have done it a lot. Most of the posts in my bad habits series are cautionary tales based on my own “learning the hard way.” There are always trade-offs with doing something correctly – maybe proper design is less efficient, or takes longer to write, or has to pass more checks. Over time, though, you start getting a feel for where it makes sense to cut these corners, and where it doesn’t.

Read on for some practical examples around Stack Overflow.

Comments closed

Replication from 2000 to 2012

Published 2023-01-12 by Kevin Feasel

Deepthi Gogrui pulls a fast one:

The scenario that I faced was little challenging. We had SQL Server 2012 production server replicating data to a Server 2000 which is used for reporting purposes. Subscriber SQL Server 2000 used by the reporting team were not ready to upgrade the Server as they need to rewrite their entire application as it was using vb6 code. They need a strategy where the data can still be replicated without upgrading the Server.

As I researched, I found that it is not compatible version but planned to test the replication to see if somehow it works. I tested the replication between SQL Server 2012 as a publisher and SQL Server 2000 as subscriber. I was able to setup the transactional replication between the servers for the database but found during the initial initialization snapshot, the ANSI_PADDING setting in the snapshot generated .sch files caused the issue while the distribution job runs. 

Read on for the solution. This turned out to work despite Microsoft’s official guidance that they only support replication between SQL Server instances within two versions of each other.

Comments closed

Backing up SQL Server with Multiple Solutions

Published 2023-01-12 by Kevin Feasel

Chad Callihan tells us a story:

Many years ago, a friend was using a backup/recovery tool for managing their backups. This particular tool on its own wasn’t necessarily bad. But in this case, it didn’t work well. In fact, it barely worked at all. Backups were slow to complete and restores were even slower. Attempting to restore even one database could take 10-15 minutes just to navigate a GUI and start the restore process.

It…was…very…slow…

Read on for the rest of the story. Most of the time, when I see two products used for backups, I typically see a bunch of redundant backups, with both products taking full backups.

Comments closed

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