VARCHAR(MAX) acting weird when concatenating string
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
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.