title: SQL index for date range query
body: <p>For a few days, I've been struggling with improving the performance of my database and there are some issues that I'm still kind a confused about regarding indexing in a SQL Server database.</p>
  
  <p>I'll try to be as informative as I can.</p>
  
  <p>My database currently contains about 100k rows and will keep growing, therfore I'm trying to find a way to make it work faster.</p>
  
  <p>I'm also writing to this table, so if you suggestion will drastically reduce the writing time please let me know.</p>
  
  <p>Overall goal is to select all rows with a specific names that are in a date range.</p>
  
  <p>That will usually be to select over 3,000 rows out of a lot lol ...</p>
  
  <p>Table schema:</p>
  
  <pre><code>CREATE TABLE [dbo].[reports]
  (
      [id] [int] IDENTITY(1,1) NOT NULL,
      [IsDuplicate] [bit] NOT NULL,
      [IsNotValid] [bit] NOT NULL,
      [Time] [datetime] NOT NULL,
      [ShortDate] [date] NOT NULL,
      [Source] [nvarchar](350) NULL,
      [Email] [nvarchar](350) NULL,
  
      CONSTRAINT [PK_dbo.reports] 
          PRIMARY KEY CLUSTERED ([id] ASC)
  ) ON [PRIMARY]
  </code></pre>
  
  <p>This is the SQL query I'm using:</p>
  
  <pre><code>SELECT * 
  FROM [db].[dbo].[reports]
  WHERE Source = 'name1' 
    AND ShortDate BETWEEN '2017-10-13' AND '2017-10-15'
  </code></pre>
  
  <p>As I understood, my best approach to improve efficency without hurting the writing time as much would be to create a nonclustered index on the <code>Source</code> and <code>ShortDate</code>.</p>
  
  <p>Which I did like such, index schema:</p>
  
  <pre><code>CREATE NONCLUSTERED INDEX [Source&amp;Time] 
  ON [dbo].[reports]([Source] ASC, [ShortDate] ASC)
  </code></pre>
  
  <p>Now we are getting to the tricky part which got me completely lost, the index above sometimes works, sometime half works and sometime doesn't work at all....</p>
  
  <p>(not sure if it matters but currently 90% of the database rows has the same Source, although this won't stay like that for long)</p>
  
  <ol>
  <li><p>With the query below, the index isn't used at all, I'm using SQL Server 2014 and in the Execution Plan it says it only uses the clustered index scan:</p>
  
  <pre><code>SELECT * 
  FROM [db].[dbo].[reports]
  WHERE Source = 'name1' 
    AND ShortDate BETWEEN '2017-10-10' AND '2017-10-15'
  </code></pre></li>
  <li><p>With this query, the index isn't used at all, although I'm getting a suggestion from SQL Server to create an index with the date first and source second... I read that the index should be made by the order the query is? Also it says to include all the columns Im selecting, is that a must?... again I read that I should include in the index only the columns I'm searching.</p>
  
  <pre><code>SELECT * 
  FROM [db].[dbo].[reports]
  WHERE Source = 'name1' 
    AND ShortDate = '2017-10-13'
  </code></pre>
  
  <p>SQL Server index suggestion - </p>
  
  <pre><code>/* The Query Processor estimates that implementing the following 
     index could improve the query cost by 86.2728%. */
  
  /*
  USE [db]
  GO
  
  CREATE NONCLUSTERED INDEX [&lt;Name of Missing Index, sysname,&gt;]
  ON [dbo].[reports] ([ShortDate], [Source])
  INCLUDE ([id], [IsDuplicate], [IsNotValid], [Time], [Email])
  GO
  */
  </code></pre></li>
  </ol>
  
  <p>Now I tried using the index SQL Server suggested me to make and it works, seems like it uses 100% of the nonclustered index using both the queries above.</p>
  
  <p>I tried to use this index but deleting the included columns and it doesn't work... seems like I must include in the index all the columns I'm selecting?</p>
  
  <p>BTW it also work when using the index I made if I include all the columns.</p>
  
  <p>To summarize: seems like the order of the index didn't matter, as it worked both when creating <code>Source + ShortDate</code> and <code>ShortDate + Source</code></p>
  
  <p>But for some reason its a must to include all the columns... (which will drastically affect the writing to this table?)</p>
  
  <p>Thanks a lot for reading, My goal is to understand why this stuff happens and what I should do otherwise (not just the solution as I'll need to apply it on other projects as well ).</p>
  
  <p>Cheers :)</p>