title: VARCHAR(MAX) acting weird when concatenating string
body: <p>I have a concatenating query:</p>
  
  <pre><code>DECLARE @path NVARCHAR(max) 
  SELECT @path = ISNULL(@path + '/', '') + url_segment 
      FROM navigation_self_and_parents(2813) ORDER BY depth ASC
  SELECT @path
  </code></pre>
  
  <p><code>navigation_self_and_parents(2813)</code> returns</p>
  
  <blockquote>
  <pre><code>id    par_id  title        url_segment    sequence        depth
  2813  2816    testing1234  testing1234    0               0   
  2816  2809    U            /fixedurl      0               -1   
  2809  NULL    E            E              0               -2   
  </code></pre>
  </blockquote>
  
  <p>My concatenating query returns</p>
  
  <pre>'testing1234'             when using `NVARCHAR(MAX)` and
  'E//fixedurl/testing1234' when using `NVARCHAR(4000)`</pre>
  
  <p>My best guess is that using <code>NVARCHAR(MAX)</code> causes @path to be retyped every time it's set and thus losing the contents set prior to retyping or it's typed the first time it's set and then subsequent concatenating calls silently fail. </p>
  
  <p>I would love to truly understand the root cause of this behavior though.</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>navigation_self_and_parents:</p>
  
  <pre><code>USE [SomeDatabase]
  GO
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  ALTER FUNCTION [dbo].[navigation_self_and_parents]
  (   
      @id int
  )
  RETURNS TABLE 
  AS
  RETURN 
  (
      WITH navigation_self_and_parents (id, parent_id, title, url_segment, sequence_number, depth)
      AS
      (
          SELECT id, parent_id, title, url_segment, sequence_number, 0 FROM navigation_node WHERE id=@id 
          UNION ALL
  
          SELECT n.id, n.parent_id, n.title, n.url_segment, n.sequence_number, depth - 1 From navigation_node as n
          INNER JOIN navigation_self_and_parents as rn
          ON n.id = rn.parent_id
      )
      SELECT * FROM navigation_self_and_parents
  )
  </code></pre>
  
  <p>navigation_node DDL:</p>
  
  <pre><code>CREATE TABLE [dbo].[navigation_node](
      [id] [int] IDENTITY(1,1) NOT NULL,
      [title] [nvarchar](128) NULL,
      [url_segment] [nvarchar](max) NULL,
      [hidden] [bit] NOT NULL,
      [page_id] [int] NULL,
      [parent_id] [int] NULL,
      [sequence_number] [int] NOT NULL,
      [createdOn] [datetime] NOT NULL,
      [updatedOn] [datetime] NULL,
      [navigation_type_id] [int] NULL,
      ...snap
  </code></pre>