Indexed views
An indexed view (sometimes referred to as a materialized view), is a view that has been made into more than just a simple stored query by creating a clustered index on it. By doing this, it basically makes it into a copy of data in a physical structure much like a table.
The first benefit of using an indexed view is that when you use it Enterprise Edition of SQL Server, it uses the stored data in the index structure. For Standard Edition, it uses the code of the query unless you use a NOEXPAND table hint, in which case it uses the clustered index representation.
A second benefit, which is very important, is that it is recalculated for every modification of the underlying data. If you need up to the second aggregations extremely fast, it is better than managing copying data using a trigger. This can also be a detriment, depending on how busy the server is as the aggregations are done synchronously, meaning other users may need to wait for locks to be released.
Finally, and the benefit that can be the biggest help is that, when using Enterprise Edition, SQL Server considers using the aggregates that are stored in your view for queries that look like the query, but doesn’t reference the view directly. Getting this to work depends on the needs being fairly limited. The limitations are pretty stiff. For example, a few common bits of coding syntax that are not allowed:
SELECT * syntax—columns must be explicitly named
UNION, EXCEPT, or INTERSECT
Subqueries
Outer joins or recursive join back to the same table TOP in the SELECT clauseDISTINCTSUM() function referencing more than one columnAlmost any aggregate function against an expression that can return NULLReference any other views, or use CTEs or derived tablesReference any nondeterministic functionsReference data outside the database.COUNT(*) – Must use COUNT_BIG(*)View not specifying SCHEMABINDING
TOP in the SELECT clause
DISTINCT
SUM() function referencing more than one column
Almost any aggregate function against an expression that can return NULL
Reference any other views, or use CTEs or derived tables
Reference any nondeterministic functions
Reference data outside the database.
COUNT(*) – Must use COUNT_BIG(*)
View not specifying SCHEMABINDING
We create a view in the WideWorldImporters database that a customer needed. It is pretty typical, and gives the sum of the cost of what they have purchased, the profit, and the number of line items.
CREATE VIEW Sales.InvoiceCustomerInvoiceAggregates WITH SCHEMABINDING AS SELECT Invoices.CustomerId, SUM(ExtendedPrice * Quantity) AS SumCost, SUM(LineProfit) AS SumProfit, COUNT_BIG(*) AS TotalItemCount FROM Sales.Invoices JOIN Sales.InvoiceLines ON Invoices.InvoiceID = InvoiceLines.InvoiceID GROUP BY Invoices.CustomerID;
CREATE UNIQUE CLUSTERED INDEX XPKInvoiceCustomerInvoiceAggregates on Sales.InvoiceCustomerInvoiceAggregates(CustomerId)
SELECT * FROM Sales.InvoiceCustomerInvoiceAggregates;
SELECT Invoices.CustomerId,
SUM(ExtendedPrice * Quantity) / SUM(LineProfit), COUNT(*) AS TotalItemCount FROM Sales.Invoices JOIN Sales.InvoiceLines
ON Invoices.InvoiceID = InvoiceLines.InvoiceID GROUP BY Invoices.CustomerID;
It uses the view that we created, because the building blocks used SUM(LineProfit), COUNT(*), and SUM(ExtendedPrice * Quantity) exist in the indexed view. In the plan, you see the same Clustered Index Scan operator, with two Compute Scalars, for the columns that are being output. Indexed views can be a useful tool to apply when you are dealing with a view that is costly and all or some of it can be put into an indexed view. Using indexed views in this manner is a niche use, but it is definitely possible and very powerful when needed. The typical use of indexed views is for reporting, and typically reporting in a reporting database where lots of data modifications are not taking place.
Example 1:
Users report that the query takes a long time to return results. You need to minimize the amount of time requires for the query to return data. What should you do?
A. Create clustered indexes on TBL1 and TBL2.
B. Create a clustered index on TBL1.Create a nonclustered index on TBL2 and add the most frequently queried column as included columns.
C. Create a nonclustered index on TBL2 only.
D. Create UNIQUE constraints on both TBL1 and TBL2. Create a partitioned view that combines columns from TBL1 and TBL2.
E. Drop existing indexes on TBL1 and then create a clustered columnstore index. Create a nonclustered columnstore index on TBL1.Create a nonclustered index on TBL2.
F. Drop existing indexes on TBL1 and then create a clustered columnstore index. Create a nonclustered columnstore index on TBL1.Make no changes to TBL2.
G. Create CHECK constraints on both TBL1 and TBL2. Create a partitioned view that combines columns from TBL1 and TBL2.
H. Create an indexed view that combines columns from TBL1 and TBL2.
Correct Answer: H
Example 2:
You are a database developer for a company that delivers produce and other refrigerated goods to grocery stores. You capture the food storage temperature for delivery vehicles by using Internet of Things (IoT) devices. You store the temperature data in a database table named VehicleTemperatures. The ChillerSensorNumber column stores the identifier for the IoT devices. You need to create an indexed view that meets the following requirements:
✑ Persists the data on disk to reduce the amount of I/O ✑ Provides the number of ChillerSensorNumber items ✑ Creates only a set of summary rows
How should you complete the view definition? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bat between panes or scroll to view content.
Example 3:
You need to create a view that can be indexed. You write the following statement.
What should you add at line 02?
A. with check_option
B. with recompile
C. with view_metadata
D. with schemabinding
Correct Answer: D
The following steps are required to create an indexed view and are critical to the successful implementation of the indexed view: 1. Verify the SET options are correct for all existing tables that will be referenced in the view. 2. Verify that the SET options for the session are set correctly before you create any tables and the view. 3. Verify that the view definition is deterministic. 4. Create the view by using the WITH SCHEMABINDING option. 5. Create the unique clustered index on the view.
References: https://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-2017