Working with SQL Server can be both challenging and exciting. There are always new concepts and ideas to explore, and it’s fascinating to see how different scenarios can affect the results. In this blog post, we will discuss the Order By clause and its impact on concatenation queries in SQL Server.

Recently, we received an interesting comment from Bob on one of our articles. Bob shared his insights on the topic and provided a similar example to illustrate his point. Let’s take a closer look at his comment and execute his example to see the result set.

Bob emphasized the importance of shifting our perspective from an implementation-centric view to an ANSI point of view. According to ANSI, processing order is not guaranteed. While the execution plan may show sorting, it is implementation-specific and may not reflect the actual order of concatenation.

Bob also highlighted that the ORDER BY clause only pertains to the final results from an ANSI perspective. SQL Server follows ANSI rules and does not guarantee a specific order for string concatenation. Therefore, we cannot expect consistent results when using the construct “varchar = varchar + varchar”.

To further illustrate his point, Bob provided an example:

DECLARE @sum INT
SELECT @sum = 0
SELECT @sum = @sum + num
FROM (
    SELECT 1 [num]
    UNION
    SELECT 2 [num]
) [a]
ORDER BY (num * num)
SELECT @sum [Is it 3]

In this example, the ORDER BY clause does not affect the order of concatenation. Removing the ORDER BY clause results in a different sum. This demonstrates that the construct “@ = @ + col” is non-deterministic and may produce different results for the same input values.

It’s important to note that if you use the construct “@=@ + col” in a table expression (e.g., VIEW, FUNCTION, FROM (SELECT)), you may get the desired results in some cases, but unexpected behavior can occur under certain circumstances.

We appreciate Bob’s excellent explanation and his active participation in the community. It’s through discussions like these that we can deepen our understanding of SQL Server concepts and improve our skills.

In conclusion, the Order By clause and concatenation queries in SQL Server can be complex and may not always produce the expected results. It’s crucial to consider the ANSI perspective and be aware of the non-deterministic nature of certain constructs. By understanding these concepts, we can write more efficient and reliable SQL code.