Below is a list containing the SQL Server data types, in order of precedence.

1. user-defined data types (highest)
2. sql_variant
3. xml
4. datetimeoffset
5. datetime2
6. datetime
7. smalldatetime
8. date
9. time
10. float
11. real
12. decimal
13. money
14. smallmoney
15. bigint
16. int
17. smallint
18. tinyint
19. bit
20. ntext
21. text
22. image
23. timestamp
24. uniqueidentifier
25. nvarchar (including nvarchar(max) )
26. nchar
27. varchar (including varchar(max) )
28. char
29. varbinary (including varbinary(max) )
30. binary (lowest)

Source: Microsoft

When you use an operator to combine operands of different data types, the data type with the lower precedence is first converted to the data type with the higher precedence.

If the conversion isn’t a supported implicit conversion, an error is returned.

If both operands are of the same type, then no conversion is done (or needed) and the result of the operation uses the data type of the operands.

Example

Here’s an example of an implicit conversion that succeeds:

SELECT 1 * 1.00;

Result:

1.00

Here, the left operand was converted to the data type of the right operand.

Here’s a more explicit way of doing it:

DECLARE 
    @n1 INT, 
    @n2 DECIMAL(5, 2);
SET @n1 = 1;
SET @n2 = 1;
SELECT @n1 * @n2;

Result:

1.00

In this case I explicitly declared the left operand as an INT and the right operand as DECIMAL(5, 2).

We can further examine the results with the sys.dm_exec_describe_first_result_set system dynamic management function.

This function enables us to check the data type of each column returned in a query:

SELECT 
    system_type_name,
    max_length,
    [precision],
    scale
FROM sys.dm_exec_describe_first_result_set(
    'DECLARE @n1 INT, @n2 DECIMAL(5, 2);
SET @n1 = 1;
SET @n2 = 1;
SELECT @n1, @n2, @n1 * @n2;', 
    null, 
    0);

Result:

+--------------------+--------------+-------------+---------+
| system_type_name   | max_length   | precision   | scale   |
|--------------------+--------------+-------------+---------|
| int                | 4            | 10          | 0       |
| decimal(5,2)       | 5            | 5           | 2       |
| decimal(16,2)      | 9            | 16          | 2       |
+--------------------+--------------+-------------+---------+

Here, we can see that each row represents each column returned by the query. Therefore, the first column was an INT, the second column was DECIMAL(5,2), and the third column a DECIMAL(16,2).

So SQL Server actually returned a DECIMAL(16,2), even though the original decimal value was a DECIMAL(5,2).

Example of a Conversion Error

As mentioned, if the conversion isn’t a supported implicit conversion, an error is returned:

SELECT 'Age: ' + 10;

Result:

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

In this case, I was trying to concatenate a string (VARCHAR) and a number (INT). Seeing as INT has a higher precedence than VARCHAR, SQL Server tried to implicitly convert the string to an INT.

This failed, because this string cannot be converted to an integer.

To overcome this, we can first convert the INT to VARCHAR:

SELECT 'Age: ' + CAST(10 AS VARCHAR(2));

Result:

Age: 10

Now both operands have the same data type, and so SQL Server performs the operation successfully without the need to perform any implicit conversions.

Another way of doing this particular operation is with the CONCAT() function:

SELECT CONCAT('Age: ', 10);

Result:

Age: 10

The CONCAT() function is a string function and therefore implicitly converts all arguments to string types before concatenation. Therefore, there was no need for us to perform an explicit conversion.

However, if the string operand can be implicitly converted to a number, then it won’t cause an error when using the + operator:

SELECT '10' + 10;

Result:

20

But in this case, the + operator turns into an addition mathematical operator, rather than a string concatenation operator.