SQL: Tempdb, Temporary Tables, and Collation: A Design Trap You Can Avoid

SQL: Tempdb, Temporary Tables, and Collation: A Design Trap You Can Avoid

One of the more frustrating classes of SQL Server application failures is also one of the most avoidable: collation conflicts involving tempdb.

Background

I see these issues all the time. Developers create applications that require specific collations at the server level, because they don’t handle temporary tables (and by extension) tempdb well.

All this can be avoided, and you can easily build applications that will work with temporary tables, without worrying about the server (and tempdb) collation.

These issues tend to surface late—during deployment, upgrades, or migrations - because the application worked perfectly in development and test. Then it suddenly fails with errors such as:

Cannot resolve the collation conflict between … in the equal to operation.

What makes this especially painful is that the failure is not caused by a limitation in SQL Server. It is almost always a design decision in the application code.

Why tempdb is different and why this happens

Let’s look at why this happens, how tempdb behaves, and what applications should be doing to avoid collation-related failures entirely.

The key fact that often gets overlooked is this:

tempdb always uses the SQL Server instance collation, not the user database collation.

Because tempdb is loosely based on the model database, I’ve seen people restore a model database with a different collation, to force tempdb collation to be different to the server collation. Don’t do this. There are so many things that could go wrong with this. Instead, learn to build applications that play nicely with servers with different collations.

Which objects are affected?

There are a number of objects that are affected:

  • Temporary tables (#temp_tables)
  • Intermediate objects created by the optimizer
  • Work tables used by features such as sorting, hashing, and spooling …all inherit their collation from tempdb but the one that leads to the main issues is temporary tables.

If your user database collation is different from the server collation, then any string comparison between:

  • A column from your database, and
  • A column from a temporary table may fail unless SQL Server can implicitly resolve the difference.

Sometimes it can. Sometimes it can’t. And when it can’t, your application breaks. Assuming that the database collation and the server collation will be the same is a fragile assumption. Worse, it can greatly limit deployment and consolidation options for clients.

It works fine in environments where:

  • The application database was created using the server default collation
  • Dev, test, and prod servers all happen to match

But it fails when:

  • The database uses a vendor-specific collation
  • The server uses a regional or legacy collation
  • The database is restored to a different environment
  • Multiple applications with different collation requirements share a server

None of these scenarios are unusual.

A Common Failure Pattern

Consider a simple example:

CREATE TABLE dbo.customers
(
    customer_code varchar(20) NOT NULL
);

Now the application uses a temporary table:

CREATE TABLE #customers
(
    customer_code varchar(20) NOT NULL
);

And then joins them:

SELECT c.*
FROM dbo.customers AS c
JOIN #customers AS t
    ON c.customer_code = t.customer_code;

This code looks simple enough but depending upon the configuration, SQL Server may raise a collation conflict error.

Note that the application didn’t do anything unusual. But it also didn’t do anything to protect itself.

Fixing the issue

The Root Cause: Missing Collation Intent

The underlying problem is not that collations are different. The problem is that the application code fails to state its intent.

When SQL Server sees a string comparison, it needs to know:

  • Which collation rules apply
  • How to compare characters
  • How to determine equality and ordering

If the code does not specify this explicitly, SQL Server is left in a quandry.

The Correct Design Pattern: COLLATE DATABASE_DEFAULT

SQL Server provides a built-in, portable solution for this exact scenario:

COLLATE DATABASE_DEFAULT

This tells SQL Server:

Use the collation of the current database, regardless of where this object physically lives.

This is the single most important rule that applications should follow when interacting with tempdb.

Applying It Where It Matters

Temporary Table Definitions

When defining string columns in temporary tables, if you can make a change, explicitly apply the database collation:

CREATE TABLE #customers ( customer_code varchar(20) COLLATE DATABASE_DEFAULT NOT NULL );

Now the column in #customers uses the same collation as the database, even though it lives in tempdb.

Comparisons and Joins

If you can’t change the table definition (or are dealing with legacy code), apply collation at comparison time:

SELECT c.*
FROM dbo.customers AS c
JOIN #customers AS t
    ON c.customer_code = t.customer_code COLLATE DATABASE_DEFAULT;

This is less ideal than fixing the table definition, but it is still safe and explicit.

String Literals

String literals inherit the database collation, unless they are forced into a comparison with a differently collated column.

Being explicit removes ambiguity:

WHERE c.customer_code = 'ABC123' COLLATE DATABASE_DEFAULT;

Why This Should Be Standard Practice

Using COLLATE DATABASE_DEFAULT has several important advantages:

  • Portability - The same code works regardless of server collation.
  • Predictability - The database collation always governs string behavior.
  • Reduced Support Issues - Fewer works on my machine problems.
  • Cleaner Migrations - Restores, upgrades, and cloud moves are safer.

Most importantly:

This design decision costs almost nothing and prevents an entire category of production failures.

Relying on the Server Collation Is a Mistake

Some applications take the opposite approach: they assume the server collation should dictate the behavior.

This is risky because:

  • Server collations are often chosen for historical reasons
  • Multiple databases on a server may have different requirements
  • Changing server collation is disruptive and expensive
  • Cloud and managed environments may impose defaults you don’t control

Applications should be self-contained. Collation behavior should be owned by the database, not leaked from the instance.

A Simple Rule for Application Designers

If your application uses temporary tables, and compares string data across database and temp objects, then your code should always make collation intent explicit.

The rule is simple: When working with temporary objects, use COLLATE DATABASE_DEFAULT for string columns and comparisons. Following this rule does not make your application slower, more complex, or harder to read. It simply makes it correct.

What about table variables?

Table variables (@table) use the current database collation, not the tempdb collation. So they do not suffer from the same collation mismatch problem that temporary tables (#temp) do.

That distinction is real, important, and often glossed over.

Temporary tables (#temp)

  • Physically created in tempdb
  • String columns inherit the tempdb collation
  • tempdb uses the server collation
  • Can conflict with user database collation

Table variables (@table)

  • Metadata scoped to the batch / procedure
  • String columns inherit the current database collation
  • Not the server collation
  • Generally safe from collation conflicts with user tables

This means:

DECLARE @t TABLE
(
    customer_code varchar(20) -- database collation
);

customer_code will use the user database collation, not the server or tempdb collation.

Why table variables behave differently

Even though table variables are materialized in tempdb, SQL Server treats their metadata differently:

  • Their column definitions are bound to the current database context
  • Collation is resolved at declaration time
  • They behave more like inline table metadata than true temp objects

So while the data lives in tempdb, the collation rules do not. Many articles simplify by saying anything in tempdb uses tempdb collation. That’s just not true for table variables.

If you ever see collation errors involving table variables, it’s almost always because:

  • They are being compared to something else (temp tables, literals, other databases)
  • Or the code was executed under a different database context than expected and not because the table variable itself used the wrong collation.

Final Thoughts

Collation conflicts involving tempdb are not an unavoidable SQL Server quirk. They are a design oversight at the application level.

Applications fail not because SQL Server behaves unpredictably, but because the code does not tell SQL Server what it wants.

A few well-placed uses of COLLATE DATABASE_DEFAULT turn a fragile application into a portable, robust one, and save you from some of the most annoying support calls you’ll ever receive.

If you’re reviewing existing code, this is a small change with a potentially large payoff.

Learn more about SQL Server Administration

If you really want to learn about SQL Server administration right now, we have an online on-demand course that you can enrol in, right now. You’ll find it at SQL Server Administration for Developers and DBAs

2026-01-26