How does SQL Server determine the expression type when mixing Unicode and non-Unicode strings? When you say a + b in T-SQL and a and b are of a string type (e.g. varchar(...) or nvarchar(...)): How is the resulting type determined?

I found no clear rules for this when experimenting. It seems to depend on whether literals are involved.

Also, how is the length determined? I found out that the type of 'x' + 'y' is varchar(2). Apparently, the length of the literal is being tracked.

Here's a little experiment that I ran:

USE tempdb

DECLARE @v VARCHAR(400) = 'x'
DECLARE @n NVARCHAR(400) = 'x'

SELECT
    '' AS [varchar]
    , N'' AS [nvarchar]

    , ('x' + N'x') AS ['x' + N'x']
    , (N'x' + 'x') AS [N'x' + 'x']
    , (ISNULL('x', N'x')) AS [ISNULL('x', N'x')]
    , (ISNULL(N'x', 'x')) AS [ISNULL(N'x', 'x')]
    , (COALESCE('x', N'x')) AS [COALESCE('x', N'x')]
    , (COALESCE(N'x', 'x')) AS [COALESCE(N'x', 'x')]

    , (@v + @n) AS [@v + @n]
    , (@n + @v) AS [@n + @v]
    , (ISNULL(@v, @n)) AS [ISNULL(@v, @n)]
    , (ISNULL(@n, @v)) AS [ISNULL(@n, @v)]
    , (COALESCE(@v, @n)) AS [COALESCE(@v, @n)]
    , (COALESCE(@n, @v)) AS [COALESCE(@n, @v)]
INTO StringConversionTest




The documentation calls out the behavior of ISNULL and COALESCE so I understand that part. But the + operator seems to behave under a more complex set of rules.
There are several situations in which two (or more) distinct things are being combined and yet there can only be a single result. These questions are resolved by applying a set of precedence rules. In this case, it would use both Datatype Precedence and Collation Precedence. But there is also logical precedence (when combining AND and OR without parenthesis) and how to deal with decimal computations of varying precision, etc). 


Data Type Precedence
Collation Precedence
Precision, Scale, and Length


Related:


Operator Precedence