Introduction

Have you ever tried to run a query that spans across multiple databases in SQL Server, only to be puzzled when it doesn’t work as expected or runs slower than molasses? Well, my friend, you may have fallen victim to the dreaded issue of cross-database query incompatibility!

In this article, I’ll share some hard-learned lessons from my own battles with this problem. We’ll explore what compatibility levels are, how they can trip up your cross-database queries, and most importantly – how to troubleshoot and fix these issues. By the end, you’ll be armed with the knowledge to conquer even the gnarliest multi-database monsters. Let’s dive in!

What are Compatibility Levels?

In SQL Server, each database has a compatibility level setting that determines certain database behaviors. It’s specified as a version number like 150 for SQL Server 2019 or 160 for SQL Server 2022.

The compatibility level acts like a time machine for your database. It allows the database engine to mimic the behavior of that specific version, even on a newer version of SQL Server. This provides backwards compatibility so your old code doesn’t break on upgrades.

But beware! When databases involved in a cross-database query have different compatibility levels set, strange things can happen. It’s like trying to have a conversation with someone who speaks a different dialect – misunderstandings are bound to arise!

How Compatibility Level Affects Cross-DB Queries

Let’s consider an example. Say you have two databases:

• DB1 – compatibility level 150 (SQL Server 2019)
• DB2 – compatibility level 130 (SQL Server 2016)

You try to run this seemingly innocent query:

SELECT * 
FROM DB1.dbo.Table1 t1
INNER JOIN DB2.dbo.Table2 t2 
  ON t1.ID = t2.ID

Looks fine, right? But when you check the execution plan, you notice the join between tables is happening much later than you expect. The Query Optimizer is evaluating the ON clause differently due to the compatibility level mismatch!

In my experience, these types of issues often manifest as:

• Queries that worked before suddenly failing with errors
• Painfully slow performance compared to querying within one database
• Incorrect results or plans due to semantic differences between versions

Troubleshooting Compatibility Level Conflicts

Okay, so you’ve got a misbehaving cross-database query. Take a deep breath. You’ve got this! Let’s walk through some steps to get to the bottom of it:

1. Check the compatibility levels of all databases involved using SELECT compatibility_level FROM sys.databases. Any differences? That’s suspect #1!
2. Look at the execution plan for any operators that don’t make sense. Are joins, filters, or aggregates happening in a weird order?
3. Compare schemas between the databases. Even minor differences can confuse the Query Optimizer when working across compatibility levels.
4. Trace the query with SQL Profiler or Extended Events to look for errors or resource-intensive operations.
5. TEST TEST TEST! Isolate the problem in a repro and see if forcing the same compatibility level makes it go away.

Conclusion

In a perfect world, all of our databases would be in perfect harmony at the same compatibility level. But in reality, many of us are dealing with a mish-mash of database versions and settings.

The key takeaway here is to be aware of how different compatibility levels can cause chaos when querying across databases. Armed with that knowledge, you can attack the problem head-on!

When in doubt, aim to get your compatibility levels in sync. It will make your life as a database professional much easier in the long run. Trust me, I learned that lesson the hard way!

I hope this article helped demystify this tricky topic. Do you have any crazy stories about wrestling with cross-database compatibility issues? Share them in the comments below!

Learn more: ALTER DATABASE Compatibility Level