sql - 连接字符串时VARCHAR(MAX)表现得很奇怪

Question

我有一个连接查询:

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)返回

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   

我的连接查询返回

'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
)

导航节点 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

Answer 1

这种字符串连接方法通常有效，但不能保证。

官方专线the KB article for a similar issue是“未定义聚合串联查询的正确行为”。

计划之间肯定有一些细微的差别。您可以调整查询以消除差异并获得所需的执行计划，或者您可以/当然应该只使用 XML PATH就像您在 SQL Server 2005 上一样，并且记录在案。