How to avoid sort operator in index plan
<p>I have two tables [LogTable] and [LogTable_Cross].</p>

<p>Below is the schema and script to populate them:</p>

<pre><code> --Main Table

 CREATE TABLE [dbo].[LogTable]
    (
      [LogID] [int] NOT NULL
                    IDENTITY(1, 1) ,
      [DateSent] [datetime] NULL,
    )
 ON [PRIMARY]
GO
 ALTER TABLE [dbo].[LogTable] ADD CONSTRAINT [PK_LogTable] PRIMARY KEY CLUSTERED  ([LogID]) ON [PRIMARY]
GO
 CREATE NONCLUSTERED INDEX [IX_LogTable_DateSent] ON [dbo].[LogTable] ([DateSent] DESC) ON [PRIMARY]
GO
 CREATE NONCLUSTERED INDEX [IX_LogTable_DateSent_LogID] ON [dbo].[LogTable] ([DateSent] DESC) INCLUDE ([LogID]) ON [PRIMARY]
GO


--Cross table

 CREATE TABLE [dbo].[LogTable_Cross]
    (
      [LogID] [int] NOT NULL ,
      [UserID] [int] NOT NULL
    )
 ON [PRIMARY]
GO
 ALTER TABLE [dbo].[LogTable_Cross] WITH NOCHECK ADD CONSTRAINT [FK_LogTable_Cross_LogTable] FOREIGN KEY ([LogID]) REFERENCES [dbo].[LogTable] ([LogID])
GO
 CREATE NONCLUSTERED INDEX [IX_LogTable_Cross_UserID_LogID]
 ON [dbo].[LogTable_Cross] ([UserID])
 INCLUDE ([LogID])
GO


-- Script to populate them
 INSERT INTO [LogTable]
        SELECT TOP 100000
                DATEADD(day, ( ABS(CHECKSUM(NEWID())) % 65530 ), 0)
        FROM    sys.sysobjects
                CROSS JOIN sys.all_columns


 INSERT INTO [LogTable_Cross]
        SELECT  [LogID] ,
                1
        FROM    [LogTable]
        ORDER BY NEWID()

 INSERT INTO [LogTable_Cross]
        SELECT  [LogID] ,
                2
        FROM    [LogTable]
        ORDER BY NEWID()

 INSERT INTO [LogTable_Cross]
        SELECT  [LogID] ,
                3
        FROM    [LogTable]
        ORDER BY NEWID()


GO
</code></pre>

<p>I want to select all those logs (from LogTable) which has given userid (user id will be checked from cross table LogTable_Cross).</p>

<p>I have tried the following 4 different queries to do that:</p>

<pre><code> SELECT DMT.LogID ,
        ROW_NUMBER() OVER ( ORDER BY DMT.DateSent DESC ) AS RowNumber
 FROM   LogTable DMT
 WHERE  LogID IN ( SELECT   LogID
                   FROM     LogTable_Cross DMTP
                   WHERE    DMTP.UserID = 1 )

 SELECT DMT.LogID ,
        ROW_NUMBER() OVER ( ORDER BY DMT.DateSent DESC ) AS RowNumber
 FROM   LogTable DMT
 WHERE  EXISTS ( SELECT LogID
                 FROM   LogTable_Cross DMTP
                 WHERE  DMTP.LogID = DMT.LogID
                        AND DMTP.UserID = 1 )


 SELECT DMT.LogID ,
        ROW_NUMBER() OVER ( ORDER BY DMT.DateSent DESC ) AS RowNumber
 FROM   LogTable DMT
        JOIN ( SELECT   LogID
               FROM     LogTable_Cross DMTP
               WHERE    DMTP.UserID = 1
             ) T ON DMT.LogID = T.LogID                            


 SELECT DMT.LogID ,
        ROW_NUMBER() OVER ( ORDER BY DMT.DateSent DESC ) AS RowNumber
 FROM   LogTable DMT
        INNER JOIN LogTable_Cross DMTP ON DMTP.LogID = DMT.LogID
 WHERE  DMTP.UserID = 1 
</code></pre>

<p>The issue I am facing is that despite of the non clustered index I have applied of LogTable for DateSent desc it is not being used for my queries.
A heavy "Sort" operator is coming in the execution plan. </p>

<p>Below is the screenshot for the plan:</p>

<p><a href="https://i.stack.imgur.com/vi5x2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vi5x2.png" alt="enter image description here"></a></p>

<p>Can you help me fixing the queries or adding some new index so that I can avoid that "Sort" operator coming in plan?</p>

<p>Edit:</p>

<p>Even if I remove Row_Number() the sort operator is still coming for the following query:</p>

<pre><code> SELECT DMT.LogID  
 FROM   LogTable DMT
 WHERE  LogID IN ( SELECT   LogID
                   FROM     LogTable_Cross DMTP
                   WHERE    DMTP.UserID = 1 )
ORDER BY DMT.DateSent DESC 
</code></pre>

<p>Here is the execution plan for this query:
<a href="https://i.stack.imgur.com/x1JiY.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/x1JiY.png" alt="enter image description here"></a></p>