Press "Enter" to skip to content

Curated SQL Posts

Choosing between Temporary Data Options

Published 2026-08-12 by Kevin Feasel

Chad Callihan builds a list:

Jeff provides half a dozen prompts to get the ball rolling, and I thought I’d discuss my thoughts on “the whole family. #temp vs. table variables vs. CTEs vs. indexed views. When does each one earn a spot?”

Indexed views are an odd man out. As far as table variables go, there are two places where I really like to use them. First is for logging, because they survive a transaction rollback, so even if you roll back your transaction in a catch block, you can still insert that vital error information into a logging table. Second is for table-valued parameters in stored procedures, though that does require a user-defined table type versus an ad hoc table variable.

Leave a Comment

When Columnstore Indexes are Not the Answer

Published 2026-08-12 by Kevin Feasel

Mehdi Ghapanvari explains that columnstore indexes should not be a default:

A SQL Server columnstore index does not improve performance when a query fetches many columns. This is an important factor to consider when choosing between columnar and row-based data storage. In this article, I will set up a demo to show this point.

Yes, it’s obvious if you know how columnstore indexes work. But if you’re new to the topic, it’s a good primer on why we don’t use these things everywhere. But in their wheelhouse, they’re incredibly powerful.

Leave a Comment

Temp Tables and Stored Procedures

Published 2026-08-12 by Kevin Feasel

Shane O’Neill breaks a stored procedure:

Ages ago, I broke one of the stored procedures in Brent Ozar‘s First Responder Kit.

I filed that feat under the heading “Cool; Good to Know“, and then promptly forgot about it. Part of me thinks that I forgot about it because I didn’t understand how I accomplished it. While another part is sure I forgot about it cause “good to know” wasn’t on the list of tasks with looming deadlines.

I’m a man of many parts, but of more deadlines. Now, let’s understand it together!

Click through to see how. Also, when Shane refers to finding Wally, we in North America know him as Waldo. That’s how hard to find he is: he changes his name depending on the continent.

Leave a Comment

Temp Tables as Frenemies

Published 2026-08-12 by Kevin Feasel

Steve Jones shares some thoughts on temporary tables:

My answer to Jeff’s question of temp tables as friend or foe is yes.

They are friends.

They are foes.

Most things that we struggle with in database work are tradeoffs. We have to balance the demands. It’s why we say “it depends” so often because we have to find a way to do more of one thing, while accepting less of another.

This reminds me of a line I heard from a computer science professor I had in school. His line was that computer engineers get miracles every other day—they get some new breakthrough in hardware that makes things faster, cheaper, smaller, etc. But in computer science, we only ever got one miracle: when you want to access a piece of information, the most likely next piece of information you want is near to the piece you accessed. That’s why we can work with memory in pages and how caching can be successful. Everything else in computer science is a trade-off.

Leave a Comment

Accessing Fabric Semantic Model Data in Excel

Published 2026-08-12 by Kevin Feasel

Teo Lachev comes full-circle:

Ask a business user where they want to work with the data, and Excel would probably top the list. Regardless of how hard we try to lure users away from it, Excel remains the endpoint of self-service BI for many organizations. I have clients who have built custom apps or purchased Excel add-ins simply to get data into Excel—or manipulate it once it gets there.

So rather than fighting Excel, let’s look at the options Microsoft provides for bringing governed Power BI data into it. In particular, I’m interested in getting data from Fabric semantic models into Excel.

Click through for five options, including their pros and cons.

Leave a Comment

Deploying Fabric Resources via ADO

Published 2026-08-11 by Kevin Feasel

Jon Lunn continues a series on source control and branching with Microsoft Fabric:

There are a few things you need to check first:

  1. Does the account your going to use to authenticate to (In this case a Service Principal) been added to the workspace with contributor level access?
  2. Has the DevOps Library been updated with the values for the Service Principal and workspace id you are deploying to?
  3. Has any DevOps Environment that is production been gated to to allow deployments only after approval?
  4. Got the list of items to deploy?

You have done all those, then alright! Lets push that button! Flick that switch! Saddle that horse!

Be right back; acquiring horse.

Leave a Comment

The Nuances of Using Temp Tables

Published 2026-08-11 by Kevin Feasel

Rob Farley explains a position:

Let me say for starters that I’m pleased we have temporary tables. They’re tremendously useful, but often get abused. Compared to obvious villains like NOLOCK, cursors, and scalar functions, they’re really quite inane (but any of these features can be used for good – trust me). But temporary tables were the one of these I mentioned in last month’s post about signs of a bad query. If nothing else, using temporary tables probably means you’re writing procedural code rather than set-based queries.

The scenario I want to talk about is using temporary tables to materialise a set of data, ahead of using it in a separate query a moment later. That’s essentially what Jeff Taylor is asking us to comment on.

I agree very strongly with Rob’s position. In stored procedures, temp tables should be there for a specific reason, a resignation that it is a second- or third-best design decision. But sometimes, you need second-best because first-best doesn’t perform well enough.

Leave a Comment

Building an Attack Profile for msdb

Published 2026-08-11 by Kevin Feasel

Fabiano Amorim takes us through a thought process:

SQL Server DBAs routinely rely on automation: SQL Server Agent jobs, maintenance plans, refresh processes, restore scripts, replication cleanup, CDC operations, cross-database modules, and temporary objects. These workflows are often trusted because they’re normal – and that’s precisely why they deserve more attention. 

The goal of this article is not to provide exploit recipes. Instead, it’s to help DBAs answer a practical question: how do I know whether my SQL Server instances are exposed to this kind of risk, what should I monitor, and what should I change when I find a problem? 

Click through for the process.

Leave a Comment