TSQL CTE error ”Types don’t match between the anchor and the recursive part”

Would someone help me understand the details of the error below..? This is for SQL Server 2008.

I did fix it myself, and found many search hits which show the same fix, but none explain WHY this happens in a CTE.

Types don’t match between the anchor and the recursive part in column “txt” of recursive query “CTE”.

Here is an example where I resolved the issue with CAST, but why does it work?

WITH CTE(n, txt) AS
(
    --SELECT      1, '1'                          --This does not work.
    --SELECT      1, CAST('1' AS varchar)         --This does not work.
    --SELECT      1, CAST('1' AS varchar(1000))   --This does not work.
    SELECT      
        1, 
        CAST('1' AS varchar(max))                 --This works. Why?
    UNION ALL
    SELECT      
        n+1, 
        txt + ', ' + CAST(n+1 AS varchar)         --Why is (max) NOT needed?
    FROM        
        CTE
    WHERE       
        n < 10
)
SELECT *
FROM CTE

I assume there are default variable types at play which I do not understand, such as:

• what is the type for something like SELECT 'Hello world! ?
• what is the type for the string concatenation operator SELECT 'A' + 'B' ?
• what is the type for math such as SELECT n+1 ?

Answer

The info you want is all in the documentation:

When concatenating two char, varchar, binary, or varbinary expressions, the length of the resulting expression is the sum of the lengths of the two source expressions, up to 8,000 bytes.

snip …

When comparing two expressions of the same data type but different lengths by using UNION, EXCEPT, or INTERSECT, the resulting length is the longer of the two expressions.

The precision and scale of the numeric data types besides decimal are fixed. When an arithmetic operator has two expressions of the same type, the result has the same data type with the precision and scale defined for that type.

However, a recursive CTE is not the same as a normal UNION ALL:

The data type of a column in the recursive member must be the same as the data type of the corresponding column in the anchor member.

So in answer to your questions:

• 'Hello world!' has the data type varchar(12) by default.
• 'A' + 'B' has the data type varchar(2) because that is the sum length of the two data types being summed (the actual value is not relevant).
• n+1 is still an int
• In a recursive CTE, the data type must match exactly, so '1' is a varchar(1). If you specify varchar without a length in a CAST then you get varchar(30), so txt + ', ' + CAST(n+1 AS varchar) is varchar(33).

When you cast the anchor part to varchar(max), that automatically means the recursive part will be varchar(max) also. You don’t need to cast to max, you could also cast the recursive part directly to varchar(30) for example:

WITH CTE(n, txt) AS
(
    --SELECT      1, '1'                          --This does not work.
    SELECT      1, CAST('1' AS varchar(30))         --This does work.
    --SELECT      1, CAST('1' AS varchar(1000))   --This does not work.
    UNION ALL
    SELECT      
        n+1, 
        CAST(CONCAT(txt, ', ', n+1) AS varchar(30))
    FROM        
        CTE
    WHERE       
        n < 10
)
SELECT *
FROM CTE

db<>fiddle