Why do linked servers have a limitation of 10 branches in a CASE expression?
Clearly there isn't a nested CASE expression here.


Not in the query text, no. But the parser always expands CASE expressions to the nested form:

SELECT CASE SUBSTRING(p.Name, 1, 1)
        WHEN 'a' THEN '1' 
        WHEN 'b' THEN '2' 
        WHEN 'c' THEN '3' 
        WHEN 'd' THEN '4' 
        WHEN 'e' THEN '5' 
        WHEN 'f' THEN '6' 
        WHEN 'g' THEN '7' 
        WHEN 'h' THEN '8' 
        WHEN 'i' THEN '9' 
        WHEN 'j' THEN '10' 
        WHEN 'k' THEN '11'  
    END
FROM AdventureWorks2012.Production.Product AS p




That query is local (no linked server) and the Compute Scalar defines the following expression:



This is fine when executed locally, because the parser does not see a nested CASE statement over 10 levels deep (though it does pass one on to the later stages of local query compilation).

However, with a linked server, the generated text may be sent to the remote server for compilation. If that is the case, the remote parser sees a nested CASE statement more than 10 levels deep and you get error 8180.


  Another oddity. This inline table-valued function produces the same
  error


The in-line function is expanded in-place into the original query text, so it's no surprise the same error results with the linked server.


  But a similar multi-statement TVF works fine


Similar, but not the same. The msTVF involves an implicit conversion to varchar(max), which happens to prevent the CASE expression being sent to the remote server. Because the CASE is evaluated locally, a parser never sees an over-nested CASE and there is no error. If you change the table definition from varchar(max) to the implicit type of the CASE result - varchar(2) - the expression is remoted with the msTVF and you will get an error.

Ultimately, the error occurs when an over-nested CASE is evaluated by the remote server. If the CASE is not evaluated in the Remote Query iterator, no error results. For example, the following includes a CONVERT that is not remoted, so no error occurs even though a linked server is used:

SELECT CASE CONVERT(varchar(max), SUBSTRING(p.Name, 1, 1))
        WHEN 'a' THEN '1' 
        WHEN 'b' THEN '2' 
        WHEN 'c' THEN '3' 
        WHEN 'd' THEN '4' 
        WHEN 'e' THEN '5' 
        WHEN 'f' THEN '6' 
        WHEN 'g' THEN '7' 
        WHEN 'h' THEN '8' 
        WHEN 'i' THEN '9' 
        WHEN 'j' THEN '10' 
        WHEN 'k' THEN '11'  
    END
FROM SQL2K8R2.AdventureWorks.Production.Product AS p