[Solved] T-SQL Order By - data type precedence not working as expected

EverSQL Database Performance Knowledge Base

T-SQL Order By - data type precedence not working as expected

I've been puzzling over this problem for days now, and have just identified the source of my woes - an order by clause is not working as expected.

The script goes like this:

select * from my_table
order by change_effective_date, unique_id desc

change_effective_date is a datetime field, and unique_id is an int field.

I had expected this to give me the most recent row first (i.e. the row with the highest value in change_effective_date). However, it was giving the oldest row first, and the unique_id was also in ascending order (these IDs are normally sequential, so I would generally expect them to follow the same order as the dates anyway, though this is not completely reliable).

Puzzled, I turned to Google and found that data type precedence can affect order by clauses, with lower-ranking datatypes being converted to the higher-ranking datatype: https://blog.sqlauthority.com/2010/10/08/sql-server-simple-explanation-of-data-type-precedence/

However, datetime takes precedence over int, so it shouldn't be affected in this way.

More curiously, if I take unique_id out of the order by clause, it sorts the data in descending date order perfectly. I do want to add a unique identifier to the order by clause, though, as there could be multiple rows with the same date and further on in the script I want to identify the most recent (in this case, the unique_id would be the tie-breaker as I would assume it to be sequential).

If anyone can explain what's happening here, I'd really appreciate it!

Thanks.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
  2. Mixed Order By Directions Prevents Index Use (query line: 6): The database will not use a sorting index (if exists) in cases where the query mixes ASC (the default if not specified) and DESC order. To avoid filesort, you may consider using the same order type for all columns. Another option that will allow you to switch one direction to another is to create a new reversed "sort" column (max_sort - sort) and index it instead.
The optimized query:
SELECT
        * 
    FROM
        my_table 
    ORDER BY
        my_table.change_effective_date,
        my_table.unique_id DESC

Related Articles



* original question posted on StackOverflow here.