Sql Server: ORDER BY column name Or ORDER BY column number

Column in ORDER BY clause can be mentioned by two ways. First method is common method used by every database developer i.e. column name and second one is bit rare. In this method column number are used instead of column names.

By column name:

USE AdventureWorks

SELECT *

FROM HumanResources.Department

ORDER BY [GroupName], [Name] -- Sort on GroupName and then on Name of departments

By column number:

USE AdventureWorks

SELECT *

FROM HumanResources.Department

ORDER BY 3,2 -- Sort on GroupName (column number 3)and then on Name (column number 2) of departments

Column numbers are fixed identity numbers allotted to each column of a table. These id numbers are unique for a single table. We already had discussed it in an early post.

Performance Comparison:

Execution plan clearly showing ZERO performance difference.

Though, there is no performance difference but still Sql Gurus are still against “ORDER BY column number” method. This is because it creates confusion for other persons to understand your code. But still I love to use “ORDER BY column number” in development environment, and like to avoid it in production databases.

Have you experienced any performance difference for both above mentioned methods? Do share with us.

Note: Why we should not use ORDER BY column number, MVP Pinal Dave has better explanation