Why STUFF

Question

Why STUFF

Viewing 10 posts - 46 through 54 (of 54 total)

You must be logged in to reply to this topic. Login to reply

Marcia J SSCertifiable Points: 5649 More actions · Answer 1

One advantage of looking at the question a day late. The STUFF had been stuffed in before I looked at the question.

j-1064772 SSCertifiable Points: 5316 More actions · Answer 2

Marcia J (5/5/2016)
One advantage of looking at the question a day late. The STUFF had been stuffed in before I looked at the question.

:-D:-D:-D!

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 3

edwardwill (5/4/2016)
Ed Wagner (5/4/2016)
It came from Wayne Sheffield's SQL Spackle article on string concatenation. The article is at http://www.sqlservercentral.com/articles/comma+separated+list/71700/. It's a great technique.
It's broken so no, it's not great.

What's broken about it?

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Jeff Moden (5/5/2016)
edwardwill (5/4/2016)
Ed Wagner (5/4/2016)
It came from Wayne Sheffield's SQL Spackle article on string concatenation. The article is at http://www.sqlservercentral.com/articles/comma+separated+list/71700/. It's a great technique.
It's broken so no, it's not great.
What's broken about it?

The question was originally missing the STUFF((...),1,1,'').

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

Lynn Pettis (5/5/2016)
Jeff Moden (5/5/2016)
edwardwill (5/4/2016)
Ed Wagner (5/4/2016)
It came from Wayne Sheffield's SQL Spackle article on string concatenation. The article is at http://www.sqlservercentral.com/articles/comma+separated+list/71700/. It's a great technique.
It's broken so no, it's not great.
What's broken about it?
The question was originally missing the STUFF((...),1,1,'').

He intended to say the question was broken, not the technique.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 6

Lynn Pettis (5/4/2016)
Wow, so much work when it isn't needed.

Like I said... very old code... as-in sql 2000... (to me, one of the fine block of code 16 years ago)

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 7

Luis Cazares (5/4/2016)
Raghavendra Mudugal (5/4/2016)
(EP)Z!!!
No need of STUFF. (very old code)
No need for cursors, or several statements.
WITH CTE AS(
select distinct personid from @t
)
SELECT PersonID,
phone = STUFF(( SELECT ', ' + AreaCode + '-' + Exchange + '-' + root
FROM @t t
WHERE t.PersonID = CTE.PersonID
FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 2, '')
FROM CTE;
If you use a Surround With snippet, the keystrokes are reduced by half while the performance improves.
Surround With snippet provided at: http://www.sqlservercentral.com/articles/SSMS/138994/

point of my code is only this- where without STUFF and by using ISNULL we can exclude the extra comma at the beginning.

@Pn = IsNull(@pn + ', ','') + (AreaCode+'-'+Exchange+'-'+ root)

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

Raghavendra Mudugal (5/5/2016)
Luis Cazares (5/4/2016)
Raghavendra Mudugal (5/4/2016)
(EP)Z!!!
No need of STUFF. (very old code)
No need for cursors, or several statements.
WITH CTE AS(
select distinct personid from @t
)
SELECT PersonID,
phone = STUFF(( SELECT ', ' + AreaCode + '-' + Exchange + '-' + root
FROM @t t
WHERE t.PersonID = CTE.PersonID
FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 2, '')
FROM CTE;
If you use a Surround With snippet, the keystrokes are reduced by half while the performance improves.
Surround With snippet provided at: http://www.sqlservercentral.com/articles/SSMS/138994/
point of my code is only this- where without STUFF and by using ISNULL we can exclude the extra comma at the beginning.
@Pn = IsNull(@pn + ', ','') + (AreaCode+'-'+Exchange+'-'+ root)

I don't see any gain on that. You're replacing a function with another function. Not only that, your function would execute for every row instead of once per group. Your option won't guarantee the order of the items in the list, while the FOR XML option is able to do it. I understand that for SQL 2000 it was a good option (it would be better if it didn't use the default options of the cursor), but it's not worth it with the current tools available.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 9

Luis Cazares (5/5/2016)
I understand that for SQL 2000 it was a good option....

That's the whole point. (its not about- performance analysis, gain; just a piece of code written long back to avoid data concatenation with comma; and also why it was easy for me for selecting the right choice).

Thanks for your inputs and ideas, was really helpful.

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 10

Luis Cazares (5/5/2016)
Raghavendra Mudugal (5/5/2016)
Luis Cazares (5/4/2016)
Raghavendra Mudugal (5/4/2016)
(EP)Z!!!
No need of STUFF. (very old code)
No need for cursors, or several statements.
WITH CTE AS(
select distinct personid from @t
)
SELECT PersonID,
phone = STUFF(( SELECT ', ' + AreaCode + '-' + Exchange + '-' + root
FROM @t t
WHERE t.PersonID = CTE.PersonID
FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 2, '')
FROM CTE;
If you use a Surround With snippet, the keystrokes are reduced by half while the performance improves.
Surround With snippet provided at: http://www.sqlservercentral.com/articles/SSMS/138994/
point of my code is only this- where without STUFF and by using ISNULL we can exclude the extra comma at the beginning.
@Pn = IsNull(@pn + ', ','') + (AreaCode+'-'+Exchange+'-'+ root)
I don't see any gain on that. You're replacing a function with another function. Not only that, your function would execute for every row instead of once per group. Your option won't guarantee the order of the items in the list, while the FOR XML option is able to do it. I understand that for SQL 2000 it was a good option (it would be better if it didn't use the default options of the cursor), but it's not worth it with the current tools available.

Actually, it can be very fast and it doesn't suffer the problem of returning multiple copies of a single row like XML can if you don't get the external reference just right. Also remember that the old method doesn't suffer the problem of entitizing reserved characters like XML does, the fix for which (using TYPE) causes XML to slow down a fair bit.

Both methods have their problems so I'd have to say what I always say... "It Depends". 😀

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)