While I was working on creating a question for SQL SERVER – SQL Quiz – The View, The Table and The Clustered Index Confusion, I had actually created yet another question along with this question. However, I felt that the one which is posted on the SQL Quiz is much better than this one because what makes that question more challenging is that it has a multiple answer.
Here is the question regarding Simple Explanation of Data Type Precedence:
Run the following example first and then observe the query execution plan.
CREATE TABLE FirstTable (ID INT, Col VARCHAR(100))
INSERT INTO FirstTable (ID, Col)
SELECT 1, 'First'
SELECT 2, 'Second'
SELECT 3, 'Third'
-- Enable Execution Plan; Result to Text
--SET SHOWPLAN_ALL OFF
WHERE ID = N'1' AND Col = N'First'
-- Clean up
DROP TABLE FirstTable
Move your mouse over the table scan operation.
You will notice that there are two usages of the CONVERT_IMPLICIT functions. If you carefully observe them, you will also notice that the functions are implemented once to the right side of the operator and once to the left side of the operator. Technically speaking, this means it is applied once to the column of the table and once to the parameter passed to the function.
Let us try to understand this further. In the created table, we have one column as VARCHAR and another as INT. The variable which we are comparing along with that is of type NVARCHAR. What is interesting with this is that the passed NVARCHAR parameter has been converted once to INT, while another time table column was converted to NVARCHAR.
The reason for this is very simple: data type precedence:
- SQL Server uses the following precedence order for data types:
- user-defined data types (highest)
- nvarchar (including nvarchar(max) )
- varchar (including varchar(max) )
- varbinary (including varbinary(max) )
- binary (lowest)
In terms of precedence, INT has a higher precedence than NVARCHAR, leading NVARCHAR data type to be converted into INT. However, VARCHAR has a lower precedence than NVARCHAR, making that column to convert it into NVARCHAR.
I hope this is clear enough so it can help you to understand the execution plan better. You can read further details on this subject over here.
Reference: Pinal Dave (http://blog.SQLAuthority.com)