THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Paul Nielsen

www.SQLServerBible.com

Multiple Assignment Variables and Order By in SQL Server 2008

A multiple assignment variable, sometimes called an aggregate concatenation, is a fascinating method that appends a variable to itself using a select statement. 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. The primary complaint has been that the sort order of the concatenation is not guaranteed by the SQL statement. Using a multiple assignment variable method depended on the behavior of the engine instead of on the declarative nature of SQL - Horrors to even a non-purist pragmatic minded fellow such as myself.

But, while writing about this technique for SQL Server 2008 Bible, I discovered  (are you sitting down?) that SQL Server 2008 does indeed respond to an order by in a multiple assignment variable query!

The first query has an order by name:

USE AdventureWorks2008;

 

Declare @MAV VARCHAR(max)

 

SELECT @MAV = Coalesce(@MAV + ', ' + Name, Name)

  FROM (select name, DepartmentID from HumanResources.Department) D

  order by name

Select @MAV

 

Result:

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

Changed Name, Document Control, Engineering, Executive, Facilities and Maintenance, Finance, Human Resources, Information Services, Marketing, Production, Production Control, Purchasing, Quality Assurance, Research and Development, Sales, Shipping and Receiving, Test Two, Tool Design

 

Change the order by clause and re-run the query:

 

Declare @MAV VARCHAR(max)

 

SELECT @MAV = Coalesce(@MAV + ', ' + Name, Name)

  FROM (select name, DepartmentID from HumanResources.Department) D

  order by DepartmentID

Select @MAV

Result:

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

Engineering, Tool Design, Sales, Marketing, Purchasing, Research and Development, Production, Production Control, Human Resources, Finance, Information Services, Document Control, Quality Assurance, Facilities and Maintenance, Shipping and Receiving, Executive, Changed Name, Test Two

 

I haven’t tested MAV and order by on SQL Server 2005 for quite some time. I don’t recall it responding to order by in 2005, but I could be wrong. In any case it works great in 2008. If this is a new improvement in 2008, then I sure missed it in the Microsoft presentations.

Published Tuesday, March 17, 2009 11:27 AM by Paul Nielsen

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam Machanic said:

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

March 17, 2009 1:24 PM
 

Paul Nielsen said:

Adam, You could just as easily have said, "That’s cool, now FOR XML PATH is unnecessary."

Both methods have equivalent performance. The developer is free to choose the method that seems most readable to him. Personally, I think it's good for SQL Server to offer more than one way to solve a problem.

March 17, 2009 1:57 PM
 

Alexander Kuznetsov said:

March 17, 2009 1:58 PM
 

AaronBertrand said:

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?

March 17, 2009 2:00 PM
 

Adam Machanic said:

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.

March 17, 2009 2:28 PM
 

Alejandro Mesa said:

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

March 17, 2009 2:41 PM
 

Adam Machanic said:

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.

March 17, 2009 2:53 PM
 

TiborKaraszi said:

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.

March 18, 2009 8:46 AM
 

James Luetkehoelter said:

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.

March 18, 2009 2:03 PM
 

Jared Ko said:

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

March 18, 2009 2:27 PM
 

Paul Nielsen said:

In SQL Server 2008 Bible, I wrote that to use MAV with caution because it's undoccumented. Hugo (gotta love him) corrected me pointing to

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q287515

It's an old KB article - last revision: October 16, 2003

Basically the article documents that it doesn't correctly respond to Order By.

jsut adding some history, fyi.

March 18, 2009 3:41 PM
 

Paul Nielsen said:

Re: the FOR XML PATH method, there are concerns there too. I’m not opposed to FOR XML PATH, but the question should be raised, “Is FOR XML PATH the best possible syntax for an aggregate concatenation solution?”

Sure it works, but take a step back from the code, put on your architect / language designer hat, and ask yourself if an XML addition to SELECT is the best possible way to concatenate rows? FOR XML PATH is pulling from a relational data source, and outputting data, without any XML in the picture. Using FOR XML PATH to concatenate rows basically exploits a nuance of the XML PATH feature to solve a problem that I doubt it was ever intended to solve.

Long term, I’m more interested in seeing the multiple assignment variable method gaining official recognition and support from Microsoft than I am seeing FOR XML PATH gaining more non-XML use. Multiple assignment variable is easier to explain and makes more sense than FOR XML PATH.

March 18, 2009 3:57 PM
 

Alexander Kuznetsov said:

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

March 18, 2009 4:40 PM
 

Paul Nielsen said:

Alexander, Absolutely! - Paul

March 18, 2009 5:30 PM
 

Alejandro Mesa said:

Alex,

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

Cheers,

AMB

March 18, 2009 6:04 PM
 

Greg Linwood said:

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)

March 19, 2009 2:14 AM
 

steve dassin said:

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:)

March 20, 2009 12:12 AM
 

Joe Celko said:

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

March 22, 2009 2:19 PM
 

Alexander Kuznetsov said:

March 28, 2009 8:22 PM
 

Alexander Kuznetsov said:

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

March 29, 2009 2:39 PM
 

Jeff Moden said:

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

May 10, 2009 11:43 AM
 

Itzik Ben-Gan said:

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

February 3, 2010 1:48 PM
 

Jeff Moden said:

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?

May 1, 2011 8:56 PM
 

Praks said:

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

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

:)

September 27, 2011 1:17 AM

Leave a Comment

(required) 
(required) 
Submit

About Paul Nielsen

Paul Nielsen believes SQL is the romance language of data. As such he’s a hands-on database developer, Microsoft SQL Server MVP, trainer, and author of SQL Server Bible series (Wiley). As a data architect, he developed the concepts of Smart Database Design and Nordic – an open source O/R dbms for SQL Server. He lives in Colorado Springs.

This Blog

Syndication

News

news item test
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement