You may encounter unexpected results when you apply any operators or expressions to the ORDER BY clause of aggregate concatenation queries. The results of aggregate concatenation queries in Microsoft SQL Server depend upon whether or not Transact-SQL expressions are applied to the columns in the ORDER BY clause of the query. For more information or to see an example of a aggregate concatenation query and the behavior exhibited, refer to the "More Information" section of this article.
The correct behavior for an aggregate concatenation query is undefined.
An examination of the SHOWPLAN output of the query reveals that the SQL Server query processor builds an different execution plan when expressions are applied to columns in the query's ORDER BY clause, than when those same expressions are applied to columns in the query's SELECT list. The decision made by the query processor is based on the cost of possible execution plans.
The ANSI SQL-92 specification requires that any column referenced by an ORDER BY clause match the result set, defined by the columns present in the SELECT list. When an expression is applied to a member of an ORDER BY clause, that resulting column is not exposed in the SELECT list, resulting in undefined behavior.
Additionally, the ANSI specifications regarding how an ORDER BY clause is to be applied state that the effect of the ORDER BY should be the same as if you take the entire result set produced by the SELECT list, and then perform the ordering of the table based on those columns in the SELECT list.
In order to achieve the expected results from an aggregate concatenation query, apply any Transact-SQL function or expression to the columns in the SELECT list rather than in the ORDER BY clause.
An aggregate concatenation query is a query that combines the values of multiple rows into one row.
Steps to Reproduce Behavior
Use the following Transact-SQL script to reproduce the behavior.
Note the application of the LTRIM and RTRIM functions to the [C1] column in the ORDER BY clause versus the SELECT list.
IF EXISTS( SELECT * FROM sysobjects WHERE name = 'T1' )
DROP TABLE T1
GO
CREATE TABLE T1( C1 NCHAR(1) )
SET NOCOUNT ON
INSERT T1 VALUES( 'A' )
INSERT T1 VALUES( 'B' )
DECLARE @Str0 VARCHAR(4)
DECLARE @Str1 VARCHAR(4)
DECLARE @Str2 VARCHAR(4)
SET @Str0 = ''
SET @Str1 = ''
SET @Str2 = ''
SELECT @Str0 = @Str0 + C1 FROM T1 ORDER BY C1
SELECT @Str1 = @Str1 + C1 FROM T1 ORDER BY LTRIM( RTRIM( C1 ) )
SELECT @Str2 = @Str2 + LTRIM( RTRIM( C1 ) ) FROM T1 ORDER BY C1
SELECT @Str0 'No functions applied to column.'
SELECT @Str1 'LTRIM() and RTRIM() applied to ORDER BY clause.'
SELECT @Str2 'SELECT list with LTRIM(RTRIM()) (Workaround)'
IF @Str1 <> @Str2
BEGIN
PRINT ''
PRINT 'Execution plan depends on where the functions are applied:'
PRINT '=========================================================='
PRINT ''
SET @Str1 = ''
SET @Str2 = ''
SET STATISTICS PROFILE ON
SELECT @Str1 = @Str1 + C1 FROM T1 ORDER BY LTRIM( RTRIM( C1 ) )
SELECT @Str2 = @Str2 + LTRIM( RTRIM( C1 ) ) FROM T1 ORDER BY C1
SET STATISTICS PROFILE OFF
END
SET NOCOUNT OFF
DROP TABLE T1