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.
