title: How to avoid sorting results, returned by Index Seek?
body: <p>Well, the question is not really "how to avoid" sorting as it is required by business logic, but how to do it preliminary using index, and not on the go.</p>
  
  <p>I have a query plan:</p>
  
  <pre><code>|--Sort(TOP 5, ORDER BY:([this_].[DateAdded] DESC))
     |--Nested Loops(Inner Join, OUTER REFERENCES:([this_].[Id], [Expr1002]) OPTIMIZED WITH UNORDERED PREFETCH)
          |--Index Seek(OBJECT:([storm].[dbo].[Items].[IX_Items_ByLocationSorted] AS [this_]), SEEK:([this_].[Status]=(1) AND [this_].[RegionId]=(32) AND [this_].[LocationId]=(32001)),  WHERE:([storm].[dbo].[Items].[SubcategoryTypeId] as [this_].[SubcategoryTypeId]=(88) AND ([storm].[dbo].[Items].[IsHidden] as [this_].[IsHidden]&lt;(1) OR [storm].[dbo].[Items].[IsHidden] as [this_].[IsHidden]&gt;(1))) ORDERED FORWARD)
          |--Clustered Index Seek(OBJECT:([storm].[dbo].[Items].[PK_Items] AS [this_]), SEEK:([this_].[Id]=[storm].[dbo].[Items].[Id] as [this_].[Id]) LOOKUP ORDERED FORWARD) 
  </code></pre>
  
  <p>You can see, that Index Seek operation returns a set of rows. Than Query Optimizer does a Key Lookup to retrieve the whole row (since he'd have to return it anyway), and than it sorts all those rows by DateAdded column. It's an obvious and perfectly valid behavior. But it's really slow (might take up to 40 sec), considering the number of rows, returned by Index Seek (30k max). </p>
  
  <p>How can I speed this query up and possibly avoid this on-the-go sorting?</p>
  
  <p>PS: The queried table has about 3 million rows, and is frequently updated. This might cause page locking but I don't think those locks can last 40 seconds.</p>
  
  <p>Query:</p>
  
  <pre><code>SELECT TOP 5 * 
  FROM Items 
  WHERE 
       SubcategoryTypeId = 88 
       and RegionId = 32
   and LocationId = 32001
   and not (IsHidden = 1 and Status = 1) 
   ORDER BY DateAdded desc
  </code></pre>