When are SQL Server Views updated?


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),
enter image description here
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 becase:
- 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
enter image description here
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.

Article source: http://stackove rflow.com/questions/14814884/when-are-sql-server-views-updated

Comments are closed.