Why is there a different outcome when using LIKE vs. =?

Question

I am using Report Builder 3.0. I have a report where one of the sorts is based on the line (it is also grouped on line) using Row Groups. When I use LIKE in the query code, the sorting on the report works fine: it is sorted A-Z. When I use = in the query code, the report sorts it like this:

LN BM4
LN BM2
LN BM1
LN BM6
LN BM5
LN WT2
LN WT4
LN WT3
LN WT5
LN BM3
LN WT5

Why is using a different operator causing a sorting difference? How can I get my report to sort A-Z and still use the = operator? On a side note, I was told by one of our I.T. guys to use = because using LIKE slows down a query significantly, but seeing as how it's causing this problem I'm wondering what I should really use. Is this true?

Below I have given all of the possible data in our table.

**Raw data (unsorted)**
LN BM1
LN BM2
LN BM3
LN BM4
LN BM5
LN BM6

LN WT1
LN WT2
LN WT3
LN WT4
LN WT5
LN WT6

Query code

(CASE
            WHEN jobs.Uf_Production_Line LIKE 'LN BM%' THEN jobs.Uf_Production_Line
            WHEN jobs.Uf_Production_Line LIKE 'LN WT%' THEN jobs.Uf_Production_Line

            ELSE

            (CASE

                WHEN    IsNumeric(RIGHT(jobs.Uf_Production_Line, 4)) = 1 
                    THEN RIGHT(jobs.Uf_Production_Line, 4)

                ELSE    

                    (CASE
                        WHEN    IsNumeric(RIGHT(jobs.Uf_Production_Line, 3)) = 1 
                            THEN RIGHT(jobs.Uf_Production_Line, 3)
                        ELSE    
                            '99999'
                    END)
            END) 


        END
    ) AS line

ORDER BY statement at the bottom of the query seems to have no effect because there are different grouping and sorting rules based on Row Groups in the Design View of the Report Builder

ORDER BY job, datevalue, shift

Answer 1

If you haven't specify any sorting for the query, the records will come in the order that the database finds them.

Changing an operator can result in a different execution plan. That means that the data will use different methods to find the right data, and the rows will be returned in an order depending on how those methods process the data.

An index scan for example would produce rows in the order of that index, but a table scan would produce rows in the order that they happen to be stored in the table.

Even for the same query the execution plan can change. The database gathers statistics about the data which it uses to choose the execution plan, so it may choose a different plan because it has got better knowledge about the data.

Without specifying the sorting for the query, you can simply never be sure what the order will be, or even that it will stay the same.