Using Indexed Views to Replace Triggers

Let’s get this out of the way: I think triggers are cool. I also think triggers have their place, they just need to be used with care. One reason for this is that triggers are synchronous. That is – trigger actions will block a command from returning to the client until the the trigger’s actions have completed. What’s the solution? Do we run summary jobs on a regular basis? Maybe. Sometimes summary jobs aren’t enough. Sometimes we have to update summarized data in real time. Let’s break out AdventureWorks and look at an example. Let’s say we have a report that shows us the total line item sales per customer broken out by year and month. That’s pretty simple, right?``` SELECT p.BusinessEntityID , p.FirstName , p.LastName , DATEPART(yyyy, soh.OrderDate) AS [Year] , DATEPART(mm, soh.OrderDate) AS [Month] , SUM(sod.LineTotal) AS LineItemTotal FROM Person.Person AS p INNER JOIN Sales.Customer AS c ON c.PersonID = p.BusinessEntityID INNER JOIN Sales.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID INNER JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID GROUP BY p.BusinessEntityID , p.FirstName , p.LastName , DATEPART(yyyy, soh.OrderDate) , DATEPART(mm, soh.OrderDate) ORDER BY p.BusinessEntityID , [Year] , [Month] ;

The problem is that this query may use a lot of CPU or disk resources. How can we avoid that? Earlier I mentioned using summary jobs. The problem is that the summary jobs will only run on a schedule and require that we maintain some kind of maintenance history of each row – even if that maintenance history is just a modification timestamp. Wouldn’t it be easier if our summary report just updated itself automagically? It can! Enter the indexed view. An indexed view is a regular view that has a clustered index on it. By adding the clustered index the view is persisted to disk. The upside of persisting the indexed view to disk is that there’s an automatic mechanism in place that will update the indexed view. An indexed view must have a unique clustered index so that SQL Server can identify the materialized row that must be updated in response to changes in the underlying data. CREATE VIEW dbo.my_indexed_view WITH SCHEMABINDING AS SELECT p.BusinessEntityID, p.FirstName, p.LastName, DATEPART(yyyy, soh.OrderDate) AS [Year], DATEPART(mm, soh.OrderDate) AS [Month], SUM(sod.LineTotal) AS LineItemTotal, COUNT_BIG(*) AS NumberOfRecords FROM Person.Person AS p INNER JOIN Sales.Customer AS c ON c.PersonID = p.BusinessEntityID INNER JOIN Sales.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID INNER JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID GROUP BY p.BusinessEntityID, p.FirstName, p.LastName, DATEPART(yyyy, soh.OrderDate), DATEPART(mm, soh.OrderDate) ; GO

CREATE UNIQUE CLUSTERED INDEX CX_my_indexed_view ON dbo.my_indexed_view (BusinessEntityID, [Year], [Month]) ;