February 27, 2019 at 9:53 pm
And in another very old thread, Anith Sen listed some conditions on which it can fail:
Using FETCH to populate a variable
And the discussions have a few MVPs involved. And another person who ended up working for MS.
Sue
February 27, 2019 at 11:40 pm
Eirikur Eiriksson - Wednesday, February 27, 2019 9:10 AMJonathan AC Roberts - Wednesday, February 27, 2019 6:56 AMEirikur Eiriksson - Wednesday, February 27, 2019 6:31 AMJonathan AC Roberts - Tuesday, February 26, 2019 5:33 PMOr something even simpler like this should work:DECLARE @Cols NVARCHAR(MAX)=''
SELECT @Cols +=QUOTENAME(c.column_name)+', '
FROM INFORMATION_SCHEMA.columns c
WHERE c.TABLE_NAME='MYTABLENAME'
AND c.TABLE_SCHEMA='dbo'
ORDER BY c.ORDINAL_POSITIONSET @Cols =LEFT(@Cols ,LEN(@Cols)-1);
PRINT @Cols ;Quick thought, it has been stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method, hence the promotion of the FOR XML method. An example would be a parallel execution where the gathering of the streams would not align.
😎I think all that query is doing is concatenating a string with the order specified with ORDER BY. It should be no different to writing a query with an ORDER BY in and displaying results to a grid.
Can you provide any links to "stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method" or an example where this doesn't work?Let me have a look Jonathan, I've had this discussion with Itzik Ben-Gan and few others
😎
It's pretty awesome to have a discussion with legend Itzik Ben-Gan
Saravanan
February 28, 2019 at 2:07 am
Sue_H - Wednesday, February 27, 2019 9:53 PMHere is a very old thread on the subject that I could remember and took me forever to find. Go down to the post by BP Margolin for a great explanation:
how to add description togetherAnd in another very old thread, Anith Sen listed some conditions on which it can fail:
Using FETCH to populate a variableAnd the discussions have a few MVPs involved. And another person who ended up working for MS.
Sue
Thanks Sue,
I also found there was quite a discussion on this article: http://www.sqlservercentral.com/articles/cursors/72538/
February 28, 2019 at 6:37 am
Jonathan AC Roberts - Thursday, February 28, 2019 2:07 AMSue_H - Wednesday, February 27, 2019 9:53 PMHere is a very old thread on the subject that I could remember and took me forever to find. Go down to the post by BP Margolin for a great explanation:
how to add description togetherAnd in another very old thread, Anith Sen listed some conditions on which it can fail:
Using FETCH to populate a variableAnd the discussions have a few MVPs involved. And another person who ended up working for MS.
Sue
Thanks Sue,
I also found there was quite a discussion on this article: http://www.sqlservercentral.com/articles/cursors/72538/
Yup...that's a good one too. There is more information about it related to string concatenation rather than quirky update. It's not an update as you said. It's related to how order by is handled. If you think about it, you're building a string, adding each row value and it's not until the end, after you've built the string, when the order by would come into play. So the issues make sense in that respect. But It always seems like it will work and the odd thing is that it often does and then just suddenly stop working correctly.
Sue
February 28, 2019 at 6:47 am
Sue_H - Thursday, February 28, 2019 6:37 AMJonathan AC Roberts - Thursday, February 28, 2019 2:07 AMSue_H - Wednesday, February 27, 2019 9:53 PMHere is a very old thread on the subject that I could remember and took me forever to find. Go down to the post by BP Margolin for a great explanation:
how to add description togetherAnd in another very old thread, Anith Sen listed some conditions on which it can fail:
Using FETCH to populate a variableAnd the discussions have a few MVPs involved. And another person who ended up working for MS.
Sue
Thanks Sue,
I also found there was quite a discussion on this article: http://www.sqlservercentral.com/articles/cursors/72538/Yup...that's a good one too. There is more information about it related to string concatenation rather than quirky update. It's not an update as you said. It's related to how order by is handled. If you think about it, you're building a string, adding each row value and it's not until the end, after you've built the string, when the order by would come into play. So the issues make sense in that respect. But It always seems like it will work and the odd thing is that it often does and then just suddenly stop working correctly.
Sue
I should have worded that as it's not until the end that an order by would be guaranteed.
We get used to seeing the results a certain way but it's not always guaranteed - similar to how someone newer who queries a table with a clustered index, has no order by, data is always in "order" without an order by. And then suddenly, the results seem scrambled.
Sue
February 28, 2019 at 7:09 am
Sue_H - Thursday, February 28, 2019 6:47 AMSue_H - Thursday, February 28, 2019 6:37 AMJonathan AC Roberts - Thursday, February 28, 2019 2:07 AMSue_H - Wednesday, February 27, 2019 9:53 PMHere is a very old thread on the subject that I could remember and took me forever to find. Go down to the post by BP Margolin for a great explanation:
how to add description togetherAnd in another very old thread, Anith Sen listed some conditions on which it can fail:
Using FETCH to populate a variableAnd the discussions have a few MVPs involved. And another person who ended up working for MS.
Sue
Thanks Sue,
I also found there was quite a discussion on this article: http://www.sqlservercentral.com/articles/cursors/72538/Yup...that's a good one too. There is more information about it related to string concatenation rather than quirky update. It's not an update as you said. It's related to how order by is handled. If you think about it, you're building a string, adding each row value and it's not until the end, after you've built the string, when the order by would come into play. So the issues make sense in that respect. But It always seems like it will work and the odd thing is that it often does and then just suddenly stop working correctly.
Sue
I should have worded that as it's not until the end that an order by would be guaranteed.
We get used to seeing the results a certain way but it's not always guaranteed - similar to how someone newer who queries a table with a clustered index, has no order by, data is always in "order" without an order by. And then suddenly, the results seem scrambled.Sue
It seems to go wrong when you sort on a function, not just a column on the rowset. When you do a SELECT in SSMS to display results in a grid you can use OPTION(FAST 1) and it will display the rows as they are being generated (in the correct order), one might think (I did anyway) that this should be the same when concatenating a string. In Hugo's example:DECLARE @X NVARCHAR(MAX);
SET @X = '';
SELECT @X = @X + 'PRINT ''[' + name + ']'';' + CHAR(13) + CHAR(10) FROM msdb.sys.tables ORDER BY NEWID();
PRINT @X;
Only one row is returned. I'm guessing this must be the optimiser working out that it only needs one value (as this type of query is only documented as being used to set the value of a variable to the value of a column in a row), the order is random, so it just picks one row at random.
February 28, 2019 at 7:40 am
Jonathan AC Roberts - Thursday, February 28, 2019 7:09 AMSue_H - Thursday, February 28, 2019 6:47 AMSue_H - Thursday, February 28, 2019 6:37 AMJonathan AC Roberts - Thursday, February 28, 2019 2:07 AMSue_H - Wednesday, February 27, 2019 9:53 PMHere is a very old thread on the subject that I could remember and took me forever to find. Go down to the post by BP Margolin for a great explanation:
how to add description togetherAnd in another very old thread, Anith Sen listed some conditions on which it can fail:
Using FETCH to populate a variableAnd the discussions have a few MVPs involved. And another person who ended up working for MS.
Sue
Thanks Sue,
I also found there was quite a discussion on this article: http://www.sqlservercentral.com/articles/cursors/72538/Yup...that's a good one too. There is more information about it related to string concatenation rather than quirky update. It's not an update as you said. It's related to how order by is handled. If you think about it, you're building a string, adding each row value and it's not until the end, after you've built the string, when the order by would come into play. So the issues make sense in that respect. But It always seems like it will work and the odd thing is that it often does and then just suddenly stop working correctly.
Sue
I should have worded that as it's not until the end that an order by would be guaranteed.
We get used to seeing the results a certain way but it's not always guaranteed - similar to how someone newer who queries a table with a clustered index, has no order by, data is always in "order" without an order by. And then suddenly, the results seem scrambled.Sue
It seems to go wrong when you sort on a function, not just a column on the rowset. When you do a SELECT in SSMS to display results in a grid you can use OPTION(FAST 1) and it will display the rows as they are being generated (in the correct order), one might think (I did anyway) that this should be the same when concatenating a string. In Hugo's example:
DECLARE @X NVARCHAR(MAX);
SET @X = '';
SELECT @X = @X + 'PRINT ''[' + name + ']'';' + CHAR(13) + CHAR(10) FROM msdb.sys.tables ORDER BY NEWID();
PRINT @X;
Only one row is returned. I'm guessing this must be the optimiser working out that it only needs one value (as this type of query is only documented as being used to set the value of a variable to the value of a column in a row), the order is random, so it just picks one row at random.
Different functions, expressions in the order by can often mess things up - often getting just the one row. Try this one in your Adventure Works - with and without the order by: DECLARE @STR VARCHAR(max)
SET @STR = ''
SELECT @STR = @STR + Name + ','
FROM AdventureWorks.Purchasing.Vendor
ORDER BY LEFT(AccountNumber, 2)
PRINT @STR
February 28, 2019 at 7:54 am
Sue_H - Thursday, February 28, 2019 7:40 AMJonathan AC Roberts - Thursday, February 28, 2019 7:09 AMSue_H - Thursday, February 28, 2019 6:47 AMSue_H - Thursday, February 28, 2019 6:37 AMJonathan AC Roberts - Thursday, February 28, 2019 2:07 AMSue_H - Wednesday, February 27, 2019 9:53 PMHere is a very old thread on the subject that I could remember and took me forever to find. Go down to the post by BP Margolin for a great explanation:
how to add description togetherAnd in another very old thread, Anith Sen listed some conditions on which it can fail:
Using FETCH to populate a variableAnd the discussions have a few MVPs involved. And another person who ended up working for MS.
Sue
Thanks Sue,
I also found there was quite a discussion on this article: http://www.sqlservercentral.com/articles/cursors/72538/Yup...that's a good one too. There is more information about it related to string concatenation rather than quirky update. It's not an update as you said. It's related to how order by is handled. If you think about it, you're building a string, adding each row value and it's not until the end, after you've built the string, when the order by would come into play. So the issues make sense in that respect. But It always seems like it will work and the odd thing is that it often does and then just suddenly stop working correctly.
Sue
I should have worded that as it's not until the end that an order by would be guaranteed.
We get used to seeing the results a certain way but it's not always guaranteed - similar to how someone newer who queries a table with a clustered index, has no order by, data is always in "order" without an order by. And then suddenly, the results seem scrambled.Sue
It seems to go wrong when you sort on a function, not just a column on the rowset. When you do a SELECT in SSMS to display results in a grid you can use OPTION(FAST 1) and it will display the rows as they are being generated (in the correct order), one might think (I did anyway) that this should be the same when concatenating a string. In Hugo's example:
DECLARE @X NVARCHAR(MAX);
SET @X = '';
SELECT @X = @X + 'PRINT ''[' + name + ']'';' + CHAR(13) + CHAR(10) FROM msdb.sys.tables ORDER BY NEWID();
PRINT @X;
Only one row is returned. I'm guessing this must be the optimiser working out that it only needs one value (as this type of query is only documented as being used to set the value of a variable to the value of a column in a row), the order is random, so it just picks one row at random.Different functions, expressions in the order by can often mess things up - often getting just the one row. Try this one in your Adventure Works - with and without the order by:
DECLARE @STR VARCHAR(max)
SET @STR = ''
SELECT @STR = @STR + Name + ','
FROM AdventureWorks.Purchasing.Vendor
ORDER BY LEFT(AccountNumber, 2)
PRINT @STR
Thanks, I don't have AdventureWorks on my machine but I tried it on another table and yes, it seems to be set up to only to the extent as documented in the BOL. That statement just sets the result to the last row that would be returned from the query.
February 28, 2019 at 9:54 am
Jonathan AC Roberts - Thursday, February 28, 2019 7:54 AMSue_H - Thursday, February 28, 2019 7:40 AMJonathan AC Roberts - Thursday, February 28, 2019 7:09 AMSue_H - Thursday, February 28, 2019 6:47 AMSue_H - Thursday, February 28, 2019 6:37 AMJonathan AC Roberts - Thursday, February 28, 2019 2:07 AMSue_H - Wednesday, February 27, 2019 9:53 PMHere is a very old thread on the subject that I could remember and took me forever to find. Go down to the post by BP Margolin for a great explanation:
how to add description togetherAnd in another very old thread, Anith Sen listed some conditions on which it can fail:
Using FETCH to populate a variableAnd the discussions have a few MVPs involved. And another person who ended up working for MS.
Sue
Thanks Sue,
I also found there was quite a discussion on this article: http://www.sqlservercentral.com/articles/cursors/72538/Yup...that's a good one too. There is more information about it related to string concatenation rather than quirky update. It's not an update as you said. It's related to how order by is handled. If you think about it, you're building a string, adding each row value and it's not until the end, after you've built the string, when the order by would come into play. So the issues make sense in that respect. But It always seems like it will work and the odd thing is that it often does and then just suddenly stop working correctly.
Sue
I should have worded that as it's not until the end that an order by would be guaranteed.
We get used to seeing the results a certain way but it's not always guaranteed - similar to how someone newer who queries a table with a clustered index, has no order by, data is always in "order" without an order by. And then suddenly, the results seem scrambled.Sue
It seems to go wrong when you sort on a function, not just a column on the rowset. When you do a SELECT in SSMS to display results in a grid you can use OPTION(FAST 1) and it will display the rows as they are being generated (in the correct order), one might think (I did anyway) that this should be the same when concatenating a string. In Hugo's example:
DECLARE @X NVARCHAR(MAX);
SET @X = '';
SELECT @X = @X + 'PRINT ''[' + name + ']'';' + CHAR(13) + CHAR(10) FROM msdb.sys.tables ORDER BY NEWID();
PRINT @X;
Only one row is returned. I'm guessing this must be the optimiser working out that it only needs one value (as this type of query is only documented as being used to set the value of a variable to the value of a column in a row), the order is random, so it just picks one row at random.Different functions, expressions in the order by can often mess things up - often getting just the one row. Try this one in your Adventure Works - with and without the order by:
DECLARE @STR VARCHAR(max)
SET @STR = ''
SELECT @STR = @STR + Name + ','
FROM AdventureWorks.Purchasing.Vendor
ORDER BY LEFT(AccountNumber, 2)
PRINT @STRThanks, I don't have AdventureWorks on my machine but I tried it on another table and yes, it seems to be set up to only to the extent as documented in the BOL. That statement just sets the result to the last row that would be returned from the query.
There are so many weird things that come into play. There used to be a couple of Microsoft articles about it but I have no idea where they went. I just searched on what I remember about one title and didn't find the article but quite a few articles related to this issue come up. I searched on:
Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location
That used to be a KB article and there were others that have been lost in the midst of the changes with the articles. But Hugo summed it up in that thread you found - the behavior is undefined. Martin Smith has a good post on it in this thread:
nvarchar concatenation / index / nvarchar(max) inexplicable behavior
Sue
March 1, 2019 at 1:33 pm
Thank you everyone for all of your suggestions! I took out the DISTINCT and the ORDER BY and the temp table and it was much simpler and works beautifully. All of you are Gods among SQL Programmers! 😉
February 26, 2020 at 7:29 pm
I was able to fake it by using TOP and ORDERY BY at the end. I used TOP 10000 just to ensure that all columns ( I have far less) are included in the result. because the error says:
"The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified."
like this
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', a.' + QUOTENAME(pymt_date)
FROM ( select distinct top 10000 Dateadd(d,15-DATEPART(d,pymt_date),pymt_date) pymt_date from #temp2 order by 1
) AS x;
print @columns
works well so far for me.
February 26, 2020 at 10:21 pm
Jonathan AC Roberts - Thursday, February 28, 2019 7:54 AMSue_H - Thursday, February 28, 2019 7:40 AMJonathan AC Roberts - Thursday, February 28, 2019 7:09 AMSue_H - Thursday, February 28, 2019 6:47 AMSue_H - Thursday, February 28, 2019 6:37 AMJonathan AC Roberts - Thursday, February 28, 2019 2:07 AMSue_H - Wednesday, February 27, 2019 9:53 PMHere is a very old thread on the subject that I could remember and took me forever to find. Go down to the post by BP Margolin for a great explanation:how to add description together
And in another very old thread, Anith Sen listed some conditions on which it can fail:
Using FETCH to populate a variable
And the discussions have a few MVPs involved. And another person who ended up working for MS.
Sue
Thanks Sue,
I also found there was quite a discussion on this article: http://www.sqlservercentral.com/articles/cursors/72538/
Yup...that's a good one too. There is more information about it related to string concatenation rather than quirky update. It's not an update as you said. It's related to how order by is handled. If you think about it, you're building a string, adding each row value and it's not until the end, after you've built the string, when the order by would come into play. So the issues make sense in that respect. But It always seems like it will work and the odd thing is that it often does and then just suddenly stop working correctly.
Sue
I should have worded that as it's not until the end that an order by would be guaranteed.
We get used to seeing the results a certain way but it's not always guaranteed - similar to how someone newer who queries a table with a clustered index, has no order by, data is always in "order" without an order by. And then suddenly, the results seem scrambled.
Sue
It seems to go wrong when you sort on a function, not just a column on the rowset. When you do a SELECT in SSMS to display results in a grid you can use OPTION(FAST 1) and it will display the rows as they are being generated (in the correct order), one might think (I did anyway) that this should be the same when concatenating a string. In Hugo's example:
DECLARE @X NVARCHAR(MAX);SET @X = '';
SELECT @X = @X + 'PRINT ''[' + name + ']'';' + CHAR(13) + CHAR(10) FROM msdb.sys.tables ORDER BY NEWID();
PRINT @X;
Only one row is returned. I'm guessing this must be the optimiser working out that it only needs one value (as this type of query is only documented as being used to set the value of a variable to the value of a column in a row), the order is random, so it just picks one row at random.
Different functions, expressions in the order by can often mess things up - often getting just the one row. Try this one in your Adventure Works - with and without the order by:
DECLARE @STR VARCHAR(max)SET @STR = ''
SELECT @STR = @STR + Name + ','
FROM AdventureWorks.Purchasing.Vendor
ORDER BY LEFT(AccountNumber, 2)
PRINT @STR
Thanks, I don't have AdventureWorks on my machine but I tried it on another table and yes, it seems to be set up to only to the extent as documented in the BOL. That statement just sets the result to the last row that would be returned from the query.
There are so many weird things that come into play. There used to be a couple of Microsoft articles about it but I have no idea where they went. I just searched on what I remember about one title and didn't find the article but quite a few articles related to this issue come up. I searched on:
Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location
That used to be a KB article and there were others that have been lost in the midst of the changes with the articles. But Hugo summed it up in that thread you found - the behavior is undefined. Martin Smith has a good post on it in this thread:
nvarchar concatenation / index / nvarchar(max) inexplicable behavior
Sue
You also should try that on SQL Server 2019 - I recently saw that someone using this technique to populate a variable with the 'last' value found that instead it was now populating the 'first' value returned. I believe it turned out that the ORDER BY without ASC or DESC seems to default to DESC in that scenario, thus returning the 'first' value based on the order.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply