SQL SERVER – Simple Explanation of Data Type Precedence

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.

USE tempdb
GO
CREATE TABLE FirstTable (ID INT, Col VARCHAR(100))
GO
INSERT INTO FirstTable (ID, Col)
SELECT 1, 'First'
UNION ALL
SELECT 2, 'Second'
UNION ALL
SELECT 3, 'Third'
GO
-- Enable Execution Plan; Result to Text
--SET SHOWPLAN_ALL OFF
GO
SELECT *
FROM FirstTable
WHERE ID = N'1' AND Col = N'First'
GO
-- Clean up
DROP TABLE FirstTable
GO

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:

1. SQL Server uses the following precedence order for data types:
2. user-defined data types (highest)
3. sql_variant
4. xml
5. datetimeoffset
6. datetime2
7. datetime
8. smalldatetime
9. date
10. time
11. float
12. real
13. decimal
14. money
15. smallmoney
16. bigint
17. int
18. smallint
19. tinyint
20. bit
21. ntext
22. text
23. image
24. timestamp
25. uniqueidentifier
26. nvarchar (including nvarchar(max) )
27. nchar
28. varchar (including varchar(max) )
29. char
30. varbinary (including varbinary(max) )
31. 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 (https://darkslategrey-bat-805937.hostingersite.com)