posted by | on Uncategorized | No comments


August 09, 2012 by rickmorelan

Identify Tables with an Index

So far we have accepted ‘on faith’ that views do not store their own data unless they are indexed. Let’s use the Object Explorer to find what Indexes are currently on the SalesInvoice base table. The screen shot below shows a clustered index on the InvoiceID field named CI_SalesInvoice_InvoiceID and a non-clustered index on the CustomerID field named NCI_SalesInvoice_CustomerID.

Creating an Index on a View

Let’s build a view called vSalesInvoice that is based on the SalesInvoice table.

CREATE VIEW vSalesInvoice
SELECT si.InvoiceID, si.OrderDate, si.CustomerID
FROM dbo.SalesInvoice AS si

Once created, the view does not take up any space of its own. To confirm this, execute the sp_spaceused system stored procedure, to clearly see that the a view without an index takes up no space in SQL Servers’ memory:

EXEC sp_spaceused vSalesInvoice

The code below will create a clustered index on the vSalesInvoice view based on the InvoiceID column. Once the index has been created the data will be materialized to the view.

ON dbo.SalesInvoice (InvoiceID)

Now that the index is created, the data will be materialized in the view and the indexed view will begin to occupy space in memory. By simply executing the sp_spaceused system stored procedure again, it will be evident how much of SQL Servers’ memory is being used now. SQL Server has reserved 64KB in the JProCo database for this indexed view:

EXEC sp_spaceused vSalesInvoice

Clustered Index View – Performance

After the view is indexed, a copy of the data is stored in the index and thus the view now occupies space in the JProCo database. Since the view has its own data, many SQL folks believe it will now use its own indexes for future queries. While this is often true, it is by no means always true.

Take a good look at the two execution plans for the two queries in the next example. The first query is run against the SalesInvoice table and the second query is run against the vSalesInvoice clustered indexed view.

Question: Which index will the first query use? Which index will the second query use?

FROM dbo.SalesInvoice
WHERE InvoiceID = 50

FROM dbo.vSalesInvoice
WHERE InvoiceID = 50

The previous code shows an example of how an indexed view will sometimes expand to access the index of the base table. In order to explicitly tell the query optimizer to use the clustered indexed view, the same way it works with a clustered index table, then it is necessary to use the NO EXPAND table hint.

Running the code below will show how the execution plan now uses the UCI_vSalesInvoice_InvoiceID from the clustered index view instead of the CI_SalesInvoice_InvoiceID clustered index from the table.

FROM dbo.vSalesInvoice (NO EXPAND)
WHERE InvoiceID = 50

This lesson is a condensed excerpt from Chapter 5:
SQL Architecture Basics: Core Architecture Concepts (SQL Exam Prep Series 70-433 Volume 4 of 5)

Welcome to Joes2Pros
Joes2Pros Academy
Joes2Pros Website