I'm trying to index my views since the data is relatively static and it could increase performance.
I cannot index the view because it contains a "ranking or aggregate window function". Is there a workaround for that?
SELECT r.Id, r.Value, r.TestSessionId, t.Type AS TestType, r.StudentId, ROW_NUMBER() OVER (partition BY r.StudentId, r.TestSessionId ORDER BY r.Id) AS AttemptNumber
FROM dbo.Responses r
INNER JOIN dbo.TestSessions ts ON r.TestSessionId = ts.Id
INNER JOIN dbo.Tests t ON ts.TestId = t.Id
This view just adds an attempt number to student responses to questions, and I thought this would be a perfect scenario for an indexed view, but SQL Server doesn't support indexes on views with window functions.
I could generate a cache table manually, but I want this to be low maintenance so I don't have to remember to do something like that:
For example, perhaps I could create some kind of trigger (I'm not familiar with triggers) that inserts the view into a cache table when the base table is changed... which is basically what an index on a view is supposed to do under the hood (although more efficiency because it can update the index rather than completely replacing it when the base table data changes).