SQL SERVER – ORDER BY ColumnName vs ORDER BY ColumnNumber

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 (http://blog.sqlauthority.com)

About these ads

19 thoughts on “SQL SERVER – ORDER BY ColumnName vs ORDER BY ColumnNumber

  1. 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?

  2. 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?

  3. 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

        • If you want to compare both of them then you need to compare following

          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 Name, GroupName

  4. 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.

  5. 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!

  6. 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.

  7. 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.

  8. 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 ?

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #009 « SQL Server Journey with SQL Authority

  10. 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)

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