连接字符串时VARCHAR(MAX)表现得很奇怪 [英] VARCHAR(MAX) acting weird when concatenating string

问题描述

我有一个连接查询:

DECLARE @path NVARCHAR(max)SELECT @path = ISNULL(@path + '/', '') + url_segmentFROM navigation_self_and_parents(2813) ORDER BY depth ASC选择@路径

navigation_self_and_parents(2813) 返回

<块引用>

id par_id title url_segment 序列深度2813 2816 测试1234 测试1234 0 02816 2809 U/fixedurl 0 -12809 NULL E E 0 -2

我的连接查询返回

'testing1234' 使用 `NVARCHAR(MAX)` 和'E//fixedurl/testing1234' 使用 `NVARCHAR(4000)` 时

我最好的猜测是，使用 NVARCHAR(MAX) 会导致每次设置 @path 时都重新输入，从而丢失重新输入之前设置的内容，或者在第一次设置时输入然后随后输入连接调用默默地失败.

不过，我很想真正了解这种行为的根本原因.

更新

navigation_self_and_parents:

使用 [SomeDatabase]走设置 ANSI_NULLS ON走设置 QUOTED_IDENTIFIER ON走更改功能 [dbo].[navigation_self_and_parents](@id 整数)退货表作为返回(WITH navigation_self_and_parents(id、parent_id、title、url_segment、sequence_number、depth)作为(SELECT id, parent_id, title, url_segment, sequence_number, 0 FROM navigation_node WHERE id=@id联合所有SELECT n.id, n.parent_id, n.title, n.url_segment, n.sequence_number, depth - 1 从 navigation_node 作为 nINNER JOIN navigation_self_and_parents 作为 rnON n.id = rn.parent_id)SELECT * FROM navigation_self_and_parents)

navigation_node DDL:

创建表 [dbo].[navigation_node]([id] [int] IDENTITY(1,1) 非空，[title] [nvarchar](128) NULL,[url_segment] [nvarchar](max) NULL，[隐藏] [位] 非空，[page_id] [int] NULL，[parent_id] [int] NULL，[sequence_number] [int] 非空，[createdOn] [datetime] NOT NULL,[updatedOn] [datetime] NULL，[navigation_type_id] [int] NULL，...折断

解决方案

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

针对类似问题的知识库文章中的官方说法是未定义聚合串联查询的正确行为."

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

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.

这篇关于连接字符串时VARCHAR(MAX)表现得很奇怪的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！