patternsqlMinor
Design best practices for last tracking with indexed view
Viewed 0 times
trackinglastwithdesignviewindexedpracticesforbest
Problem
My tables structure is below :
I want to have indexed view to get last TbDocActions columns for each
result of this view must be such as below :
For get this result with view I can use below query:
but I want indexed view to have better performance. and in indexed view I can't use
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.MaxActionIDbut 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
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.