Basic Event Sourcing in SQL Server using Indexed Views

Question

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.

Assume I have this table:

Id | Name      | Data   | CreatedOn
1  | Fruit     | Apple  | <DATE_TIME> Apr 1, 2015
2  | Fruit     | Peach  | <DATE_TIME> Apr 5, 2015 
3  | Fruit     | Mango  | <DATE_TIME> Apr 10, 2015
4  | Vegetable | Potato | <DATE_TIME> May 20, 2015
5  | Vegetable | Tomato | <DATE_TIME> May 30, 2015

I now want to create an view that shows me latest Data, grouped by Name, such that the result is:

Id | Name      | Data   | CreatedOn
3  | Fruit     | Mango  | <DATE_TIME> Apr 10, 2015
4  | Vegetable | Tomato | <DATE_TIME> May 30, 2015

Easy enough, 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;

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.

Any thoughts on how I could go about creating an Indexed View?

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.

Any other suggestions?

Answer 1

Your existing view defition would not have worked because SELECT TOP 1 Id FROM table ORDER BY CreatedOn DESC, Id DESC returns only one row but you would have needed one per outer row.

This query cannot be materialized with an indexed view. Even if you reformulate the query like this:

select *
from T t1
join (
 select Name, MAX(ID) as MaxID
 from T
 group by Name
) t2 on t1.name = t2.Name and t1.ID = t2.MaxID

You cannot materialize any meaningful part of that. Normally, this pattern can be used to materialize at least the group by part but here the MAX aggregated prevents that. And that makes sense because it is hard to maintain an indexed view and keep some kind of maximum value accurate in the presence of deletes or updates (inserts are easy).

Your options:

1. Properly index the base table.
2. Manually maintain a denormalized version of the data.