title: 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) with datesent desc.</p>
  
  <pre><code>SELECT  DI.LogID              
  FROM    LogTable DI              
          INNER JOIN LogTable_Cross DP ON DP.LogID = DI.LogID  
          WHERE  DP.UserID = 1  
  ORDER BY DateSent DESC
  </code></pre>
  
  <p>After running this query here is my execution plan:
  <a href="https://i.stack.imgur.com/JjX9O.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JjX9O.png" alt="enter image description here"></a></p>
  
  <p>As you can see there is a sort operator coming in role and that should be probably because of following line "ORDER BY DateSent DESC"</p>
  
  <p>My question is that why that Sort operator is coming in the plan even though I have the following index applied on the table</p>
  
  <pre><code>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
  </code></pre>
  
  <p>On the other hand if I remove the join and write the query in this way:</p>
  
  <pre><code>SELECT  DI.LogID              
  FROM    LogTable DI              
    --      INNER JOIN LogTable_Cross DP ON DP.LogID = DI.LogID  
          --WHERE  DP.UserID = 1  
  ORDER BY DateSent DESC
  </code></pre>
  
  <p>the plan changes to </p>
  
  <p><a href="https://i.stack.imgur.com/qLgRs.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/qLgRs.png" alt="enter image description here"></a></p>
  
  <p>i.e Sort operator is removed and the plan is showing that my query is using my non clustered index.</p>
  
  <p>So is that a way to remove "Sort" operator in the plan for my query even if I am using join.</p>
  
  <p><strong>EDIT</strong>:</p>
  
  <p>I went further and limited the "Max Degree of Parallelism" to 1</p>
  
  <p><a href="https://i.stack.imgur.com/xSox1.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/xSox1.png" alt="enter image description here"></a></p>
  
  <p>Ran the following query again:</p>
  
  <pre><code>SELECT  DI.LogID              
  FROM    LogTable DI              
          INNER JOIN LogTable_Cross DP ON DP.LogID = DI.LogID  
          WHERE  DP.UserID = 1  
  ORDER BY DateSent DESC
  </code></pre>
  
  <p>and the plan is still having that Sort operator:</p>
  
  <p><a href="https://i.stack.imgur.com/ZEo2O.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ZEo2O.png" alt="enter image description here"></a></p>
  
  <p><strong>Edit 2</strong></p>
  
  <p>Even if I have the following index as suggested:</p>
  
  <pre><code> CREATE NONCLUSTERED INDEX [IX_LogTable_Cross_UserID_LogID_2]
   ON [dbo].[LogTable_Cross] ([UserID], [LogID])
  </code></pre>
  
  <p>the plan is still having the Sort operator:
  <a href="https://i.stack.imgur.com/ffKw6.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ffKw6.png" alt="enter image description here"></a></p>