title: Optimizing SQL queries by removing Sort operator in Execution plan
body: <p>I’ve just started looking into optimizing my queries through indexes because SQL data is growing large and fast. I looked at how the optimizer is processing my query through the Execution plan in SSMS and noticed that a Sort operator is being used. I’ve heard that a Sort operator indicates a bad design in the query since the sort can be made prematurely through an index. So here is an example table and data similar to what I’m doing: </p>
  
  <pre><code>IF OBJECT_ID('dbo.Store') IS NOT NULL DROP TABLE dbo.[Store]
  GO
  
  CREATE TABLE dbo.[Store]
  (
      [StoreId] int NOT NULL IDENTITY (1, 1),
      [ParentStoreId] int NULL,
      [Type] int NULL,
      [Phone] char(10) NULL,
      PRIMARY KEY ([StoreId])
  )
  
  INSERT INTO dbo.[Store] ([ParentStoreId], [Type], [Phone]) VALUES (10, 0, '2223334444')
  INSERT INTO dbo.[Store] ([ParentStoreId], [Type], [Phone]) VALUES (10, 0, '3334445555')
  INSERT INTO dbo.[Store] ([ParentStoreId], [Type], [Phone]) VALUES (10, 1, '0001112222')
  INSERT INTO dbo.[Store] ([ParentStoreId], [Type], [Phone]) VALUES (10, 1, '1112223333')
  GO
  </code></pre>
  
  <p>Here is an example query:</p>
  
  <pre><code>SELECT [Phone]
  FROM [dbo].[Store]
  WHERE [ParentStoreId] = 10
  AND ([Type] = 0 OR [Type] = 1)
  ORDER BY [Phone]
  </code></pre>
  
  <p>I create a non clustered index to help speed up the query:</p>
  
  <pre><code>CREATE NONCLUSTERED INDEX IX_Store ON dbo.[Store]([ParentStoreId], [Type], [Phone])
  </code></pre>
  
  <p>To build the IX_Store index, I start with the simple predicates </p>
  
  <pre><code>[ParentStoreId] = 10
  AND ([Type] = 0 OR [Type] = 1)
  </code></pre>
  
  <p>Then I add the <code>[Phone]</code> column for the ORDER BY and to cover the SELECT output</p>
  
  <p>So even when the index is built, the optimizer still uses the Sort operator (and not the index sort) because <code>[Phone]</code> is sorted AFTER <code>[ParentStoreId]</code> AND <code>[Type]</code>. If I remove the <code>[Type]</code> column from the index and run the query:</p>
  
  <pre><code>SELECT [Phone]
  FROM [dbo].[Store]
  WHERE [ParentStoreId] = 10
  --AND ([Type] = 0 OR [Type] = 1)
  ORDER BY [Phone]
  </code></pre>
  
  <p>Then of course the Sort operator is not used by the optimizer because <code>[Phone]</code> is sorted by <code>[ParentStoreId]</code>.</p>
  
  <p>So the question is how can I create an index that will cover the query (including the <code>[Type]</code> predicate) and not have the optimizer use a Sort?</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>The table I'm working with has more than 20 million rows</p>