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.

SQL SERVER - ORDER BY ColumnName vs ORDER BY ColumnNumber order3

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

SQL SERVER - ORDER BY ColumnName vs ORDER BY ColumnNumber order2

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

Previous Post
SQL SERVER – Server Side Paging in SQL Server Denali – Part2
Next Post
SQL SERVER – Plan Cache and Data Cache in Memory

Related Posts

23 Comments. Leave new

  • Hi u r Articles are too good can u send me the Information about ‘Optimizing concurency with realtime Example’ .

    Reply
  • 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?

    Reply
  • 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?

    Reply
  • Thanks Guru for correcting me.

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

    Reply
    • 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

      Reply
      • 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

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

    Reply
  • 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!

    Reply
  • Hi Pinal…

    I don’t know if you read this blog post or not, but this is an amusing article I wrote on this subject, talking about the ORDER BY clause and using column aliases vs ordinal numbers vs functions:

    –Brad

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

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

    Reply
  • 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 ?

    Reply
  • 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)

    Reply
  • And old thread I know, but if your select contains functions or case statements etc. it saves you having to repeat the case or re-evaluate the fn, this can be a massive time saver when you can just say the column number

    Reply
  • I use order by column number when selecting from a wide table and want to duplicate one or more rows on the left. Order by column name in this case produces an error unless you alias the duplicate column.

    Select order_id, * from order_header order by 1
    v/s
    Select order_id as Reference, * from order_header order by order_id

    Reply
  • Omo-oba Oluwatobiloba
    June 16, 2016 8:27 pm

    I have a case where I use column ordinal from selecting from an inline function calling a multi-line function and that wasn’t good with the index. So i have a inline function A that is selecting from a multiline function B. When you run select from frunction A and order using column ordinal, the performance was poor. Even though the Ordering column was part of the clustered key. But using the column name was better.

    Reply

Leave a Reply

Menu