When it comes to optimizing SQL Server queries, understanding query execution plans is crucial. Query execution plans provide insights into how SQL Server processes and executes queries, allowing developers to identify potential performance bottlenecks and make necessary optimizations.

In a previous blog post, we explored the concept of using the UNION operator in SQL Server queries. The post received positive feedback, prompting us to continue the discussion with a new puzzle.

Let’s take a look at three different queries that use the UNION operator, subquery, and the NOT EQUAL TO operator:

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'

Query 3:

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

Executing all three queries will yield the same result. However, when we examine the execution plans, we notice a difference in how the NOT EQUAL TO operator is handled.

In Query 1 and Query 2, the execution plan converts the NOT EQUAL TO operator to an EQUAL TO operator and compares the integer 2. On the other hand, Query 3 keeps the NOT EQUAL TO operator as it is and compares it with the integer 1.

Now, the question is: Why does Query 3 not convert the NOT EQUAL TO operator like Query 1 and Query 2?

If you have the answer, please leave it in the comment section below. We will publish the correct answer with due credit in our next blog post.

Understanding query execution plans is just one aspect of optimizing SQL Server queries. Stay tuned for more articles on SQL Server optimization techniques and best practices.