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
INSERT INTO FirstTable (ID, Col)
SELECT 1, 'First'
SELECT 2, 'Second'
SELECT 3, 'Third'
-- Enable Execution Plan; Result to Text
FROM FirstTable
WHERE ID = N'1' AND Col = N'First'
-- Clean up

Move your mouse over the table scan operation.

SQL SERVER - Simple Explanation of Data Type Precedence datapre

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://blog.sqlauthority.com)

SQL Datatype, SQL Scripts
Previous Post
SQL SERVER – SQL Quiz – The View, The Table and The Clustered Index Confusion
Next Post
SQLAuthority News – SQL Server 2008 Add-ins and Feature Pack Downloads

Related Posts

5 Comments. Leave new

  • Excellent.

  • money and small money data type accepting 4 decimals then wat is difference between money and small money

  • another cool hidden bug!


    FROM FirstTable
    WHERE ID = 1 AND Col = ‘First’

    and it will do convert implicit to int but in this case it is not required according to my little sql knowledge

    BUT if you try

    FROM FirstTable
    WHERE ID = 1.0 AND Col = ‘First’

    it doesn’t do implicit conversion to int!!! why it is not doing it and why it works fine!?

    Pinal, can you please enlighten us on this!?

  • Tom Wickerath
    June 12, 2018 1:14 am

    Hi Pinal,
    I also noticed the same thing that Jack posted about on April 18. I’m using SQL Server 2016 SP2 for my tests. Why would an ID value of 1 result in an implicit conversion? I verified that an ID = 1.0 does not result in this implicit conversion.

  • This is an answer 5 years later. I was confused as hell with this as well. I looked into the execution plan xml and saw that it was treating the value 1 as tinyint and hence the IMPLICIT conversion. Any value from 1 to 255 shows up as tinyint, -1 to -32768 and 256 to 32767 shows up as small int. When you enter ID = 32768 and above or -32769 and below then the implicit conversion does not show up anymore


Leave a Reply