Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Performance Tuning: Concatenation Functions and Some Tuning Myths


Performance Tuning: Concatenation Functions and Some Tuning Myths

Author
Message
JJ B
JJ B
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 2846
Matt: I've run into that very problem on more than one occasion. Sometimes I do a replace() to fix the problem. Sometimes I can change the text to avoid the encoding (?not sure if that's the right term) text.

Here's another trick. What if you want a line return between each row instead of a comma? You might be tempted to use the following to create a new row:
SELECT Char(13) + Char(10) + MyColumn ...


But if you do that, you get this garbeldyguk at the end of each line: "
" Ugh. The trick is to do the Char(10) by itself without the Char(13). Then you get line returns without the added text. This may be obvious to people, but it wasn't to me. It took some playing to figure it out. So, I thought I would share.
JJ B
JJ B
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 2846
Mike: I'd be interested to know if you ever get the TYPE option to work. I had read about it and tried it, but it never worked for me. Then again, I may have been doing something wrong.
Mike C
Mike C
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1349 Visits: 1168
JJ B (3/19/2008)
Mike: I'd be interested to know if you ever get the TYPE option to work. I had read about it and tried it, but it never worked for me. Then again, I may have been doing something wrong.


I just tried it and it didn't change the outcome Sad I'll play around with it later and see if I can get it working. It may end up being a case where REPLACE is required on the 5 entities:

<
>
"
'
&


JJ B
JJ B
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 2846
Mike: Thanks for looking into it. Glad to know I didn't miss something terribly obvious.

re: "replace required on certain characters. "
I would add: & # x 0 D ;
as characters that may need replacing.
(without the spaces. I can't figure out how to get this to display as-is. What did you do to get the above encodings to appear as-is?)
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7639 Visits: 18062
JJ B (3/20/2008)
Mike: Thanks for looking into it. Glad to know I didn't miss something terribly obvious.

re: "replace required on certain characters. "
I would add: & # x 0 D ;
as characters that may need replacing.
(without the spaces. I can't figure out how to get this to display as-is. What did you do to get the above encodings to appear as-is?)

I usually "con" it by "bolding" one of the characters in the string. by having the tag in the middle of the string - HTML doesn't "kick in " and encode it.

As in - (x is bolded)



----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
JJ B
JJ B
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 2846
Matt: Great tip! I used it on my earlier post and it worked. Thanks.
Mike C
Mike C
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1349 Visits: 1168
That's what I basically did (after about 10 edit attempts). I italicized everything after the ampersand. It appears that when the parser for this editor sees an ampersand it wipes out all alphabetic characters up to the semicolon. So the trick of using > for instance, wipes out the gt; but converts the & to an ampersand. Seems like it might be a bug in the editor control. Apparently when it encounters a nonalphabetic character like [ it acts as a break between the ampersand and the alphabetic characters.
JJ B
JJ B
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 2846
Thanks for the additional reply. I wish there was an easy escape character or set of characters to say "hey, please print this text as-shown". Sort of like doubling a single quote in a string to let SQL Server know that it really is just a single quote.
Tom Brown
Tom  Brown
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1342 Visits: 1466
Jeff
Its brilliant. But how on earth can anyone be expected to figure out the STUFF(... WHERE ... FOR XML PATH('') Syntax - especially when there is no XML involved!
I'll be using this, but I'm a bit wary as I don't know exactly how it works
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44993 Visits: 39879
Tom Brown (8/21/2008)
Jeff
Its brilliant. But how on earth can anyone be expected to figure out the STUFF(... WHERE ... FOR XML PATH('') Syntax - especially when there is no XML involved!
I'll be using this, but I'm a bit wary as I don't know exactly how it works



Thanks... I wish I could take credit for it, but I can't.

I haven't done an indepth analysis of the method, but I've read that it tries to make an XML path out of the individual items and, because the "root" is supressed, it generates not tags. The "STUFF" is just to kill the leading comma.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search