Press "Enter" to skip to content

Curated SQL Posts

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.

Comments closed

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.

Comments closed

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.

Comments closed

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.

Comments closed

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.

Comments closed

Thoughts on Temp Tables

Published 2026-08-11 by Kevin Feasel

Andy Brownsword shares an opinion:

For August’s T-SQL Tuesday ask, Jeff has thrown temp tables to the wolves. Does the community like or loathe their usage?

I’ll predict a few ‘it depends’ responses, and I lean towards Jeff’s position that they’re more of the exception than the rule. I’d argue there’s one particular scenario where this exception becomes exceptional (heh).

I imagine that this topic will get a bit of play this month and that it will be a bit more contentious than some might expect. I do like Andy’s scenario of when temp tables become quite valuable.

Comments closed

Dealing with Catch-All Queries

Published 2026-08-11 by Kevin Feasel

Dualcore DBA handles an overly broad class of query:

We have a query that SELECTs from the Users table that takes multiple optional parameters and filters the output based on those.

Whilst fairly easy to read and write, queries written in this way often underperform.

This is a very generous understatement. Click through for two ways to improve the performance of such queries, as well as the pros and cons of each.

Comments closed

Choosing COUNTROWS() over DISTINCTCOUNT() in DAX

Published 2026-08-10 by Kevin Feasel

Phil Seamark provides some guidance:

Counting transactions is one of the most common things a retail model is asked to do. How many transactions did we do in March. How many in New Zealand. How many involved a T-shirt.

The obvious measure is the one everybody writes:

Transactions = DISTINCTCOUNT ( FactTransaction[TransactionID] )


It is correct, and on a large fact table it is one of the most expensive things you can ask the storage engine to do.

Read on for a better alternative, and more importantly, when it’s a better alternative.

Comments closed

Reviewing the SQL Server 2022 STIG

Published 2026-08-10 by Kevin Feasel

Marlon Ribunal reads some guidance:

Security on your SQL Server is important. That doesn’t need any explaining. But where do you start when evaluating the security of your SQL Server? If you are like me, and probably for many DBAs, that’s the hardest part. You know security matters, but without a structured baseline, it’s easy to overlook configuration issues that could expose your environment to unnecessary risk. Starting with a proven checklist gives you a clear way to identify gaps before they become problems.

And how do you even implement the principle of least privilege on the instance and database level?

Read on to see what the US Department of Defense recommends. Also, I like pointing out Tracy Boggiano’s dbachecks updates that include CIS security auditing, though that is a few years old at this point so I’m not 100% sure how well it works.

Comments closed