Design best practices for last tracking with indexed view

Question

My tables structure is below :

  TbDoc (ID int , ...)
  TbDocActions( ID Int, DocID Int, Date DateTime, col1 int, col2 int, ...)

I want to have indexed view to get last TbDocActions columns for each TbDoc record. result of this view must be such as below :

  DocID , col1, col2, ...

For get this result with view I can use below query:

Select Z.DocID, X.*
From (Select DocID, Max(ID) as MaxActionID
      From TbDocActions
      Group By DocID
     )Z
inner join TbDocActions X ON X.ID = Z.MaxActionID

but I want indexed view to have better performance. and in indexed view I can't use Max() aggregate function.

Answer 1

The fastest solution is as follows: you create an additional column, IsLastID, and build a filtered index or an indexed view using it. You can use constraints to ensure the integrity of IsLastID, as described here

Grant Fritchey wrote up a detailed comparison of various solutions here

Answer 2

As you already know, you can't use a MAX() aggregate in an indexed view. In any case don't fool yourself into believing that creating an indexed view is a magical performance power button. Depending on your workload, maintaining the indexed view may cost you more performance than you're gaining from the queries against it.

A couple of alternatives might be:

(1) create a non-clustered index on dbo.TbDocActions(DocID, ID DESC) or drop the existing clustered index and change it to DocID, ID DESC. (If ID is an identity column, you've probably also set it up to be the primary key and it will be clustered by default, but that doesn't have to be the case.)

(2) maintain a separate table with the DocID as the primary key, and keep the highest ID up to date using triggers (or a stored procedure if you are constraining writes to stored procedures only). You will have to have logic for all three DML types (insert, update and delete) because, in theory, any of these could affect the MAX calculation. (Less risk, obviously, if both ID columns are IDENTITY.) You could also add a column to the main table and keep that up to date the same way.

Note that the same caveats apply - adding indexes or maintaining values in other tables might benefit this specific query, but they are not magic - they might affect other parts of your workload. You should always take your entire business cycle into consideration when thinking about "fixing" any single query...