title: How to avoid sort operator in index plan
body: <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>