Have you ever wondered why two SQL queries that seem identical can have different execution plans? In today’s blog post, we will explore this puzzling phenomenon and try to understand the underlying concepts.

Let’s consider two queries, Query 1 and Query 2:

Query 1:

SELECT *
FROM (
SELECT '1' AS Col
UNION ALL
SELECT '2') t
WHERE t.Col <> 1

Query 2:

SELECT *
FROM (
SELECT 1 AS Col
UNION ALL
SELECT 2) t
WHERE t.Col <> '1'

At first glance, these queries appear to be almost identical and should produce the same result. However, when we examine their execution plans, we notice a significant difference.

In Query 1, there is an implicit conversion and a warning related to this conversion. The filter operator also contains a Not Equal To operator, which matches the condition used in the query.

On the other hand, Query 2 does not involve any implicit conversion, despite the mismatched data types between the column and the variable. Additionally, the filter operator behaves differently, automatically converting the Not Equal To operator to an Equal To operator.

So, why do Query 1 and Query 2, which seem identical in theory, exhibit such different behavior in terms of execution plans?

We invite you to share your thoughts and insights in the comment section below. Next week, we will compile all the answers and reveal the valid explanation, giving credit to the contributors.

Understanding execution plans is crucial for optimizing SQL queries and improving performance. Stay tuned for more articles on SQL Server concepts and best practices!