May 10, 2009 at 6:45 am
Paul White (5/10/2009)
GilaMonster (5/10/2009)
You are correct in that conclusion.Can you provide anything authoritative to support that?
I am intrigued.
Itzik Ben-Gan mentioned it during his precon at PASS last year. He also mentioned that the other T-SQL trick often used (select @var = @var +col from tbl) was never intended to work on multiple rows and is a hack, and one that does not always work. If fact, Barry (iirc) had a case here some time back where it didn't work
If you want an authoritative article, I'll ask and see if anyone on the dev team (query processor I'm guessing would be best) will blog on it. Other than dev team, anyone else who you would consider authoritative enough?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 10, 2009 at 6:46 am
Paul White (5/10/2009)
Barry,[Brief comments on the first of your two replies]...
It is true that you don't have to convert back from XML if you don't want to, but then it might not work properly! :pinch:
Actually my point was that you don't have to convert the produced string TO XML in the first place. It really is just a string concatenater & formatter first, that later on got the ability to do a CAST as XML. Oddly, this fixes the invalid characters issue, but leaves the entitization problem.
Given the limitations of FOR XML, I even prefer Paul Nielsen's approach! At least it doesn't die horribly if the wrong character is found in the input - whatever its other faults.
Paul Nielsen's approach is just the SELECT Pseudocursor method which I mentioned before. That's been around and has worked since the SyBase days (but was broken in SQL 2000, which MS sort of acknowledged as a bug). The problem with it is that it's O(n2), so even the highest overhead XML method, that handles every functional problem mentioned, will beat it silly on scale.
Further, the SELECT pseudocursor is extremely limited in application: you cannot use it in subqueries, CTE's or any other kind if derived table and there is no practical way to make it useful in GROUP BY aggregation.
[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]
May 10, 2009 at 6:48 am
Paul White (5/10/2009)
RBarryYoung (5/9/2009)
I am talking only about Adam Machanics trick to get around the 8K local context serialization limit for SQL CLR aggregate functions in SQL Server 2005. That would crash ANY time I allowed it to be used in an execution plan with any parallelism. Adam's "trick" always looked very suspect to me and I am convinced that it just isn't safe.Hey Barry,
Any chance of a link to that code? I would love to take a look!
Paul
Unfortunately it's only is his book, I have never seen it on the web. Further, the example in the book is incomplete (non-working) so I had to finish it myself, and I translated it from C# to VB which I am more proficient in, so I may have misunderstood or introduced mistakes of my own. I'll try and post my attempts later today.
[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]
May 10, 2009 at 8:05 am
GilaMonster (5/10/2009)
Itzik Ben-Gan mentioned it during his precon at PASS last year. He also mentioned that the other T-SQL trick often used (select @var = @var +col from tbl) was never intended to work on multiple rows and is a hack, and one that does not always work. If fact, Barry (iirc) had a case here some time back where it didn't workIf you want an authoritative article, I'll ask and see if anyone on the dev team (query processor I'm guessing would be best) will blog on it. Other than dev team, anyone else who you would consider authoritative enough?
No that'll do. I was just hoping there might be something published somewhere with some more background info. Would have been an interesting read.
May 10, 2009 at 8:16 am
RBarryYoung (5/10/2009)
Actually my point was that you don't have to convert the produced string TO XML in the first place. It really is just a string concatenater & formatter first, that later on got the ability to do a CAST as XML. Oddly, this fixes the invalid characters issue, but leaves the entitization problem.
Cool, but the difference between formatting a string as XML, and making it an XML type doesn't help the fact that it is impossible at all to solve both the entitization and non-printable characters at the same time - in a non-hideous way, I mean.
RBarryYoung (5/10/2009)
Paul Nielsen's approach is just the SELECT Pseudocursor method which I mentioned before. That's been around and has worked since the SyBase days (but was broken in SQL 2000, which MS sort of acknowledged as a bug). The problem with it is that it's O(n2), so even the highest overhead XML method, that handles every functional problem mentioned, will beat it silly on scale.
I was attempting to highlight the fact that ORDER BY appears to work on SQL2K8. Sure it may be slower, but it is at least resilient to ampersands and non-printable characters. His approach is only really unreliable (and slow rather than broken) if the ORDER BY trick doesn't work. I take the point about it being undocumented and liable to change of course.
RBarryYoung (5/10/2009)
Further, the SELECT pseudocursor is extremely limited in application: you cannot use it in subqueries, CTE's or any other kind if derived table and there is no practical way to make it useful in GROUP BY aggregation.
Also true. This is why I think a CLR aggregate solution using an additional explicit ordering parameter is the best solution out of the three, all things considered.
May 10, 2009 at 8:20 am
RBarryYoung (5/10/2009)
Unfortunately it's only is his book, I have never seen it on the web. Further, the example in the book is incomplete (non-working) so I had to finish it myself, and I translated it from C# to VB which I am more proficient in, so I may have misunderstood or introduced mistakes of my own. I'll try and post my attempts later today.
Oh ok - I hadn't realized it was in a book. I wouldn't want you to get into copyright trouble by posting it. I'm no lawyer, but posting a modified version might be dodgy too. I'm happy to have a go at it myself, or maybe even to resort to buying the book - which one is it, and is it any good?
May 10, 2009 at 9:23 am
Paul White (5/10/2009)
Given the limitations of FOR XML, I even prefer Paul Nielsen's approach! At least it doesn't die horribly if the wrong character is found in the input - whatever its other faults.
I wonder what the heck Paul Nielsen is actually talking about. ORDER BY has worked correctly for MAV's for a very long time. I don't have SQL Server 6.5 or 7 to prove it anymore, but here's the code for 2k..
[font="Courier New"]DECLARE @MAV VARCHAR(8000)
--===== Sort by description
SELECT @MAV = ISNULL(@MAV+', ','') + RTRIM(TerritoryDescription)
FROM dbo.Territories
ORDER BY TerritoryDescription
SELECT @MAV
SELECT @MAV = NULL
--===== Sort by ID
SELECT @MAV = ISNULL(@MAV+', ','') + RTRIM(TerritoryDescription)
FROM dbo.Territories
ORDER BY TerritoryID
SELECT @MAV[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
May 10, 2009 at 9:32 am
I don't get it... maybe it was a service pack or something, but even Paul N's odd code produces the correct ordered MAV in 2k5. What the heck are they talking about ORDER BY not working on an MAV?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 10, 2009 at 10:04 am
GilaMonster (5/10/2009)
Itzik Ben-Gan mentioned it during his precon at PASS last year. He also mentioned that the other T-SQL trick often used (select @var = @var +col from tbl) was never intended to work on multiple rows and is a hack, and one that does not always work.
I've never seen it fail and would love to see any example where it does.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 10, 2009 at 10:34 am
Paul White (5/10/2009)
RBarryYoung (5/10/2009)
Actually my point was that you don't have to convert the produced string TO XML in the first place. It really is just a string concatenater & formatter first, that later on got the ability to do a CAST as XML. Oddly, this fixes the invalid characters issue, but leaves the entitization problem.Cool, but the difference between formatting a string as XML, and making it an XML type doesn't help the fact that it is impossible at all to solve both the entitization and non-printable characters at the same time - in a non-hideous way, I mean.
Well there's hideous and there's hideous. 🙂 Yes, right now, the only sure ways I have to fix both are pretty hideous (nested REPLACE of the entitizations seems best so far), but I think that there is a way that is only about half as hideous. Maybe.
RBarryYoung (5/10/2009)
Paul Nielsen's approach is just the SELECT Pseudocursor method which I mentioned before. That's been around and has worked since the SyBase days (but was broken in SQL 2000, which MS sort of acknowledged as a bug). The problem with it is that it's O(n2), so even the highest overhead XML method, that handles every functional problem mentioned, will beat it silly on scale.I was attempting to highlight the fact that ORDER BY appears to work on SQL2K8. Sure it may be slower, but it is at least resilient to ampersands and non-printable characters. His approach is only really unreliable (and slow rather than broken) if the ORDER BY trick doesn't work. I take the point about it being undocumented and liable to change of course.
I was a little unclear here in my point, which is that the ORDER BY (NOTE: for SELECT only!) has worked since forever (back to Sybase), except that there was a bug in SQL 2000 so that sometimes it did not.
And although MS has been very ambivalent about this feature, IMO it is documented in BOL (including ORDER BY) as working and is an intentional feature carried over from Sybase, otherwise, why the heck did they fix it in for 2005 after it was broken in 2000?
RBarryYoung (5/10/2009)
Further, the SELECT pseudocursor is extremely limited in application: you cannot use it in subqueries, CTE's or any other kind if derived table and there is no practical way to make it useful in GROUP BY aggregation.Also true. This is why I think a CLR aggregate solution using an additional explicit ordering parameter is the best solution out of the three, all things considered.
I certainly would like to have that. Still for 2005, we are pretty much stuck with FOR XML as the only general purpose linear scaling solution for string aggregation. Ugly, hideous, warts and all, it's still the best solution.
[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]
May 10, 2009 at 11:03 am
Jeff Moden (5/10/2009)
GilaMonster (5/10/2009)
Itzik Ben-Gan mentioned it during his precon at PASS last year. He also mentioned that the other T-SQL trick often used (select @var = @var +col from tbl) was never intended to work on multiple rows and is a hack, and one that does not always work.I've never seen it fail and would love to see any example where it does.
Hmm, it's odd, I thought that you were involved in the discussion about that case Gail attributed to me (I think that I was just commenting or referring to someone else's though). Anyway, I have seen it reported several times (at least once here at SSC), it's basically a bug in SQL Server 2000 related to parallelism and there's an article on MSDN or TechNet detailing it.
[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]
May 10, 2009 at 12:19 pm
Paul White (5/10/2009)
RBarryYoung (5/10/2009)
Unfortunately it's only is his book, I have never seen it on the web. Further, the example in the book is incomplete (non-working) so I had to finish it myself, and I translated it from C# to VB which I am more proficient in, so I may have misunderstood or introduced mistakes of my own. I'll try and post my attempts later today.Oh ok - I hadn't realized it was in a book. I wouldn't want you to get into copyright trouble by posting it. I'm no lawyer, but posting a modified version might be dodgy too. I'm happy to have a go at it myself, or maybe even to resort to buying the book - which one is it, and is it any good?
The book is Expert SQL Server 2005 Development, the link will take you to the Amazon listing. At Amazon, use the "Look Inside" and search for "aggregates", the relevant material is on pages 162-167 (it will skip page 165, copyright protection, I assume). Or, if you like what you see, just order the book. 🙂
[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]
May 10, 2009 at 2:43 pm
RBarryYoung (5/10/2009)
Jeff Moden (5/10/2009)
GilaMonster (5/10/2009)
Itzik Ben-Gan mentioned it during his precon at PASS last year. He also mentioned that the other T-SQL trick often used (select @var = @var +col from tbl) was never intended to work on multiple rows and is a hack, and one that does not always work.I've never seen it fail and would love to see any example where it does.
Hmm, it's odd, I thought that you were involved in the discussion about that case Gail attributed to me (I think that I was just commenting or referring to someone else's though). Anyway, I have seen it reported several times (at least once here at SSC), it's basically a bug in SQL Server 2000 related to parallelism and there's an article on MSDN or TechNet detailing it.
Heh... Yep... and we fixed it there, didn't we? So, let me make the statement more correctly... When used properly, it does not fail. But no one ever says that. They just bad mouth a very useful technique and they're on their way never mentioning the proper way to use it.
There was also a similar bug due to parallism that kept update from recognizing WHERE somecolumn IS NULL. The fix for that was not to stop using Update or an IS NULL criteria (which is also a useful technique)... it was to use Option(Maxdop 1) until they came out with the fix which, if I remember correctly, was included in sp-3a and also available as a separate hotfix.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 10, 2009 at 3:03 pm
RBarryYoung (5/10/2009)
why the heck did they fix it in for 2005 after it was broken in 2000?
Because people use it. It wasn't intended to be used for concatenating a table together (from what I've heard), but because people use it that way the bugs got fixed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 10, 2009 at 3:36 pm
Jeff Moden (5/10/2009)
RBarryYoung (5/10/2009)
Jeff Moden (5/10/2009)
GilaMonster (5/10/2009)
Itzik Ben-Gan mentioned it during his precon at PASS last year. He also mentioned that the other T-SQL trick often used (select @var = @var +col from tbl) was never intended to work on multiple rows and is a hack, and one that does not always work.I've never seen it fail and would love to see any example where it does.
Hmm, it's odd, I thought that you were involved in the discussion about that case Gail attributed to me (I think that I was just commenting or referring to someone else's though). Anyway, I have seen it reported several times (at least once here at SSC), it's basically a bug in SQL Server 2000 related to parallelism and there's an article on MSDN or TechNet detailing it.
Heh... Yep... and we fixed it there, didn't we?
Not as I recall. My recollection is that we came up with an alternate way to do it.
[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]
Viewing 15 posts - 4,216 through 4,230 (of 66,749 total)
You must be logged in to reply to this topic. Login to reply