How to use switching to make metadata changes online

Metadata changes, like modifying a clustered index, or many types of column changes, will create locks in SQL Server that will block users from working with that table until the change is completed. In many cases, those locks will extend to the system objects, so you won’t even be able to expand the “Tables” or “Views” nodes in Management Studio.

I want to show you how you can perform those changes using a copy of the table, then instantly switching the table with the copy. The secret is partition switching, and contrary to popular belief, you won’t need Enterprise Edition, or even partitions, to do it.

Continue reading

Prioritizing rows in a union

I just remembered a pretty common data challenge the other day. Suppose you have a number of tables, all with similar information in them. You want to union their contents, but you need to prioritize them, so you want to choose all the rows from table A, then rows from table B that are not included in A, then rows from C that are not included in A or B, and so on.

This is a pretty common use case in data cleansing or data warehousing applications. There are a few different ways to go about this, some more obvious than others.

Continue reading

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

Video: three SQL Server join operators in three minutes

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.

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