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'

    '' 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

enter image description here

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).


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.