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 (http://blog.SQLAuthority.com)

About these ads

3 thoughts on “SQL SERVER – Simple Explanation of Data Type Precedence

  1. Pingback: SQL SERVER – Weekly Series – Memory Lane – #050 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s