It seems that my yesterday’s Simple Puzzle with UNION intrigued many readers. Here is my fifth and final part of this Puzzles with UNION. I strongly suggest to read yesterday’s puzzle before continuing today’s puzzle as it is very much based on yesterday’s puzzle.
- SQL SERVER – Simple Puzzle with UNION
- SQL SERVER – Simple Puzzle with UNION – Part 2
- SQL SERVER – Simple Puzzle with UNION – Part 3
- SQL SERVER – Simple Puzzle with UNION – Part 4
Well, Now if you have solved yesterday’s puzzle today’s puzzle will be a bit easy for you.
Here are two almost similar queries and both of them give us an almost identical answer. However, their execution plans are very different.
Query 1
SELECT * FROM ( SELECT '1' AS Col UNION ALL SELECT '2') t WHERE t.Col <> 1
Now let us observe the execution plan for this query.
When we pay attention to the execution plan of this query – we can notice that there is implicit conversion as well as there is a warning which is related to implicit conversion. Additionally, when you see the filter it also contains Not Equal To operator which we have used in the query as well.
Query 2
SELECT * FROM ( SELECT 1 AS Col UNION ALL SELECT 2) t WHERE t.Col <> '1'
Now when you execute both of the queries.
When we play attention to the execution plan of this query – we can notice that there is no implicit conversion even though the data type of the column and variable is mismatched to each other. Additionally, there is interesting behavior of the filter operator as well. In this case, the Not Equal To operator is automatically converted to Equal To operator.
Now here is the question back to you –
Why Query 1 and Query 2 even though looks identically and in theory has almost same logic their behavior in terms with execution plan is so different?
Please leave your answer in the comment section. Next week, I will compile all the answers and will post the valid answer with due credit.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
10 Comments. Leave new
When column with char datatype to compare with numeric value then its need to implicit datatype conversion for column data
&
when column with numeric datatype to compare with char value then no need for conversion.
its because the quotes that are associated with 1 & 2 on the first query & then on the second the quotes are associated only in the where clause.
The optimizer favors comparisons between char/varchar and int be done as integers (it is faster). Query 2’s union returns integers so the ‘1’ is optimized during plan generation (because it is a constant) to 1 and then to =2. This removes the need for an implicit run time conversion of the conditional test.
The conversion depends on the rules of data type precedence rule. The data type precedence rules specify the lower precedence is converted to the data type with the higher precedence. Here in the 1st query the value of t.col can be considered as char,nchar,varchar,nvarchar having lower precedence then int value so the 1st query required to use implicit conversion function to convert the value of t.col to integer datatype.
It seems that if in a query/sub-query unions are of the same type, then the optimiser won’t kick in before the union operation is executed. In other words the optimizer doesn’t foresee the datatype of the predicate in the forthcoming filter operation to optimise the union as integers from the beginning. This happens in general sub-queries and not only unions.
HI PINAL,
I did’nt recieve any warnings after executing query 1.
Whatsoever,both the queries are same in terms of output and filter operation used in both queries.
the difference between query1 and query2 is predicte,subtree cost and cpu cost and operation cost as well.
In the query 2,When column with char datatype to compare with numeric value then the need for implicit datatype conversion comes into picture.therefore, in order to convert numeric values in different costs and predicte will increase .
which is not in query 1 because, when column with numeric datatype to compare with char value then no need for conversion.
Optimizer prefers to convert the integer for query processing as it is faster. Also in Query 1, where optimizer trying to convert table values to integer, it does not know whether all varchar values are compatible so it gives warning.
The query optimizer prefers to convert the literal (constant) value to an integer in query 2 because the comparison is much faster as compare to other non-integer data types. Also, the results of the union is integer deterministic values, so optimizer converts the not equal operator to an equal operator.
SQL Server always try to convert data type of right side part of comparison operator to that of left side value. Here it is feasible to convert the value ‘1’ to lower data type.