Multiple Assignment Variables and Order By in SQL Server 2008

It's cool and all, but rather unnecessary at this point thanks to FOR XML PATH...

Hi Paul,

I'm using FOR XML PATH these days:

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/06/20/832.aspx

http://pratchev.blogspot.com/2008/03/row-concatenation-with-for-xml-path.html

Agreed, I also stopped using this method once I learned about FOR XML PATH.  I wonder if there were any interesting alternatives to either of these methods in your challenge Adam?

Hi Paul,

Sure, I could have said that, but I wouldn't have meant it <g> -- I can't see any advantages of using the method described herein over FOR XML PATH.  You can use FOR XML PATH in any situation that you can use this method, and in many other situations as well -- subqueries, etc.  Readability is of course entirely subjective but I personally think they're about equal.  Plus, FOR XML PATH is fully documented.  

The one downside of FOR XML PATH is the entitization issue:

SELECT '<' FOR XML PATH ('')

... but we can deal with those easily enough:

SELECT (SELECT '<' FOR XML PATH (''), TYPE).value('.[1]', 'varchar(max)')

Aaron: you'll have to wait for my final writeup to see the methods but the answer is yes; some people played with various pivoting methods, recursive CTEs, and other XML techniques, all with mixed success.

Hi Paul,

The problem arises when you apply any operator or expression to the "order by" clause, like in.

...

ORDER BY RTRIM(LTRIM([Name]));

Did you test this case?

Here is the link to the PRB.

PRB: Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location

http://support.microsoft.com/default.aspx/kb/287515

Cheers,

AMB

Alejandro, good catch.  I just tried that on the examples here and ended up with some very strange results.  FOR XML PATH does indeed honor the ordering.

Thank you for submitting this cool story - Trackback from DotNetShoutout

As far as I can remember the issue has always been that if it isn't documented, then it isn't guaranteed. It has worked in some cases and not worked in some, and it has varioed over versions... So, to me, the question is whether BOL states something differently or not.

Tibor, I think you've got the nail on the head in this discussion. One of the nice (and frustrating) things about most MS products is there is more than one way to accomplish anything. As a general rule of thumb I never use something that isn't documented except for my own personal needs - never with a client, or teaching - never. There are some undocumented features that I've never understood why they weren't included in BOL, but I always work with the assumption that MS has a reason.

It's sort-of documented. This talks about a single assignment, not concatenation. The topic "Transact-SQL Variables" in BOL talks about "Last Row Returned" and states:

If a SELECT statement returns more than one row and the variable references a nonscalar expression, the variable is set to the value returned for the expression in the last row of the result set. For example, in this batch @EmpIDVariable is set to the EmployeeID value of the last row returned, which is 1

Example follows and inludes ORDER BY

USE AdventureWorks;

GO

DECLARE @EmpIDVariable int;

SELECT @EmpIDVariable = EmployeeID

FROM HumanResources.Employee

ORDER BY EmployeeID DESC;

SELECT @EmpIDVariable;

GO

Paul,

I think it would be way more readable and clear to have yet another OLAP function. How about the following explicit syntax:

SELECT ZIP, CONCATENATE(CustomerName)

 OVER(PARTITION BY ZIP ORDER BY CustomerName) AS CustomersList

FROM ...

Sample output:

10104  Aaron Darrett,Bill Zhang

60601  Jill King,Juan Martinez,Zach Nash

Alex,

Nice idea. Let me know when you have filed the connect entry to vote.

Cheers,

AMB

I agree with Paul & Alex. Using XML PATH in SQL language might work even if it's not documented, but there are other considerations & it would be better to have something purpose built such as Alex suggested.

However, the whole topic would be a non issue if SQL Servers cursors had iterator level performance as you'd then just use a cursor (which is what any other technique implies indirectly)

Hello Paul,

I went a few rounds with MS a number of years ago over aggregate concatenation queries (involving the kb you mentioned). You may find the MS response(s) interesting (I was posting under the name Pele then:)

microsoft.public.sqlserver.programming

Mar 7 2003

"transpose problem"

http://tinyurl.com/69lzz

