I strongly favor ORDER BY ColumnName.
I read one of the blog post where blogger compared the performance of the two SELECT statement and come to conclusion that ColumnNumber has no harm to use it. Let us understand the point made by first that there is no performance difference.
Run following two scripts together:
USE AdventureWorks
GO
-- ColumnName (Recommended)
SELECT *
FROM HumanResources.Department
ORDER BY GroupName, Name
GO
-- ColumnNumber (Strongly Not Recommended)
SELECT *
FROM HumanResources.Department
ORDER BY 3,2
GO
If you look at the result and see the execution plan you will see that both of the query will take the same amount of the time.
However, this was not the point of this blog post. It is not good enough to stop here. We need to understand the advantages and disadvantages of both the methods.
Case 1: When Not Using * and Columns are Re-ordered
USE AdventureWorks
GO
-- ColumnName (Recommended)
SELECT GroupName, Name, ModifiedDate, DepartmentID
FROM HumanResources.Department
ORDER BY GroupName, Name
GO
-- ColumnNumber (Strongly Not Recommended)
SELECT GroupName, Name, ModifiedDate, DepartmentID
FROM HumanResources.Department
ORDER BY 3,2
GO
Case 2: When someone changes the schema of the table affecting column order
I will let you recreate the example for the same. If your development server where your schema is different than the production server, if you use ColumnNumber, you will get different results on the production server.
Summary: When you develop the query it may not be issue but as time passes by and new columns are added to the SELECT statement or original table is re-ordered if you have used ColumnNumber it may possible that your query will start giving you unexpected results and incorrect ORDER BY.
One should note that the usage of ORDER BY ColumnName vs ORDER BY ColumnNumber should not be done based on performance but usability and scalability.
It is always recommended to use proper ORDER BY clause with ColumnName to avoid any confusion.
Reference: Pinal Dave (https://blog.sqlauthority.com)
23 Comments. Leave new
Hi u r Articles are too good can u send me the Information about ‘Optimizing concurency with realtime Example’ .
hi, can u put some more light on this topic. where exactly we can use ORDER BY ColumnNumber and its possible to use ORDER BY ColumnName instead?
hi, can u put some more light on this topic. where exactly we can use ORDER BY ColumnNumber and its *not* possible to use ORDER BY ColumnName instead?
You can use it in a dynamic sql where column names are unknown. Refer this post for more informations
Thanks Guru for correcting me.
Pinal,
Hi, I am totally mess up with the example you have given. In the First query you Order the column by OrderName namely “GroupName,Name”. But when you are ordering by ColumnNumber inthe second query you ordered by “ModifiedDate,Name”i.e “3,2” refers to the Columns orders in the select statement not with the table ordering structure.Please try this and let me know.
It will work that way. But the point is when you use column numbers in the order by clause, if some columns are added at the beginning of the select statement, it will be ordered differently. If you use column names in the order by clause, they wont be any problems
Just checked this out and noticed the execution plans are deferent
dbcc freeproccache
GO
SELECT DepartmentID,GroupName, Name, ModifiedDate
FROM HumanResources.Department
ORDER BY 3,2
GO
dbcc freeproccache
GO
SELECT DepartmentID,GroupName, Name, ModifiedDate
FROM HumanResources.Department
ORDER BY GroupName, Name
Under the same circumstance, if column numbers are used then there is a significant number of logical reads vs the column names
Nice post to bring out the differences between ordering by column name and column number. For me it has been by col name majority of the times.
Hi Pinal!
With my usual pleasure and laugh have I read another brilliant article of yours on Advanced SQL Server approaches. As usual you expect people to understand your point based on Experience and Knowledge level comparable to yours. These are not abundant around though.. So guys are just trying to understand say which column would you use for ordering in a query run against table with 20 million rows. But to understand it you have to first know how to assign Primary Key, which column to put Clustered Index on and by what reasons. Only then you will begin to understand and enjoy Pinal Daves blog! Otherwise, according to some comments, it is kind of complex. Thank you for teaching us! Keep up the excellent job! Have a happy and joyfull Holidays!
Hi Pinal…
I don’t know if you read this blog post or not, but this is an amusing article I wrote on this subject, talking about the ORDER BY clause and using column aliases vs ordinal numbers vs functions:
–Brad
Hi Pinal,
while I agree with you, number ordering very useful in a development environment and makes developers more productive.
I often use it when the select list holds a long subquery that I need to order by in SQL 2000. It saves me copying and pasting the whole thing in the order by clause, which makes the query look messy.
It is safe as long as no other columns are specified in that posistion in SELECT statement
Agree entirely with Pinal – if you go back to the code three months later, no way will you remember what order by 3,2 is supposed to mean. Is it right? Is it wrong? There is no way to tell.
How to Avoid Order by Completely . Example
I have a query
select datakey from V_Definitions
where DataKey in (
‘FLASH1ZZZZ’,
‘042I01OAMJDA’,
‘035CY40IMJ’,’034C0ZD1MJ’) and Site = ‘PJ’ and System = ‘GO’
and I want the output in the same order od the datakey that i have provided in the “in”.
Output received from query
034C0ZD1MJ
035CY40IMJ
042I01OAMJDA
FLASH1ZZZZ
Expected Ouput
FLASH1ZZZZ
042I01OAMJDA
035CY40IMJ
034C0ZD1MJ
how can a default sort of ASC be avoided ?
hi..PInal..Can u give me example about usage of order by dynamically along with case or if else statements.
1.can we use order by other than columns i mean…select * from table order by ‘msc'(value)(other than Specific columns)
And old thread I know, but if your select contains functions or case statements etc. it saves you having to repeat the case or re-evaluate the fn, this can be a massive time saver when you can just say the column number
I use order by column number when selecting from a wide table and want to duplicate one or more rows on the left. Order by column name in this case produces an error unless you alias the duplicate column.
Select order_id, * from order_header order by 1
v/s
Select order_id as Reference, * from order_header order by order_id
In that case, you should just ORDER BY Reference.
Good suggestion Brad.
I have a case where I use column ordinal from selecting from an inline function calling a multi-line function and that wasn’t good with the index. So i have a inline function A that is selecting from a multiline function B. When you run select from frunction A and order using column ordinal, the performance was poor. Even though the Ordering column was part of the clustered key. But using the column name was better.