Why We Use a Permanent Table for Always Encrypted Columns

By /

Today, a customer asked me why we use a permanent table when calling the BulkUpdate method with our Entity Framework Extensions or Dapper Plus libraries.

So, the first thing we asked was: does your destination table have an encrypted column?
The answer was: YES.

So let’s see in this article how our library behaves when a table contains an Always Encrypted column.

What Is an Always Encrypted Column?

An Always Encrypted column is a special type of column in SQL Server where the data is encrypted on the client side and never stored in plain text on the server.
Only applications with the right encryption key can read or write the real values, which makes it perfect for protecting sensitive data like credit card numbers or SSNs—even from DBAs or server admins.

Creating an Always Encrypted Column

Let’s start by creating a table that contains an Always Encrypted column.

If you want a full walkthrough, you can check out this Tutorial: Getting started with Always Encrypted.

For now, here’s a simple SQL script so you can see what it looks like:

CREATE TABLE [dbo].[TableWithEncryptedColumn](
    [ID] [int] NULL,
      COLLATE Latin1_General_BIN2 
        ENCRYPTED WITH (
            COLUMN_ENCRYPTION_KEY = [CEK_Auto1], 
            ENCRYPTION_TYPE = Deterministic, 
            ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
        ) NULL
) ON [PRIMARY]

Why TempDB Doesn’t Work

The most efficient way to update millions of entities in a database using C# is by leveraging SqlBulkCopy.

The idea is simple:
Insert your data into a staging table, then use that table to update your destination table.

Let’s try to create a temporary staging table:

CREATE TABLE #StagingTable
(
    [ID] [int] NULL,
      COLLATE Latin1_General_BIN2 
        ENCRYPTED WITH (
            COLUMN_ENCRYPTION_KEY = [CEK_Auto1], 
            ENCRYPTION_TYPE = Deterministic, 
            ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
        ) NULL
) ON [PRIMARY]

❌ That doesn’t work — you’ll get this error:
Cannot find the column encryption key “CEK_Auto1” because it does not exist or you do not have permissions.

Let’s try copying the table definition instead:

SELECT * INTO #StagingTable FROM TableWithEncryptedColumn

❌ That fails too:
Cannot create table ‘#StagingTable’ since it references a column encryption key from a different database.

So that’s why by default, we don’t use a temporary table when your table has an Always Encrypted column.
In 99.9% of cases, it just doesn’t work.

Still, if you really want to try, you can force the behavior by setting the option UseTempDbForColumnEncrypted = true.

Why We Use a Permanent Table

So what’s the only reliable option left?
You guessed it — using a permanent table.

We normally try to avoid that, but in this case, there’s no other choice.

Here’s what happens behind the scenes:

  1. A permanent staging table is created:
CREATE TABLE StagingTable_Guid
(
    [ID] [int] NULL,
      COLLATE Latin1_General_BIN2 
        ENCRYPTED WITH (
            COLUMN_ENCRYPTION_KEY = [CEK_Auto1], 
            ENCRYPTION_TYPE = Deterministic, 
            ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
        ) NULL
) ON [PRIMARY]
  1. This table is used to perform the UPDATE operation.
  2. Finally, the table is dropped:
DROP TABLE StagingTable_Guid

So yes — we create a permanent table, use it, and drop it as soon as possible.

If you see a leftover table in your database, it might be because the application crashed before the cleanup.
In that case, it’s safe to drop the table — unless you’re using this option:

options.TemporaryTablePersist = true

This explicitly tells the library not to drop the table automatically.

Conclusion

In this article, we saw why our libraries sometimes use a permanent table.
And the reason is pretty simple — we don’t have a choice. Temporary tables just don’t work in most cases with Always Encrypted columns.

Scroll to Top