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 (
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...