SQL SERVER – Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location

I was reading the blog of Ward Pond, and I came across another note of Microsoft. I really found it very interesting. The given explanation was very simple; however, I would like to rewrite it again.

Let us execute the following script. This script inserts two values ‘A’ and ‘B’ in the table and outputs a simple code to concatenate each other to produce the result ‘AB’.

IF EXISTS( SELECT * FROM sysobjects WHERE name = 'T1' )
DROP TABLE T1
GO
CREATE TABLE T1( C1 NCHAR(1)  )
INSERT T1 VALUES( 'A' )
INSERT T1 VALUES( 'B' )
DECLARE @Str0 VARCHAR(4)
SET @Str0 = ''
SELECT @Str0 = @Str0 + C1 FROM T1 ORDER BY C1
SELECT @Str0 AS Result
DROP TABLE T1

The code to concatenating any two string is very simple which is used three times in following example.

IF EXISTS( SELECT * FROM sysobjects WHERE name = 'T1' )
DROP TABLE T1
GO
CREATE TABLE T1( C1 NCHAR(1)  )
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)'
DROP TABLE T1

Resultset of the above query is as follows.

From this result, it is evident that the resultset where function is applied in ORDER BY clause gives the wrong result. When the same function is moved to SELECT clause, it gives the correct result. That is in one way very strange; however, this is how it is defined in SQL Server standard. The behavior of the function in ORDER BY is not defined anywhere in SQL documentation.

The best practice is to avoid the usage of function in ORDER BY clause when string concatenation operations are executed.

The reason for this behavior is that the use of function in ORDER BY clause will change the order of query execution and create an unexpected output.

Let me know if you have any other example for the same or a better explanation.

Reference : Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)