Especially the replies by Bill Hollinshead [MSFT].

microsoft.public.sqlserver.programming

Jan 23 2003

"Undocumented operation"

http://tinyurl.com/6jprz9

Especially the reply by Hal Berenson [MSFT].

The more things change the more they remain the same:)

PingBack from http://www.pythian.com/news/1634/log-buffer-140-a-carnival-of-the-vanities-for-dbas

PingBack from http://www.pythian.com/news/1634/log-buffer-140-a-carnival-of-the-vanities-for-dbas

>> Though nearly undocumented, the method's been around since at least SQL Server 2000. It’s been bashed as a kludge and hack by some and revered as the embodiment of elegance by others.<<

It is a kludge.  Go to the definition: Kludge or kluge:

n. Slang

1) A system, especially a computer system, that is constituted of poorly matched elements or of elements originally intended for other applications.

2) The use of undocumented, unintended, accidental or non-standard features which appear in the software or hardware to solve an immediate problem in a computer system.

3) A clumsy or inelegant solution to a problem.

Sources:

From the old Scottish word "kludgie" meaning an outside toilet; A Scottish engineering term for anything added in an ad hoc manner; the spelling "kludge" adapted by American engineers in World War II.

"How to Design a Kludge", Jackson Granholme, Datamation, February 1962, pp. 30-31], which defined it as "An ill-assorted collection of poorly matching parts, forming a distressing whole."

>> The primary complaint has been that the sort order of the concatenation is not guaranteed by the SQL statement. <<

Perhaps Newbies bitch about their kludge not working, but RM and SQL people are more worried about the more fundamental violation of 1NF. When we defined the interfaces to the X3J languages, the goal was to have a cursor take a result set from the query and map columns to fields.  We wrote conversions for the SQL data types to the host data types.  The assumption of 1NF is that every value is scalar; therefore each conversion is scalar to scalar.  

This let the front end language (present and future) handle the simplest format for file data -- a sequential flat file of scalar values.  No variant records, arrays, lists, trees, no linkages, etc.

Non-RDBMS programmers are used to having their application code and file system tightly coupled.  VERY tightly coupled. COBOL is the best example and it still has most of the commercial applications today.  There was no abstraction or division between display data and storage, which is the basis of Data Bases of any kind.  

I will not even go into the flaws in this accidental implementation of the kludge.  The ORDER BY is part of a cursor,since a table has no ordering (fundamentals, right?).  But once you start having to mimic COBOL scratch tape files in SQL, you have lost parallelism, optimizer choices and all the other advantages of the Relational Model.  

Please vote for the following suggestion:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=427987

String concatenation in SQL is frequently discussed on newsgroups and blogs. Adam Machanic recently posted

Maybe it's service pack issue, but Ordered MAV's work for me in 2k, and 2k5.  So does the Ordered XML method.

Maybe it's not the right thing to do in an RDBMS but, just because it's not ANSI, doesn't mean it's a kludge, Joe. ;-)

Here's an example where this technique fails: http://www.sqlmag.com/Article/ArticleID/103533/103533.html. Not sure that "fails" is the right term here since there were no guarantees as to how it should behave.

I'll post a separate entry to make sure people notice it and hopefully finally refrain from relying on it--at least with the ORDER BY clause.

Cheers,

BG

I'm only a year late finding this post again.  Thanks for the tip, Itzik.  That was quite a surprise.  Have you found any problems using an ORDER BY with XML concatenation?

It was good, but i tried it and found if we are concatenating any delimeter at the end then the order gets distorted, therefore, i added delimeter ',' at the begining and it got sort out with the correct order as mentioned below:

-----------------------

DECLARE @MenuIDs VARCHAR(MAX)

SET @MenuIDs=''

SELECT @MenuIDs= @MenuIDs+ ','+ convert(varchar(20),MD.MenuID)

 FROM MenuMaster MM WITH (NOLOCK)

 INNER JOIN MenuDetails MD WITH(NOLOCK)

ON MI.MenuID =MM.IncludedMenuRights

 WHERE UserId = 123  

 order by MD.MenuLevel

-----------------

:)