ORDER BY ColumnName vs ORDER BY ColumnNumber

Case 1: When Not Using * and Columns are Re-ordered

-- ColumnName (Recommended)SELECT name, DeptFROM testORDER BY name, DeptGO-- ColumnNumber (Strongly Not Recommended)SELECT name, DeptFROM name, DeptORDER BY 2,1GO

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