When are SQL Server Views updated?

Question

I have two tables that are around 500k rows each (and growing). Inserts/Updates happen to these constantly, sometimes 100's per minute. The system is having performance issues, namely timeouts, on basic inserts into these tables. We've tuned our indexes, and done the usual optimizations. But I'm wondering if the fact that these two tables are referenced in 5 views with heavy joining might be detrimental. I always thought, maybe mistakenly, that as underlying tables change, the views that reference them change too. So if the tables are changing that much, maybe our system is getting overwhelmed by having to constantly play catch-up updating views.

Answer 1

Unless they're indexed views (you haven't mention such in your question), they're not "updated" at all.

Normal views are similar to a macro in C - they're just a convenient shorthand to hide a part of a larger expression. They're expanded out into the parse tree of whatever statement references them, and the entire tree is then compiled and optimized - at the point of usage.

---

For indexed views, you would be largely correct - the views are maintained as part of the same transaction that performs changes in the base tables. However, the rules for indexed views have been designed so that this update activity shouldn't incur too large a penalty (they can be maintained without having to re-query the entire base table).

Answer 2

It depends:

1) If the view is not indexed, then view is expanded

-- View definition
CREATE VIEW Sales.v_SalesOrderDetail
AS
SELECT  h.SalesOrderID, h.SalesOrderNumber, h.OrderDate, 
        d.SalesOrderDetailID, d.OrderQty, d.UnitPrice, d.LineTotal, 
        p.ProductID, p.Name AS ProductName, p.Color AS ProductColor
FROM    Sales.SalesOrderHeader h
INNER JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
INNER JOIN Production.Product p ON d.ProductID = p.ProductID
GO

-- View usage
SELECT  v.SalesOrderDetailID, v.OrderQty, v.UnitPrice, v.ProductName
FROM    Sales.v_SalesOrderDetail v
WHERE   v.ProductColor='Red';
GO

If we look at the execution plan (SSMS: Ctrl + M), then we will see that the view (FROM Sales.v_SalesOrderDetail v) is expanded and the server queries just two tables: Sales.SalesOrderDetail d and Production.Product p. More, we can see how the join between Sales.SalesOrderHeader h and Sales.SalesOrderDetail d is removed because:

• the SELECT clause (SELECT v.SalesOrderDetailID, v.OrderQty, v.UnitPrice, v.ProductName) doesn't includes columns from Sales.SalesOrderHeader table,

• between this two table there is a foreign key constraint and

• this FK constraint is trusted.

2) If the view is indexed (meaning that there is an UNIQUE CLUSTERED INDEX defined on the view) and the SQL Server edition is enterprise then the view could be expanded or not. If the edition <> enterprise then indexed view is expanded. We can force the server to not expands the [indexed] view by using NOEXPAND hint:

-- View definition
CREATE VIEW Sales.v_SalesOrderDetail2
WITH SCHEMABINDING
AS
SELECT  h.SalesOrderID, h.SalesOrderNumber, h.OrderDate, 
        d.SalesOrderDetailID, d.OrderQty, d.UnitPrice, d.LineTotal, 
        p.ProductID, p.Name AS ProductName, p.Color AS ProductColor
FROM    Sales.SalesOrderHeader h
INNER JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
INNER JOIN Production.Product p ON d.ProductID = p.ProductID
GO

-- Defining the UNIQUE CLUSTERED INDEX
CREATE UNIQUE CLUSTERED INDEX PK_v_SalesOrderDetail2
ON Sales.v_SalesOrderDetail2 (SalesOrderDetailID);
GO

-- View usage
SELECT  v.SalesOrderDetailID, v.OrderQty, v.UnitPrice, v.ProductName
FROM    Sales.v_SalesOrderDetail2 v WITH (NOEXPAND)
WHERE   v.ProductColor='Red';
GO

In this case, we can see that the execution plan includes the Clustered Index Scan PK_v_SalesOrderDetail2 operator. So, it uses the index defined on the second view.

Be aware: SQL Server bug indexed view + MERGE.