HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Design best practices for last tracking with indexed view

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
trackinglastwithdesignviewindexedpracticesforbest

Problem

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.

Solution

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

Context

StackExchange Database Administrators Q#19360, answer score: 6

Revisions (0)

No revisions yet.