December 17, 2018 at 9:47 am
Wow, didn't know the article was going to generate a fire-storm on TSQL syntax. The original purpose was to see if there was a difference getting max/min value of a column using the TOP or MAX/MIN functions. I agree that ORDER BY 1 (first column in result set) should not be used in production -- but that was not the purpose of the article. What I didn't anticipate is all the other query plan operators needed to satisfy the overall particular query syntax. Normally I am very strict in my TSQL syntax for going into production, but obviously more lax in ad hoc queries. Thanks everyone for your replies.
Mike Byrd
December 17, 2018 at 9:54 am
Mike Byrd - Monday, December 17, 2018 9:47 AMWow, didn't know the article was going to generate a fire-storm on TSQL syntax. The original purpose was to see if there was a difference getting max/min value of a column using the TOP or MAX/MIN functions. I agree that ORDER BY 1 (first column in result set) should not be used in production -- but that was not the purpose of the article. What I didn't anticipate is all the other query plan operators needed to satisfy the overall particular query syntax. Normally I am very strict in my TSQL syntax for going into production, but obviously more lax in ad hoc queries. Thanks everyone for your replies.
The methods are less important than the underlying structure, statistics and cardinality. I've tested this in the passed and found that when the structure is correctly optimized, there is hardly any difference. In an un-optimized situation, the difference can potentially be huge, but then again, that's like teaching someone to swim with concrete boots!
😎
December 17, 2018 at 10:09 am
Mike Byrd - Monday, December 17, 2018 9:47 AMWow, didn't know the article was going to generate a fire-storm on TSQL syntax. The original purpose was to see if there was a difference getting max/min value of a column using the TOP or MAX/MIN functions. I agree that ORDER BY 1 (first column in result set) should not be used in production -- but that was not the purpose of the article. What I didn't anticipate is all the other query plan operators needed to satisfy the overall particular query syntax. Normally I am very strict in my TSQL syntax for going into production, but obviously more lax in ad hoc queries. Thanks everyone for your replies.
Any time you invoke nonstandard syntax, you can probably expect a fire-storm. The ordinal position reference syntax in the ORDER BY clause was last seen in the ANSI SQL-92 standard and was removed in ISO 9075:1999. Microsoft has kept it around, but I wouldn't rely on it.
December 17, 2018 at 10:15 am
Mike C - Monday, December 17, 2018 10:09 AMMike Byrd - Monday, December 17, 2018 9:47 AMWow, didn't know the article was going to generate a fire-storm on TSQL syntax. The original purpose was to see if there was a difference getting max/min value of a column using the TOP or MAX/MIN functions. I agree that ORDER BY 1 (first column in result set) should not be used in production -- but that was not the purpose of the article. What I didn't anticipate is all the other query plan operators needed to satisfy the overall particular query syntax. Normally I am very strict in my TSQL syntax for going into production, but obviously more lax in ad hoc queries. Thanks everyone for your replies.Any time you invoke nonstandard syntax, you can probably expect a fire-storm. The ordinal position reference syntax in the ORDER BY clause was last seen in the ANSI SQL-92 standard and was removed in ISO 9075:1999. Microsoft has kept it around, but I wouldn't rely on it.
Order by # is a subject to deprecation, do not use it!
😎
December 17, 2018 at 10:46 am
thisisfutile - Monday, December 17, 2018 9:01 AMthisisfutile - Monday, December 17, 2018 8:59 AMgotqn - Monday, December 17, 2018 12:03 AMThe queries are not the same. You are sorting by constant and you should order sort by the id in order to get the same results, right?He's not going for the same queries, he's going for the same results and then trying to see what's faster/slower. To use "TOP" and guarantee the minimum or maximum of that column you must use an ORDER BY clause.
Here's Microsoft's "Best Practices" section:
In a SELECT statement, always use an ORDER BY clause with the TOP clause. This is the only way to predictably indicate which rows are affected by TOPHTH
Oh wait, after posting this, I'm starting to think you don't understand what "order by 1" means...that simply means order by column 1.
:):D
True, I was so sure that he is ordering by constant like in window function when one is using `SELECT 1`. For example ROW_NUMBER OVER (ORDER BY (SELECT 1)) ...
Just forgot for a moment this legacy ordering by using the number of the column instead the alias ... xax
December 17, 2018 at 10:51 am
Eirikur Eiriksson - Monday, December 17, 2018 10:15 AMMike C - Monday, December 17, 2018 10:09 AMMike Byrd - Monday, December 17, 2018 9:47 AMWow, didn't know the article was going to generate a fire-storm on TSQL syntax. The original purpose was to see if there was a difference getting max/min value of a column using the TOP or MAX/MIN functions. I agree that ORDER BY 1 (first column in result set) should not be used in production -- but that was not the purpose of the article. What I didn't anticipate is all the other query plan operators needed to satisfy the overall particular query syntax. Normally I am very strict in my TSQL syntax for going into production, but obviously more lax in ad hoc queries. Thanks everyone for your replies.Any time you invoke nonstandard syntax, you can probably expect a fire-storm. The ordinal position reference syntax in the ORDER BY clause was last seen in the ANSI SQL-92 standard and was removed in ISO 9075:1999. Microsoft has kept it around, but I wouldn't rely on it.
Order by # is a subject to deprecation, do not use it!
😎
I haven't seen it on a deprecation list yet, but I agree they will probably get rid of it at some point.
December 18, 2018 at 4:56 am
Just tried:
select EntityID, secondcol, thirdcol
from dbo.Entity
order by 1+2
Doesn't order by thirdcol - gives a syntax error. So the number (1) is an alias for "first column"; it is not parsed as an integer.
http://90.212.51.111 domain
December 18, 2018 at 10:20 am
neil-560592 - Tuesday, December 18, 2018 4:56 AMJust tried:select EntityID, secondcol, thirdcol
from dbo.Entity
order by 1+2Doesn't order by thirdcol - gives a syntax error. So the number (1) is an alias for "first column"; it is not parsed as an integer.
It isn't an alias, it is the ordinal position of columns in the result set. It is a carry over from sysbase and is really dreadful in a real world scenario.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 18, 2018 at 1:12 pm
Sean Lange - Tuesday, December 18, 2018 10:20 AMneil-560592 - Tuesday, December 18, 2018 4:56 AMJust tried:select EntityID, secondcol, thirdcol
from dbo.Entity
order by 1+2Doesn't order by thirdcol - gives a syntax error. So the number (1) is an alias for "first column"; it is not parsed as an integer.
It isn't an alias, it is the ordinal position of columns in the result set. It is a carry over from sysbase and is really dreadful in a real world scenario.
But it's great for quick "what if" and "proof of principle" code where the order and column names may change a lot or not even exist. My hopes that they never get rid of it are dashed because, IIRC, they've deprecated its use.
If MS keeps deprecating useful tools like this one, they'll eventually make SQL Server "idiot proof" and then only idiots will use it. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
December 18, 2018 at 1:18 pm
Jeff Moden - Tuesday, December 18, 2018 1:12 PMSean Lange - Tuesday, December 18, 2018 10:20 AMneil-560592 - Tuesday, December 18, 2018 4:56 AMJust tried:select EntityID, secondcol, thirdcol
from dbo.Entity
order by 1+2Doesn't order by thirdcol - gives a syntax error. So the number (1) is an alias for "first column"; it is not parsed as an integer.
It isn't an alias, it is the ordinal position of columns in the result set. It is a carry over from sysbase and is really dreadful in a real world scenario.
But it's great for quick "what if" and "proof of principle" code where the order and column names may change a lot or not even exist. My hopes that they never get rid of it are dashed because, IIRC, they've deprecated its use.
If MS keeps deprecating useful tools like this one, they'll eventually make SQL Server "idiot proof" and then only idiots will use it. 😀
I have never seen it on an official deprecation list. In fact as a result of this thread I was digging and still don't see it. I agree that it is useful for development from time to time but not something I really use very often. I usually think in my head that I want to order by a column, then oh it doesn't matter which column so before I have a chance to think of using 1 I have already typed in the primary key of the main table in the query. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 18, 2018 at 2:09 pm
Sean Lange - Tuesday, December 18, 2018 1:18 PMJeff Moden - Tuesday, December 18, 2018 1:12 PMSean Lange - Tuesday, December 18, 2018 10:20 AMneil-560592 - Tuesday, December 18, 2018 4:56 AMJust tried:select EntityID, secondcol, thirdcol
from dbo.Entity
order by 1+2Doesn't order by thirdcol - gives a syntax error. So the number (1) is an alias for "first column"; it is not parsed as an integer.
It isn't an alias, it is the ordinal position of columns in the result set. It is a carry over from sysbase and is really dreadful in a real world scenario.
But it's great for quick "what if" and "proof of principle" code where the order and column names may change a lot or not even exist. My hopes that they never get rid of it are dashed because, IIRC, they've deprecated its use.
If MS keeps deprecating useful tools like this one, they'll eventually make SQL Server "idiot proof" and then only idiots will use it. 😀
I have never seen it on an official deprecation list. In fact as a result of this thread I was digging and still don't see it. I agree that it is useful for development from time to time but not something I really use very often. I usually think in my head that I want to order by a column, then oh it doesn't matter which column so before I have a chance to think of using 1 I have already typed in the primary key of the main table in the query. 😉
I can actually say that I have never used ORDER BY ordinal position in any code I have written.
If I want to order by a column or set of columns I will explicitly specify the column(s).
December 18, 2018 at 2:16 pm
Jeff Moden - Tuesday, December 18, 2018 1:12 PMSean Lange - Tuesday, December 18, 2018 10:20 AMneil-560592 - Tuesday, December 18, 2018 4:56 AMJust tried:select EntityID, secondcol, thirdcol
from dbo.Entity
order by 1+2Doesn't order by thirdcol - gives a syntax error. So the number (1) is an alias for "first column"; it is not parsed as an integer.
It isn't an alias, it is the ordinal position of columns in the result set. It is a carry over from sysbase and is really dreadful in a real world scenario.
But it's great for quick "what if" and "proof of principle" code where the order and column names may change a lot or not even exist. My hopes that they never get rid of it are dashed because, IIRC, they've deprecated its use.
If MS keeps deprecating useful tools like this one, they'll eventually make SQL Server "idiot proof" and then only idiots will use it. 😀
Does deprecation ever happen? I thought SQL collations were going to be deprecated years ago. (maybe they have been??) in favor of Windows collations.
December 18, 2018 at 2:37 pm
Lynn Pettis - Tuesday, December 18, 2018 2:09 PMSean Lange - Tuesday, December 18, 2018 1:18 PMJeff Moden - Tuesday, December 18, 2018 1:12 PMSean Lange - Tuesday, December 18, 2018 10:20 AMneil-560592 - Tuesday, December 18, 2018 4:56 AMJust tried:select EntityID, secondcol, thirdcol
from dbo.Entity
order by 1+2Doesn't order by thirdcol - gives a syntax error. So the number (1) is an alias for "first column"; it is not parsed as an integer.
It isn't an alias, it is the ordinal position of columns in the result set. It is a carry over from sysbase and is really dreadful in a real world scenario.
But it's great for quick "what if" and "proof of principle" code where the order and column names may change a lot or not even exist. My hopes that they never get rid of it are dashed because, IIRC, they've deprecated its use.
If MS keeps deprecating useful tools like this one, they'll eventually make SQL Server "idiot proof" and then only idiots will use it. 😀
I have never seen it on an official deprecation list. In fact as a result of this thread I was digging and still don't see it. I agree that it is useful for development from time to time but not something I really use very often. I usually think in my head that I want to order by a column, then oh it doesn't matter which column so before I have a chance to think of using 1 I have already typed in the primary key of the main table in the query. 😉
I can actually say that I have never used ORDER BY ordinal position in any code I have written.
If I want to order by a column or set of columns I will explicitly specify the column(s).
Same here. When I see the ordinal position I have to stop and think about it not being a constant (meaning inconsistent order).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 18, 2018 at 2:39 pm
rchantler - Tuesday, December 18, 2018 2:16 PMJeff Moden - Tuesday, December 18, 2018 1:12 PMSean Lange - Tuesday, December 18, 2018 10:20 AMneil-560592 - Tuesday, December 18, 2018 4:56 AMJust tried:select EntityID, secondcol, thirdcol
from dbo.Entity
order by 1+2Doesn't order by thirdcol - gives a syntax error. So the number (1) is an alias for "first column"; it is not parsed as an integer.
It isn't an alias, it is the ordinal position of columns in the result set. It is a carry over from sysbase and is really dreadful in a real world scenario.
But it's great for quick "what if" and "proof of principle" code where the order and column names may change a lot or not even exist. My hopes that they never get rid of it are dashed because, IIRC, they've deprecated its use.
If MS keeps deprecating useful tools like this one, they'll eventually make SQL Server "idiot proof" and then only idiots will use it. 😀
Does deprecation ever happen? I thought SQL collations were going to be deprecated years ago. (maybe they have been??) in favor of Windows collations.
Sure stuff gets deprecated every release. Sadly most of them get removed, which they should. I have never heard that sql collations would be deprecated. Where did you read that? I highly doubt that would happen anyway. SQL Server now runs on Linux. If they used on Windows collations instead of their own that would prove to be nearly impossible.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 18, 2018 at 8:34 pm
Mike Byrd - Monday, December 17, 2018 9:47 AMWow, didn't know the article was going to generate a fire-storm on TSQL syntax. The original purpose was to see if there was a difference getting max/min value of a column using the TOP or MAX/MIN functions. I agree that ORDER BY 1 (first column in result set) should not be used in production -- but that was not the purpose of the article. What I didn't anticipate is all the other query plan operators needed to satisfy the overall particular query syntax. Normally I am very strict in my TSQL syntax for going into production, but obviously more lax in ad hoc queries. Thanks everyone for your replies.
If you're article starts a fire storm or any other type of fire, it was worth the time.
Shifting gears a bit, could you post the CREATE TABLE code for the table you used along with the indexes? I'd like to play a bit.
Also, just a tip from a craggy ol' writer... posting code as a graphic is fine but only if you include the code as a file in the resource section of the article. That way people can just copy'n'paste your code if they want to "Sing along with Mitch".
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply