VARCHAR(MAX) acting weird when concatenating string

Question

I have a concatenating query:

DECLARE @path NVARCHAR(max) 
SELECT @path = ISNULL(@path + '/', '') + url_segment 
    FROM navigation_self_and_parents(2813) ORDER BY depth ASC
SELECT @path

navigation_self_and_parents(2813) returns

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

My concatenating query returns

'testing1234'             when using `NVARCHAR(MAX)` and
'E//fixedurl/testing1234' when using `NVARCHAR(4000)`

My best guess is that using NVARCHAR(MAX) 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.

I would love to truly understand the root cause of this behavior though.

UPDATE

navigation_self_and_parents:

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
)

navigation_node DDL:

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

I've had similar results with this approach to concatenating before. (where it ends up only with the value from one row rather than all) I've never noticed that datatype has played a part. Sometimes you need to tweak the query a bit to get the required execution plan. Can you post both plans? (Or you could of course just use XML PATH as you are on SQL Server 2005 which is documented to work) See also support.microsoft.com/kb/287515/en-us — Martin Smith
@Martin it seems removing the order by clause does indeed have an impact on what results i get back. If you post your comment as answer i'd be happy to accept it. — Martijn Laarman
What do you get for just SELECT * FROM navigation_self_and_parents(2813) ORDER BY depth ASC? Exactly the 3 rows no more no less? — RichardTheKiwi

Martin Smith Martin Smith · Accepted Answer · 2011-01-24T10:23:43

This approach to string concatenation does usually work but it isn't guaranteed.

The official line in the KB article for a similar issue is that "The correct behavior for an aggregate concatenation query is undefined."

There must be some subtle difference between the plans. You can either tweak the query to remove the difference and get the required execution plan or you could/should of course just use XML PATH as you are on SQL Server 2005 and this is documented to work.

VARCHAR(MAX) acting weird when concatenating string

1 Answers