Not giving a shit about performance is tech-debt

For practically every piece of code you develop, there will be trade-offs. Sometimes, you can combine the best of two worlds, other times it comes down to some hard choices. For T-SQL developers, it typically boils down to a few key questions:

  • How much time can you spend perfecting code instead of just shipping?
  • Can we just fix it when it becomes a problem?
  • Is buying more hardware cheaper than paying for developers to tune their code?
  • Is better code harder to read, and will a junior developer be able to work with it?

Continue reading

Human-readable ranges of integers or dates

This is a real-world problem that I came across the other day. In a reporting scenario, I wanted to output a number of values in an easy, human-readable way for a report. But just making a long, comma-separated string of numbers doesn’t really make it very readable. This is particularly true when there are hundreds of values.

So here’s a powerful pattern to solve that task.

Continue reading

In an attempt to try a different approach, here’s a three-minute video explanation of how the different physical join operators in SQL Server work and why you would choose one over the other.

More reading

I’ve written a few blog posts on join operators befores, so if this video wet your appetite, here’s some recommended reading:

I’d love to hear what you think of the short video format! Please leave feedback in the comments below or on Twitter.

Video: three SQL Server join operators in three minutes

My brand-new T-SQL reference app for Slack

If you haven’t heard of Slack, it’s a wonderful team messaging platform. At first glance, it looks a bit like a private Twitter where you can set up “channels” to have conversations with colleagues. But the great thing with Slack is its flexible API and all the marvelous ways in which you can extend its functionality. You can send rich text messages with status updates from your production servers or you can interface with popular web services like Trello, right there in the chat window!

So as a way to kick start an effort at learning node.js and re-discover web development (which I haven’t really done in about 15 years now), I set out to build a Slack API. Here’s what I learned.

Continue reading

Comparing nullable columns

Do you ever compare the values of a lot of columns in two tables? Sure you do. Like, for instance, in a cross update, when you need to figure out which rows you should actually update. But it gets worse if the columns are nullable. The fact that any value could potentially be NULL vastly complicates the comparison and might wreak havoc not only on your code but also on your query performance.

But there’s hope.

Continue reading

Key Lookup without an output column?

Performance tuning the other day, I was stumped by a query plan I was looking at. Even though I had constructed a covering index, I was still getting a Key Lookup operator in my query plan. What I usually do when that happens is to check the operator’s properties to see what its output columns are, so I can include those columns in my covering index.

Here’s the interesting thing: there weren’t any output columns. What happened?

Continue reading

Selectively disable “Include actual execution plan”

The “include actual execution plan” feature in SQL Server Management Studio is an invaluable tool for performance tuning. It returns the actual execution plan used for each statement, including actual row counts, tempdb spills and a lot of other information you need to do performance tuning.

But sometimes you want to run a series of statements or procedures where you only want the execution plan for some of the statements. Here’s how:

Continue reading