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

Advertisements

Posted on December 17, 2010, in Performance Tuning, TSQL Tips n Tricks. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: