Data Type Precedence

When an operator combines two expressions of different data types, the rules of data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, an error is returned. When both operand expressions have the same data type, the result of the operations will be that data type.

Here are a couple of examples on errors that are usually encountered due to the implicit conversion of data types based on the data type precedence outlined below:

SELECT 'The Current Date is ' + GETDATE()

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

SELECT @@ROWCOUNT + ' Rows Affected'

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ' Rows Affected' to data type int.

SQL Server uses the following precedence for data types, from highest to lowest:

1. User-defined data types
2. json
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