Ggthjy

in other words, how can I get rid of the sort operator on the picture below?

the picture above shows the execution plan of the following 2 selects together:

                SELECT   TOP 1 so.OrgType, 

                              ch.Status, 

                              rcs.DBSstatusDescription, 

                              cid.ApplicationId

                   FROM   tbl_application_crb_initialData cid



                   INNER JOIN  tbl_season_organisationId so

                          ON   cid.OrganisationId = so.OrgId



                   LEFT JOIN  tbl_crbHistory ch  

                          ON  cid.ClientReference = ch.ClientReference



                   LEFT JOIN  ref_crbStatus rcs

                         ON   ch.Status = rcs.statusId    



                   ORDER BY  cid.DateAdded DESC, ch.DateAdded DESC



                SELECT   TOP 1 so.OrgType, 

                              ch.Status, 

                              rcs.DBSstatusDescription, 

                              cid.ApplicationId

                   FROM   tbl_application_crb_initialData cid



                   INNER JOIN  tbl_season_organisationId so

                          ON   cid.OrganisationId = so.OrgId



                   LEFT JOIN  tbl_crbHistory ch  

                          ON  cid.ClientReference = ch.ClientReference



                   LEFT JOIN  ref_crbStatus rcs

                         ON   ch.Status = rcs.statusId    



                   ORDER BY  cid.DateAdded DESC--, ch.DateAdded DESC

the only difference is that on the second query, there is only one column in the order by.

would it make a difference, as I am using top 1?

I believe all the needed info are on the indexes and table definitions that can be seen on the query plan.

if anything else would help to get rid of that sort just let me know, tomorrow I will post all the possible info.

Your question is missing a lot of detail but I can reproduce something similar.

Setup

CREATE TABLE T1(X INT PRIMARY KEY, Y INT INDEX IX)



CREATE TABLE T2(X INT, Y INT , PRIMARY KEY(X, Y))



INSERT INTO T2 

OUTPUT INSERTED.* INTO T1

SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @@SPID), ROW_NUMBER() OVER (ORDER BY @@SPID)

FROM sys.all_objects o1, sys.all_objects o2;

Query 1

SELECT TOP 1 T1.Y AS T1Y, T2.Y AS T2Y

FROM T1 JOIN T2 ON T1.X = T2.X 

ORDER BY T1.Y;

Query 2

SELECT TOP 1 T1.Y AS T1Y, T2.Y AS T2Y

FROM T1 JOIN T2 ON T1.X = T2.X 

ORDER BY T1.Y, T2.Y

Query 3

WITH T  AS

(

SELECT TOP 1 WITH TIES T1.Y AS T1Y, T2.Y AS T2Y

FROM T1 JOIN T2 ON T1.X = T2.X 

ORDER BY T1.Y

)

SELECT TOP 1 *

FROM T 

ORDER BY T2Y

Query 1 just picks off the TOP 1 from the index in the desired sort order and does the needed joins on the other table for that row. If the join is successful it stops there otherwise it tries the next one in index order until it finds a row that matches or runs out of rows.

Query 2 When adding the new sort column this plan is no longer valid as there could be multiple matches tied with the TOP 1 value and SQL Server decides to join the whole lot and then get the TOP 1 from that.

Query 3 This encourages SQL Server to stick with the first strategy and then just does a TOP 1 Sort on any rows tied with the same value for the first sort key.

For my example data Query 3 works out better than Query 2 but if you have many duplicates tied for the value of the first sort key your milage may differ.

You can try this rewrite and see how it fares

WITH T

     AS (SELECT TOP 1 WITH TIES so.OrgType,

                                ch.Status,

                                rcs.DBSstatusDescription,

                                cid.ApplicationId,

                                ch.DateAdded AS chDateAdded

         FROM   tbl_application_crb_initialData cid

                INNER JOIN tbl_season_organisationId so

                        ON cid.OrganisationId = so.OrgId

                LEFT JOIN tbl_crbHistory ch

                       ON cid.ClientReference = ch.ClientReference

                LEFT JOIN ref_crbStatus rcs

                       ON ch.Status = rcs.statusId

         ORDER  BY cid.DateAdded DESC)

SELECT TOP 1 OrgType,

             Status,

             DBSstatusDescription,

             ApplicationId

FROM   T

ORDER  BY chDateAdded DESC