2

We are attempting to concatenate possibly thousands of rows of text in SQL with a single query. The query that we currently have looks like this:

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

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

This is working perfectly fine from a functional standpoint. The only issue we're having is that sometimes the ColumnText can be a few kilobytes in length. As a result, we're filling up tempDB when we have thousands of these rows.

The best reason that we have come up with is that as we're doing these updates to @concatText, SQL is using implicit transactions so the strings are effectively immutable.

We are trying to figure out a good way of solving this problem and so far we have two possible solutions: 1) Do the concatenation in .NET. This is an OK option, but that's a lot of data that may go back across the wire.

2) Use .WRITE which operates in a similar fashion to .NET's String.Join method. I can't figure out the syntax for this as BoL doesn't cover this level of SQL shenanigans.

This leads me to the question: Will .WRITE work? If so, what's the syntax? If not, are there any other ways to do this without sending data to .NET? We can't use FOR XML because our text may contain illegal XML characters.

Thanks in advance.

Nate Noonen
  • 1,371
  • 9
  • 19
  • 1
    I've never seen this level of documentation that explains how `TSQL` handles strings. My experiments with `STUFF` indicate that doesn't have superior performance to other methods so I guess maybe all strings are immutable in TSQL. Not sure what you mean "over the wire". Can't you use `CLR` integration in SQL Server and a user defined aggregate? – Martin Smith May 11 '11 at 16:11
  • What is the goal of this concatenation? How do you intend to use it? Do you need to do it in the database? – Magnus May 11 '11 at 17:35

4 Answers4

2

I'd look at using CLR integration, as suggested in @Martin's comment. A CLR aggregate function might be just the ticket.

Nicholas Carey
  • 71,308
  • 16
  • 93
  • 135
  • I think we'll go with this in the end. I wish there was an easy way to run .NET on SQL without deploying assemblies. Like ASP.NET jitting for SQL. – Nate Noonen May 12 '11 at 21:44
1

What exactly is filling up tempdb? It cannot be @concatText = @concatText + [ColumnText], there is no immutability involved and the @concatText variable will be at worst case 2GB size (I expect your tempdb is much larger than that, if not increase it). It seems more like your query plan creates a spool for haloween protection and that spool is the culprit.

As a generic answer, using the UPDATE ... SET @var = @var + ... for concatenation is known to have correctness issues and is not supported. Alternative approaches that work more reliably are discussed in Concatenating Row Values in Transact-SQL.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
0

First, from your post, it isn't clear whether or why you need temp tables. Concatenation can be done inline in a query. If you show us more about the query that is filling up tempdb, we might be able to help you rewrite it. Second, an option that hasn't been mentioned is to do the string manipulation outside of T-SQL entirely. I.e., in your middle-tier query for the raw data, do the manipulation and push it back to the database. Lastly, you can use Xml such that the results handle escapes and entities properly. Again, we'd need to know more about what and how you are trying to accomplish.

Thomas
  • 63,911
  • 12
  • 95
  • 141
  • There are some characters which blow up all the XML methods. Can't remember off the top of my head what they are though! – Martin Smith May 11 '11 at 16:58
  • @Martin - That blow up Xml *and cannot be escaped*? I'd be interesting in knowing what those are. – Thomas May 11 '11 at 17:02
  • @Thomas - `CHAR(11)` or `CHAR(27)` [AFAIK](http://www.sqlservercentral.com/Forums/FindPost715634.aspx) – Martin Smith May 11 '11 at 17:48
  • From RBarryYoung's comment on that thread it applies to other non printable control characters as well. Not sure what you mean by encode/decode - Is this some nested replaces or something more sophisticated than that? – Martin Smith May 11 '11 at 18:23
  • @Martin - LF, CR and Tab should be ok. In fact, those are the only three non-printables that are ok. Encode/decode does in fact mean some nested replaces for those few odd characters on which Xml chokes. There aren't going to be that many. – Thomas May 11 '11 at 18:39
0

Agreed..A CLR User Defined Function would be the best approach for what you guys are doing. You could actually read the text values into an object and then join them all together (inside the CLR) and have the function spit out a NVARCHAR(MAX) result. If you need details on how to do this let me know.

Eric Loflin
  • 1