Lesson 36. Indexed Views

You create indexed views when you want a high performing view.

An index is an object on a table that orders information on disk in a manner in which you define. Having an index drastically increases query performance. A primary key is a form of an index.

As the name suggest, an indexed view is a view with an index. An indexed view is bound to the tables that the view is based on. This creates and important constraint.

You cannot change a table a view is based on without first destroying the connection between the view and source tables.

This is where knowing T-SQL comes in handy. When I need to make a change to a table, I will do it programmatically. In the script prior to the alter statement, I will drop the view. After the alter statement, I will recreate the view.

My primary use case for indexed views is using them to mirror data warehouse tables. I do not allow anybody that is not a data engineer access to the original tables. Not even on a read only basis. I create the index views for reports analyst. These are people that know SQL, but have no need to see the audit columns used to run the database.

Examples

Indexed View Creation

In the below example, I create a view called Person and bind it to the Person table. I am able to have two objects named the same because I do not specify a schema for the view. Since I do not specify a schema, the view will go into the dbo schema where there is not an object already named person. This will come in handy as you will see in the section titled Loading Large CSVs Into Data Warehouse Staging Tables.

In [ ]:

USE AdventureWorks2016


DROP VIEW IF EXISTS Person

GO

CREATE VIEW Person
 
WITH SCHEMABINDING  
AS  
SELECT
BusinessEntityID,
FirstName,
LastName
FROM Person.Person
GO  
--Create an index on the view.  
CREATE UNIQUE CLUSTERED INDEX CIDX_PERSON_BUSINESSENTITYID ON Person(BusinessEntityID);  
GO