Press "Enter" to skip to content

Author: Kevin Feasel

Comparing Memory-Optimized Table Variables to Temp Tables

Published 2025-08-19 by Kevin Feasel

Mehdi Ghapanvari runs a test:

SQL Server memory optimized table variable and a temporary table: Which is faster to temporarily load and store data?

Dear readers, I want to share a true story. In 2018, when I was using SQL Server 2016 in production, I had a complex stored procedure where I needed to store data temporarily. I can’t remember exactly, but I needed to store, say, 50,000 rows temporarily. At that time, I thought memory-optimized table variables were extremely fast for storing temporary data. So, I used them. There was not a lot of concurrency on that stored procedure. However, after a while, I realized that it was not fast enough. I investigated to find the root of the issue and found that loading data into the Memory-Optimized table variable was causing the problem.

Here is where I say that the specific circumstances are going to play a large role in this. If the number of rows is smaller, that can change the outcome. If the action you are performing with the temporary object is more complex, that can change the outcome (generally in favor of temp tables). If you have very high concurrency, with hundreds of users trying to access variants of this object at all times throughout the day, that can change the outcome (generally in favor of the memory-optimized table type).

If you try to pin me down on specific advice, I’d say to start with temp tables and only switch to memory-optimized table types after you have proof of table creation (or re-use) contention in tempdb tied to that specific object. But unlike most situations with In-Memory OLTP, it’s reasonably straightforward to find a great use case for memory-optimized table types.

Comments closed

All-Caps and Technical Writing

Published 2025-08-18 by Kevin Feasel

Mike Robbins argues against over-capitalization:

In technical writing, letter case affects more than style. It influences tone, readability, and accessibility. Typing in ALL CAPITAL LETTERS might seem like an easy way to add emphasis or style, but it often does more harm than good, affecting how quickly readers process information and how your message is perceived.

If you want your message to be clear, approachable, and accessible, avoiding all caps is usually the best approach.

Read on for the reasons why.

Comments closed

The Value of a No-Op Startup Step in a SQL Agent Job

Published 2025-08-18 by Kevin Feasel

Steve Stedman lays out the reasoning:

When managing SQL Server, SQL Server Agent Jobs are indispensable for automating tasks like backups, index maintenance, and data imports. However, monitoring these jobs can sometimes be tricky, especially when trying to quickly assess their status. One simple yet effective trick to improve visibility is adding a no-op (no operation) or logging step as the first step in your SQL Server Agent Jobs. This blog post explains why this small addition can make a big difference in monitoring and managing your jobs.

Click through for Steve’s argument.

Comments closed

Business Rule Automation in SQL Server Triggers

Published 2025-08-18 by Kevin Feasel

Kenneth Omorodion uses triggers:

The advantage of using triggers is that the same processing can occur regardless of where or how the data has been inserted, updated or deleted. In this article, we look at several examples of where and why triggers could be useful along with an example use case.

I’m not a huge fan of using triggers for handling business rules for a few reasons. The biggest one is that triggers get lost in the mix too easily—it’s not easy to see that a trigger exists and that it is the thing modifying data. This is probably one of the better setups I’ve seen for triggers, as it does include an audit log table and the triggers assume multiple rows rather than expecting a single row will come in.

Comments closed

An Introduction to Query Folding in Power BI

Published 2025-08-18 by Kevin Feasel

Alex Powers takes us through one major performance optimization technique in Power BI:

One of the most powerful capabilities of Power Query and the M Language is Query Folding (also referred to as query delegation, and predicate push-down). Query Folding allows the Power Query Mashup Engine to push the transformations expressed in an M (mashup) query to the data source, in the data source’s query language, resulting in more efficient data processing.

For inexperienced database technology users this ability to leverage the graphical user interface of Power Query to dynamically generate a query written in the data source’s query language unlocks enormous opportunities to find insights with any data, at any scale.

Click through to see how you can know if query folding is enabled, as well as some hints around when and to what extent query folding will work.

Comments closed

The Importance of Running DBCC CHECKDB

Published 2025-08-18 by Kevin Feasel

Kevin Hill has some advice:

You just ran DBCC CHECKDB for the first time in a while (or maybe ever) and saw something you didn’t expect: the word corruption.

Take a breath.

Don’t detach the database.
Don’t run REPAIR_ALLOW_DATA_LOSS.
Don’t reboot the server or start restoring things just yet.

There’s a lot of bad advice floating around from old blogs, well-meaning forum posts, and even some popular current LinkedIn threads. Some of it might’ve been okay 15 years ago. Some of it is dangerous.

Let’s dig in.

Click through to dig in.

Comments closed

SSMS 21 and the Visual Studio Installer

Published 2025-08-15 by Kevin Feasel

Randolph West clears up a few misconceptions about SQL Server Management Studio 21’s installer:

There’s been some confusion lately about the SQL Server Management Studio (SSMS) installation process for version 21.

Before Microsoft released SSMS 21, you had to download an installer file for each release (ever since SSMS was released as a standalone product with v16). This file eventually grew to be over 1GB, which is a significant download, especially in the case of a minor update.

With SSMS 21, Microsoft changed SSMS to use the Visual Studio installer. 

Read on to learn more about the ramifications of this decision. And there is still a way to install SSMS 21 in a secure environment without outbound internet access, although it does require a few more steps in addition to “download executable and then deploy executable to other machines.”

Comments closed

Securing PostgreSQL Containers

Published 2025-08-15 by Kevin Feasel

Mercy Bassey grabs a certificate:

Running Postgres in Docker is great for a quick test but what if you want it to behave like a proper, production-style setup with SSL encryption, certificate-based authentication, persistent volumes, and custom configurations? In this article, we’ll find out how, tackling the various tasks involved such as:

  • Generating and using self-signed SSL certificates with Postgres.
  • Setting up a PostgreSQL Docker container that uses those certs for encrypted client connections.
  • Configuring authentication for both automated services and human users.
  • Controling the behavior of your Postgres instance using mounted config files.

Read on to see how.

Comments closed