Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

On Indexes and Views

By Timothy A Wiseman,

Used properly, indexed views can be a magnificent way of improving performance and providing greater ease for both users and developers while still maintaining a fully normalized and constrained database. However, to fully realize the benefits of the indexed view, the execution plan must actually make use of it.

When a nonindexed view or a view whose index will not be used in the execution plan is referenced in a query, the optimizer considers the select statement the view represents the same way it would consider a subquery. In a way, this is very much like using the view as a macro. It saves the user or developer from having to type, or even be fully aware of, the exact contents of the view, but the optimizer looks at it as though the developer had typed it in.

When dealing with a view with an index that will be used, the optimizer can go to materialized data the view represents instead. In most cases, this is can be much more efficient than expanding the view, especially when the view includes multiple joins or complex where statements within it. So, while some exceptions do exist, it is generally desirable to ensure that the index is used.

According to Books Online, the optimizer for SQL Server 2005 Enterprise Edition will intelligently decide when it is best to use or ignore indexes on views. Not only that, it has the ability to consider using the indexes on views if they would by applicable to the base tables as well. That only applies to Enterprise Edition, to quote directly from the Resolving Indexes on Views article on MSDN:

To use an indexed view in all other editions, the NOEXPAND table hint must be used.

In short, indexed views in every version but Enterprise will only be used to their full potential if the developers and users carefully ensure that they appropriately use the NOEXPAND hint. Also, applying the NOEXPAND hint to a view which does not have an index will generate an error. It can be illuminative to see an example.

First, some test data is needed.:

/* Generate 3 test tables for test data.
The technique used get the initial tally table described by Jeff Moden in: The "Numbers" or "Tally" Table
*/ /* Set proper conditions
as described by MSDN in Creating Indexed Views*/
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF GO /* Create the tally table to be used. */
IF OBJECT_ID('dbo.Tally') IS NOT NULL
DROP TABLE dbo.Tally SELECT TOP 8000
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM MASTER.dbo.SysColumns sc1,
MASTER.dbo.SysColumns sc2 --===== Conditionally drop
IF OBJECT_ID('dbo.vwTestView') IS NOT NULL
DROP VIEW dbo.vwTestView IF OBJECT_ID('dbo.a') IS NOT NULL
DROP TABLE dbo.a

IF OBJECT_ID('dbo.b') IS NOT NULL
DROP TABLE dbo.b IF OBJECT_ID('dbo.c') IS NOT NULL
DROP TABLE dbo.c SELECT TOP 10000
N AS id,
N AS JoinB,
'a' AS ADATA
INTO
dbo.a
FROM
dbo.Tally SELECT TOP 10000
N AS id,
N AS JoinC,
'b' AS BDATA
INTO
dbo.b
FROM
dbo.Tally

SELECT TOP 10000
N AS id,
'c' AS CDATA
INTO
dbo.c
FROM
dbo.Tally

/* Add primary keys for performance */
GO ALTER TABLE dbo.a
ADD CONSTRAINT PK_Tally_a
PRIMARY KEY CLUSTERED (id) ALTER TABLE dbo.b
ADD CONSTRAINT PK_Tally_b
PRIMARY KEY CLUSTERED (id)

ALTER TABLE dbo.c
ADD CONSTRAINT PK_Tally_c
PRIMARY KEY CLUSTERED (id)


/* Create the view */
GO
CREATE VIEW vwTestView
WITH schemabinding /* Schemabinding is required to create the indexes */
AS
SELECT
a.ID,
a.ADATA,
b.BDATA,
c.CDATA
FROM
dbo.a a
JOIN dbo.b b
ON b.id = a.JoinB
JOIN dbo.c c
ON c.id = b.JoinC

/* Create the index */
GO
CREATE UNIQUE CLUSTERED INDEX [vwTestViewClusteed] ON [dbo].[vwTestView]
(
[ID] ASC
)


Then, run the select statements and compare:

SELECT
*
FROM
vwTestView

SELECT
*
FROM
vwTestView WITH (NOEXPAND)

And here are the execution plans:

 

The second plan generated with the NOEXPAND query hint is vastly more efficient than the one without because of the way they are processed. This particular execution plan was generated by SQL Server 2005 Express, but the same results come from Standard Edition as well.

One way to simplify this for developers and users is to use another view to hide the query hint. For instance:

CREATE VIEW vwTestViewX
AS
SELECT
*
FROM
dbo.vwTestView WITH (NOEXPAND)

Then a query against this view will include the NOEXPAND against the other and generate an appropriate execution plan like:

 

One thing to note with embedding the NOEXPAND hint is that the query will return an error if NOEXPAND is specified and no indexes exist on the view. This should rarely be an issue, but it is worth noting. This is especially true since if a change is made to one of the base tables using SSMS Designer, the Designer will display a confirmation message that the table is referenced with Schemabinding but then readily destroy the index if the confirmation is given. This can lead to indexes being destroyed in development environments very easily.

As long as the optimizer knows to use them, indexed views can provide enormous performance benefits. In the more powerful Enterprise Edition, the optimizer will handle this and there is rarely a reason to explicitly force their use. In other versions, though, the optimized must be explicitly told if they are needed. But, like other query hints, this explicit instruction can be made just once in a view on the indexed view if that is desirable.

REFERENCES
Creating Indexed Views by MSDN ( http://msdn.microsoft.com/en-us/library/ms191432.aspx )
Resolving Indexes on Views by MSDN ( http://msdn.microsoft.com/en-us/library/ms181151.aspx )
View Resolution by MSDN ( http://msdn.microsoft.com/en-us/library/ms190237.aspx )
The "Numbers" or "Tally" Table by Jeff Moden ( http://www.sqlservercentral.com/articles/TSQL/62867/ )

RELATED ARTICLES
What is Denormalization? By Chris Kempster ( http://www.sqlservercentral.com/articles/Advanced/whatisdenormalization/1204/ )

The Myth of Over-Normalization by Tony Davis (
http://www.simple-talk.com/community/blogs/tony_davis/archive/2008/07/21/63094.aspx )

Total article views: 12099 | Views in the last 30 days: 16
 
Related Articles
FORUM

Optimizer?

how Optimizer selects the index?

ARTICLE

Creating Memory-Optimized Table in SQL Server 2014

A quick introduction of how to create memory-optimized tables in SQL Server 2014

FORUM

query optimizer

OPTIMIZATION

FORUM

Tips on creating indexes

Tips on creating indexes

FORUM

Select Query Optimization

Need to Optimize a select query with many joins and text searching.

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones