SQL连接填满tempDB

Question

我们正在尝试将SQL中可能有数千行文本与一个查询连接起来。我们当前拥有的查询如下所示：

DECLARE @concatText NVARCHAR(MAX) 
SET @concatText = ''

UPDATE TOP (SELECT MAX(PageNumber) + 1 FROM #OrderedPages) [#OrderedPages] 
SET @concatText = @concatText + [ColumnText] + '
'
WHERE (RTRIM(LTRIM([ColumnText])) != '')

从功能的角度来看，这是非常好的。我们唯一的问题是，有时ColumnText的长度可能只有几千字节。因此，当我们有数以千计的这样的行时，我们正在填充tempDB。

我们提出的最好的原因是，当我们对@concatText进行这些更新时，SQL使用了隐式事务，因此字符串实际上是不可变的。

我们正在尝试找出解决这个问题的好方法，到目前为止，我们有两种可能的解决方案: 1)在.NET中进行连接。这是一个好的选择，但这是一个可能会返回的大量数据。

2)使用与.NET的String.Join方法类似的操作方式的.WRITE。我不知道这是什么语法，因为BoL没有涵盖这个级别的SQL shenanigans。

这就引出了一个问题：.WRITE能工作吗？如果是这样，它的语法是什么？如果没有，有没有其他方法可以在不向.NET发送数据的情况下做到这一点？无法使用FOR XML，因为我们的文本可能包含非法的XML字符。

提前谢谢。

Answer 1

我会考虑使用CLR集成，就像@Martin的评论中建议的那样。CLR聚合函数可能就是票证。

Answer 2

到底是什么在填满tempdb？它不能是@concatText = @concatText + [ColumnText]，没有涉及到不可变性，@concatText变量在最坏的情况下将是2 2GB的大小(我希望你的临时数据库要比这个大得多，如果不增加的话)。看起来更像是您的查询计划为haloween保护创建了一个假脱机，而该假脱机是罪魁祸首。

作为一个通用的答案，使用UPDATE ... SET @var = @var + ...进行连接是已知存在正确性问题的，并且不受支持。在Concatenating Row Values in Transact-SQL中讨论了工作更可靠的替代方法。

Answer 3

首先，从你的帖子来看，不清楚你是否需要临时表，或者为什么需要临时表。串联可以在查询中以内联方式完成。如果您向我们展示有关填充tempdb的查询的更多信息，我们也许能够帮助您重写它。其次，还没有提到的一种选择是完全在T-SQL之外进行字符串操作。也就是说，在对原始数据的中间层查询中，执行操作并将其推送回数据库。最后，您可以使用Xml，以便结果正确地处理转义和实体。再说一次，我们需要更多地了解您试图实现的目标和方式。