karthikeyan (6/9/2009)
RBarryYoung (6/8/2009)
3) The "pre-allocate and Stuff" trick popular with mutable strings is not workable in T-SQL because the STUFF() function in T_SQL is NOT like the function of the same name in some general purpose languages: the T-SQL STUFF() is an RHS (right-hand side) function and NOT an LHS (left-hand side) function. AFAIK, there is no function in SQL that can (physically) write into a pre-existing string.RBarryyoung,
The above two points are confusing me...
you mean to say TSQL STUFF() function can't write into a pre-existing string.
Correct. Except for the TEXT-mod commands (UPDATETEXT, etc.) SQL Server does not have mutable strings for in-memory values, which simply means that technically, you can never "modify" a string in-place, you always have to make a new copy of it. (note: do not try to apply this to on-disk values, like columns in tables, because the terms don't mean the same thing there).
Note that this is in apparent contradiction to the BOL entry for STUFF() which says: "Deletes a specified length of characters and inserts another set of characters at a specified starting point." However, STUFF() does nothing of the sort as demonstrated by this script:
Declare @a varchar(20), @b-2 varchar(20), @C varchar(20)
Select @a='', @b-2='123456789', @C='xyz'
Select @a as [@a], @b-2 as [@b], @C as [@c]
Select @a as [@a], @b-2 as [@b], @C as [@c]
You will note that the @b-2 string is unchanged by STUFF().
Now if BOL had prefaced that statement so that it read "Returns a string by copying the source string and then Deletes a specified length of characters and inserts another set of characters at a specified starting point." it would be correct, but as it currently stands, BOL is wrong.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]