February 8, 2012 at 12:37 am
When using function in order by clause, SQL loses rows.
Why?
It's bug or special behavior?
DECLARE @t TABLE ( name VARCHAR(MAX) )
INSERT INTO @t
VALUES ( 'Joe' ),
( 'Mark' )
--sort -> compute scalar = OK
DECLARE @text1 VARCHAR(MAX) = ''
SELECT @text1 += name + '; '
FROM @t
ORDER BY name
--compute scalar -> sort = NOT OK
DECLARE @text2 VARCHAR(MAX) = ''
SELECT @text2 += name + '; '
FROM @t
ORDER BY LTRIM(name)
--compute scalar -> sort -> compute scalar = OK
DECLARE @text3 VARCHAR(MAX) = ''
SELECT TOP 9223372036854775807
@text3 += name + '; '
FROM @t
ORDER BY LTRIM(name)
SELECT @text1
SELECT @text2
SELECT @text3
February 8, 2012 at 3:30 am
AAYakovenko (2/8/2012)
When using function in order by clause, SQL loses rows.Why?
It's bug or special behavior?
This 'ordered variable concatenation' trick was only supported in a few specific cases for backward compatibility. These days, you cannot rely on it to produce correct results. Use FOR XML PATH instead:
DECLARE @t TABLE
(
name varchar(10) NOT NULL
);
INSERT @t
(name)
VALUES
('Joe'),
('Mark'),
('Frank'),
('Bob');
SELECT
(
SELECT
t.name + '; '
FROM @t AS t
ORDER BY
t.name
FOR XML
PATH (''),
TYPE
).value('(./text())[1]', 'varchar(8000)');
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 8, 2012 at 10:39 pm
Thanks SSChampion.
I know about concat over XML.
I want to know the reason for this behavior stranngo.
February 9, 2012 at 12:19 am
AAYakovenko (2/8/2012)
Thanks SSChampion.I know about concat over XML.
I want to know the reason for this behavior stranngo.
For some reason, the += acts as a = when you use a function in an ORDER BY without using a TOP clause. As Paul said (that's his name, SSChampion is a title given for his point status), this operator cannot be relied on as it can produce unexpected results. You can call it a feature, or a bug, or just downward strange, but it is just the way it is.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 9, 2012 at 1:21 am
AAYakovenko (2/8/2012)
I want to know the reason for this behavior stranngo.
Let me try to explain again. Using variable concatenation in this way was a quirk of (much) older versions of SQL Server that people discovered. It was never an intentional feature, but backwards compatibility was maintained for a time. It has not been updated to work with new += syntax or with ORDER BY expressions allowed now, but not at the time. There is a little more information in this blog post from July 2005:
Ordering Guarantees in SQL Server
It is item #6 on the list. Note this was for backwards compatibility seven years ago.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 9, 2012 at 1:31 am
Koen Verbeeck (2/9/2012)
For some reason, the += acts as a = when you use a function in an ORDER BY without using a TOP clause. As Paul said (that's his name, SSChampion is a title given for his point status), this operator cannot be relied on as it can produce unexpected results. You can call it a feature, or a bug, or just downward strange, but it is just the way it is.
More specifically, the behaviour depends on the order of internal operations and assignments - not all the details of which are exposed in the query plans. I'm not going to say more than that, because it only seems to encourage people to infer even more ordering guarantees that truly do not exist (it's not safe with TOP...ORDER BY). There's really no need to use this any more anyway - we have XML PATH and CLR.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 9, 2012 at 1:51 am
thank you very much!
February 9, 2012 at 3:10 am
SQL Kiwi (2/9/2012)
There's really no need to use this any more anyway - we have XML PATH and CLR.
That is a bit of the problem.
XML PATH and CLR have a slightly higher learning curve than the simple variable concatenation using +=.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 9, 2012 at 3:21 am
Koen Verbeeck (2/9/2012)
SQL Kiwi (2/9/2012)
There's really no need to use this any more anyway - we have XML PATH and CLR.That is a bit of the problem.
XML PATH and CLR have a slightly higher learning curve than the simple variable concatenation using +=.
Better to spend half an hour learning something than to get wrong results though, right? 😉
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 9, 2012 at 3:25 am
SQL Kiwi (2/9/2012)
Koen Verbeeck (2/9/2012)
SQL Kiwi (2/9/2012)
There's really no need to use this any more anyway - we have XML PATH and CLR.That is a bit of the problem.
XML PATH and CLR have a slightly higher learning curve than the simple variable concatenation using +=.
Better to spend half an hour learning something than to get wrong results though, right? 😉
It's not me you need to convince 😀
And unfortunately, in most resources it is not mentioned that += can lead to incorrect results. Hooray for the internet *ironic*
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply