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

Solarwinds

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)

Solarwinds
,
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

4 Comments. Leave new

  • Excellent.

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

    Reply
  • another cool hidden bug!

    try

    SELECT ID,Col
    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

    SELECT ID,Col
    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!?

    Reply
  • 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.

    Reply

Leave a Reply

Menu