title: Basic Event Sourcing in SQL Server using Indexed Views
body: <p>I'm trying to implement very basic Event Sourcing using a SQL Server. No libraries, no other servers, nothing fancy, just from basics using SQL Server.</p>
  
  <p>Assume I have this table:</p>
  
  <pre><code>Id | Name      | Data   | CreatedOn
  1  | Fruit     | Apple  | &lt;DATE_TIME&gt; Apr 1, 2015
  2  | Fruit     | Peach  | &lt;DATE_TIME&gt; Apr 5, 2015 
  3  | Fruit     | Mango  | &lt;DATE_TIME&gt; Apr 10, 2015
  4  | Vegetable | Potato | &lt;DATE_TIME&gt; May 20, 2015
  5  | Vegetable | Tomato | &lt;DATE_TIME&gt; May 30, 2015
  </code></pre>
  
  <p>I now want to create an view that shows me <em>latest</em> Data, grouped by Name, such that the result is:</p>
  
  <pre><code>Id | Name      | Data   | CreatedOn
  3  | Fruit     | Mango  | &lt;DATE_TIME&gt; Apr 10, 2015
  4  | Vegetable | Tomato | &lt;DATE_TIME&gt; May 30, 2015
  </code></pre>
  
  <p>Easy enough, <code>CREATE VIEW Latest AS SELECT t1.* FROM table t1 JOIN (SELECT TOP 1 Id FROM table ORDER BY CreatedOn DESC, Id DESC) t2 ON t2.Id = t1.Id;</code></p>
  
  <p>Now I'm going to have many million rows in the table, and many many concurrent reads on the view. So I'd like to index it (SQL Server Indexed Views). Seems I'm out of luck here, since Indexed Views can't contain derived tables OR TOP OR MAX OR self-joins.</p>
  
  <p>Any thoughts on how I could go about creating an Indexed View?</p>
  
  <p>If that's impossible, I could use a INSTEAD OF Trigger, which updates an IsLatest BIT column in the table, and create an indexed view based on this filter.</p>
  
  <p>Any other suggestions?</p>