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]) ;
We could have accomplished this using a summary table and triggers. Triggers are synchronous and updating the summary table will block our data changes from completing. In addition, if we ever want to bypass the trigger from updating (say during a huge data load) we need to disable and then enable the trigger again and then remember to update the summary table. With an indexed view we can just drop the clustered index and then recreate it when we’re done with our load processes.
Note: I can’t take full credit for this question and explanation. A SQL Saturday #42 attendee sent me an email with a question about using indexed views instead of triggers.
