When writing SQL queries in SQL Server, it is important to include the ORDER BY clause to specify the desired order of the result set. The ORDER BY clause allows you to sort the data based on one or more columns in ascending or descending order.

In a recent blog post, a comparison was made between using the column name and the column number in the ORDER BY clause. The conclusion was that there is no performance difference between the two methods. However, it is important to delve deeper into the advantages and disadvantages of each approach.

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

Using the column name in the ORDER BY clause is recommended in this case. By explicitly specifying the column names, you ensure that the order of the columns in the SELECT statement matches the order in the ORDER BY clause. This helps to avoid any confusion and ensures that the query returns the expected results.

On the other hand, using the column number in the ORDER BY clause is strongly not recommended. If the schema of the table is changed, such as adding new columns or re-ordering the existing columns, using the column number can lead to unexpected results. This is especially true if the development server has a different schema than the production server.

Case 2: When someone changes the schema of the table affecting column order

In this case, it is crucial to use the column name in the ORDER BY clause. If the column order is changed in the table, queries that rely on the column number will produce incorrect results. By using the column name, you future-proof your queries and ensure that they continue to work correctly even if the table schema changes.

It is important to note that the decision to use ORDER BY ColumnName versus ORDER BY ColumnNumber should not be based on performance considerations. The performance difference between the two approaches is negligible. Instead, the focus should be on usability and scalability. By using the column name, you make your queries more readable and maintainable, and you avoid potential issues when the table schema changes.

In conclusion, it is always recommended to use the proper ORDER BY clause with the column name in SQL Server queries. This ensures that your queries return the expected results and remain accurate even when the table schema changes. By following this best practice, you can avoid confusion and maintain the usability and scalability of your SQL Server applications.