Press "Enter" to skip to content

Author: Kevin Feasel

The Yin and Yang of Temp Tables

Published 2026-08-13 by Kevin Feasel

Deborah Melkin covers the gamut:

My stance is that temp tables are a useful tool when properly used, but they’re very easy to misuse to the point of creating problems. Table variables are even more misused for similar reasons; that could be a whole other blog post that almost starts and ends with “they’re not variables stored in memory but just regular temp tables without stats.”

Click through for examples in which temp tables have been quite helpful, and cases in which temp tables have been actively harmful.

Comments closed

Deletion and Ghost Cleanup in SQL Server

Published 2026-08-13 by Kevin Feasel

Martyn Jones hunts down the ghosts:

The concepts discussed in the previous blog post can also be seen in the transaction log. The logical removal of rows (by marking them as ghost records), the associated allocation metadata updates, and the eventual physical removal of those rows by the ghost cleanup process are recorded as individual transaction log operations.

The demo code uses the undocumented function fn_dblog(), this provides a clear view of the internal sequence of events that SQL Server performs during a delete operation so the individual physical changes required to implement ghosting, and later, cleanup can be studied.

Click though to see how SQL Server marks ghost records, tracks where they are, and performs cleanup.

Comments closed

Comparing Temp Tables to Table Variables

Published 2026-08-13 by Kevin Feasel

Marlon Ribunal compares two techniques:

For this post, I needed to do some research because there is more to #temp tables than simply creating one and using it. As I started digging into the topic, I realized there are a lot of discussions around #temp tables and @table variables, especially around when one might be a better choice over the other.

One of the common arguments is that #temp tables go to disk while @table variables stay in memory. Another argument is that this is a myth. That got me curious about what the actual differences are and when they really matter. Discussions like this almost always starts with where the data is stored (disk vs memory).

Yeah, the “table variables are just in memory” is a myth, at least for normal table variables. Memory-valued user-defined table types do change things, as they don’t use tempdb. But they come with their own set of handcuffs.

Comments closed

Digging into Postgres Plan Caching

Published 2026-08-13 by Kevin Feasel

Jordan Boich runs an experiment:

In my last post, I talked about how I was amazed that PostgreSQL doesn’t have a shared plan cache like SQL Server does. I wanted to create an experiment / lab where I could see this in action, so I built one. I’m going to be leveraging a free PostgreSQL database hosted on an Azure Flexible Server. I’ll also be using DBeaver to connect to my PG Database.

Click through to see what Jordan learned.

Comments closed

Assessing SQL Server Security across an Estate

Published 2026-08-13 by Kevin Feasel

Andreas Wolter scales things up:

Get-SqlSafe is a free SQL Server security assessment that checks for common configuration, access-control, authentication, auditing and generates a detailed HTML report.

It has been publicly available for a couple of months and has already received several valuable additions based on user feedback, including console-only mode, Amazon RDS for SQL Server support, and per-database reports.

It has been encouraging to see Get-SqlSafe being used in real environments, including some very large ones.

In this post, I will show how to run the assessment against dozens or even hundreds of SQL Server instances using only a small additional PowerShell wrapper.

Click through for that wrapper.

Comments closed

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.

Comments closed

From CTE to Temp Table

Published 2026-08-12 by Kevin Feasel

Brent Ozar describes a pattern I often use:

Common Table Expressions are awesome because they let SQL Server reorder processing in whatever way it deems to be the most efficient for your current data distribution, on your current version of SQL Server. Default to CTEs.

When SQL Server gets that process wrong, switch to temp tables.

Read on for an example.

Comments closed

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.

Comments closed

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.

Comments closed

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