Query shows sort cost,even when required index is available
In query plans, ordered property set to true means that an IAM-driven scan was not done. The data was read in the logical order according to the index definition.

Hash join does not preserve order. Your first query has a hash join so the explicit SORT is needed at the end. One way to avoid the sort in the query is to do a nested loop join with LogTable as the outer table. On my machine I was able to accomplish this with a variety of hints:

SELECT  DI.LogID              
FROM LogTable DI              
        INNER JOIN LogTable_Cross DP ON DP.LogID = DI.LogID  
        WHERE  DP.UserID = 1  
ORDER BY DateSent DESC
OPTION (MAXDOP 1, LOOP JOIN, FORCE ORDER, NO_PERFORMANCE_SPOOL);




This is a very inefficient query plan because the right index is not defined on the LogTable_Cross table. A seek is done against that table, but only one the UserId column:



Filtering on LogID is done in the join itself. I can create an index that better supports the query plan that I'm looking for:

CREATE INDEX IX_LogTable_Cross ON LogTable_Cross (LogID, UserID);


The creation of that index does not guarantee a query plan without a sort. SQL Server may estimate that a plan with a sort has a lower cost. However, if I eliminate all hints except for the LOOP JOIN then I get a reasonably efficient query plan without a sort: