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:
ORDER BY [GroupName], [Name] — Sort on GroupName and then on Name of departments
By column number:
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.
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