Happy Wednesday folks! Hope everyone’s having a great week. This episode of SQL Server A to Z is brought to you by the letter V, and V stands for vacation views!

What is a view?

A view is a “virtual table” that contains columns from one or more tables. Although you can query it like a real table, it doesn’t contain any actual data. The view is defined by a select statement that executes when you query the view. One advantage of using a view is that it can greatly simplify some of your code, masking an ugly query from end users and developers alike without having to store duplicate data. For example, in the AdventureWorks database, there’s a view called HumanResources.vEmployeeDepartmentHistory with the following definition:

CREATE VIEW [HumanResources].[vEmployeeDepartmentHistory]
AS
SELECT
    e.[EmployeeID]
    ,c.[Title]
    ,c.[FirstName]
    ,c.[MiddleName]
    ,c.[LastName]
    ,c.[Suffix]
    ,s.[Name] AS [Shift]
    ,d.[Name] AS [Department]
    ,d.[GroupName]
    ,edh.[StartDate]
    ,edh.[EndDate]
FROM [HumanResources].[Employee] e
    INNER JOIN [Person].[Contact] c
    ON c.[ContactID] = e.[ContactID]
    INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh
    ON e.[EmployeeID] = edh.[EmployeeID]
    INNER JOIN [HumanResources].[Department] d
    ON edh.[DepartmentID] = d.[DepartmentID]
    INNER JOIN [HumanResources].[Shift] s
    ON s.[ShiftID] = edh.[ShiftID];
GO

Now you could write out that entire query in any piece of code where you want to obtain employee department history information. But isn’t it easier and cleaner to deploy this code once and then just query the view from then on?

SELECT * FROM [HumanResources].[vEmployeeDepartmentHistory]
GO

Much better, no?

You can also use views as a security mechanism to abstract certain columns or rows from end users. In AdventureWorks, there’s a table called HumanResources.EmployeePayHistory. Suppose you want employees to be able to see their own pay history, but obviously you don’t want them seeing other people’s history. One solution would be to create a view that will only allow them to see their own history.

CREATE VIEW MyPayHistory AS
SELECT ph.EmployeeID
  ,ph.RateChangeDate
  ,ph.Rate
  ,ph.PayFrequency
  ,ph.ModifiedDate
from HumanResources.EmployeePayHistory ph
JOIN dbo.MyEmployees e on ph.EmployeeID = e.EmployeeID
WHERE e.LoginID = system_user
GO
SELECT * FROM MyPayHistory

Indexed Views

Beginning in SQL Server 2000, you can now create a unique clustered index on a view, in order to gain performance benefits. A view with a clustered index is called an indexed view. The reason indexed views out-perform their ordinary counterparts is that, while a normal view will execute its underlying query at runtime, an indexed view precalculates the view and stores the results on disk. My fellow recovering Oracle DBAs might recognize the concept as “materialized views” in that world. Because the data is physically persisted on disk, you’ll want to keep storage requirements in mind when creating an indexed view.

Another cool feature of using indexed views is that you don’t necessarily have to reference the view to use it. Huh? If you run a query on base tables that the optimizer decides can be better satisfied using an existing indexed view, it will automatically use that view to satisfy the query. You don’t need to modify your query at all. This is great for existing applications, they get the performance boost of the indexed view without having to change any code.

The rules for creating an indexed view are a little more stringent than a normal view.

• First, all columns must be explicitly listed in the definition. None of this SELECT * stuff.
• Second, all tables must use schema.table naming convention
• Third, the view must be created with SCHEMABINDING

What’s SCHEMABINDING?

The definition from MSDN states:

Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.

Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, the Database Engine raises an error. Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition.

In other words, it stops you from altering any of the underlying objects on which the view is based.

NOEXPAND

Let’s take an existing view from the AdventureWorks database and make it an indexed view. I’ll use HumanResources.vEmployee.

CREATE VIEW [HumanResources].[ivEmployee] WITH SCHEMABINDING
AS
SELECT
    e.[EmployeeID]
    ,c.[Title]
    ,c.[FirstName]
    ,c.[MiddleName]
    ,c.[LastName]
    ,c.[Suffix]
    ,e.[Title] AS [JobTitle]
    ,c.[Phone]
    ,c.[EmailAddress]
    ,c.[EmailPromotion]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,sp.[Name] AS [StateProvinceName]
    ,a.[PostalCode]
    ,cr.[Name] AS [CountryRegionName]
    ,c.[AdditionalContactInfo]
FROM [HumanResources].[Employee] e
    INNER JOIN [Person].[Contact] c
    ON c.[ContactID] = e.[ContactID]
    INNER JOIN [HumanResources].[EmployeeAddress] ea
    ON e.[EmployeeID] = ea.[EmployeeID]
    INNER JOIN [Person].[Address] a
    ON ea.[AddressID] = a.[AddressID]
    INNER JOIN [Person].[StateProvince] sp
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode];
GO
CREATE UNIQUE CLUSTERED INDEX [idx_EmployeeID] ON [HumanResources].[ivEmployee] ([EmployeeID])
GO

Ok, we have an indexed view, now we want to use it. So we run the following query:

SELECT * FROM HumanResources.ivEmployee
GO

And we look at the execution plan.

What the heck is this? Why isn’t it using our nice new indexed view? Well, by default, SQL Server will expand views at execution time down to their base tables (called, fittingly, “view expansion”). In order to have SQL Server use our indexed view as a table, as we most likely want it to do, we need to use the NOEXPAND hint.

SELECT * FROM HumanResources.ivEmployee WITH (NOEXPAND)
GO

Much better! If you were to execute both queries (with and without the NOEXPAND hint) in the same batch and get the execution plans, you’d see that the query without the hint makes up 98% of the cost of the batch!

Further Reading

For more information on views, indexed or otherwise, please check out the following resources.

CREATE VIEW (Transact-SQL)

Improving Performance with SQL Server 2008 Indexed Views