title: SQL Server Index Design
body: <p>I have a table that holds a history of events that have occurred over time, such as:</p>
  
  <pre><code>EventType | OccurredOn       | Other.. | Data..
  --------------------------------------------
  1         | 2014/10/02 09:00 | foo     | bar
  1         | 2014/10/02 10:00 | foo     | bar
  2         | 2014/10/03 09:00 | foo     | bar
  1         | 2014/10/04 09:00 | foo     | bar
  3         | 2014/10/05 09:00 | foo     | bar
  2         | 2014/10/05 09:00 | foo     | bar
  4         | 2014/10/05 09:00 | foo     | bar
  </code></pre>
  
  <p>All my queries on this table will be of the form:</p>
  
  <pre><code>select [OccurredOn], [OtherData]
  from [Data]
  where [EventType] = @type and [OccurredOn] between @start and @end
  </code></pre>
  
  <p>perhaps, with <code>grouping</code> and selecting of aggregate data.</p>
  
  <p>Rows will be inserted almost always in time order, when they are inserted out of time order, the <code>OccurredOn</code> should be reasonably "recent" - i.e. not too far before the "latest" row already inserted</p>
  
  <p>How should I index this table for efficient selecting?</p>
  
  <p>Presumably my PK should be clustered, and include <code>OccurredOn</code> it, however there is no natural unique key, so presumably I need an <code>autoincrement int</code> column too?</p>
  
  <p><strong>Update</strong></p>
  
  <p>In terms of rows, the table will have millions, possibly 10s of millions</p>
  
  <p>I'm using SQL Server 208R2</p>