While browsing through various SQL Server blogs, I stumbled upon an interesting note by Microsoft that I would like to share with you. The concept discussed in the note is quite simple, but I believe it’s worth revisiting. Let’s take a look at the following script:

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 above script inserts two values, ‘A’ and ‘B’, into a table and then concatenates them to produce the result ‘AB’. The code for concatenating any two strings is quite simple and is used three times in the example.

Now, let’s consider another variation of the script:

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

In this variation, we apply different functions to the ORDER BY clause and observe the results. The resultset of the above query shows that when a function is applied in the ORDER BY clause, it can give incorrect results. However, when the same function is moved to the SELECT clause, it produces the correct result. This behavior may seem strange, but it is defined in the SQL Server standard.

The reason for this behavior is that using a function in the ORDER BY clause can change the order of query execution and create unexpected output. Therefore, it is considered a best practice to avoid using functions in the ORDER BY clause when performing string concatenation operations.

I hope this explanation helps you understand the concept of SQL Server string concatenation better. If you have any other examples or need further clarification, please let me know.