SQL Server Indexed Views - Speed Up Your Select Queries: Part 1 - CodeProject

http://www.codeproject.com/Articles/199058/SQL-Server-Indexed-Views-Speed-Up-Your-Select-Quer[08/29/2012 4:20:35 PM]

9,219,840 members and growing!

Email Password Lost password?

Home Articles Quick Answers Discussions Zones Features Community Help!

Article Browse Code Stats Revisions Alternatives 4

» Database » Database » SQL Server

Licence CPOLFirst Posted 19 May 2011Views 11,349Bookmarked 8 times

SQL Server Indexed Views -Speed Up Your Select Queries:Part 1By andrewwasfy | 19 May 2011 | Article

SQL SQL-Server DBA Dev Beginner

An introduction to SQL Server Indexed Views.

Part of The SQL Zone sponsored by

See AlsoMore like thisMore by this author

4.46 (6 votes)

History about views

Views are a virtual table defining a query against one or more tables. Data isn’t stored in thedatabase and the result set is determined while the view is executed.

What’s an Indexed View?

An indexed view has a unique clustered index. The clustered index is stored in SQL Serverand updated like any other clustered index, providing SQL Server with another place to lookto potentially optimize a query utilizing the indexed view. Queries that don’t specifically usethe indexed view can even benefit from the existence of the clustered index from the view. Inthe developer and enterprise editions of SQL Server, the optimizer can use the indexes ofviews to optimize queries that do not specify the indexed view. In the other editions of SQLServer, however, the query must include the indexed view and specify the hint NOEXPAND toget the benefit of the index on the view. If your queries could benefit from having more thanone index on the view, non-clustered indexes can also be created on the view. This wouldsupply the optimizer with more possibilities to speed up the queries referencing the columnsincluded in the view.

Where to use them?

Indexed views have both a benefit and a cost. The cost of an indexed view is on themaintenance of the clustered index (and any non-clustered indexes you may choose to add).One must weigh the cost to maintain the index against the benefit of query optimizationprovided by the index. When the underlying tables are subject to significant inserts, updates,and deletes, be very careful in selecting the indexes (both table and view) that will providethe greatest coverage across your queries for the lowest cost. Typically, environments thatare best suited for indexed views are data warehouses, data marts, OLAP databases, and thelike. Transactional environments are less suitable for indexed views. Look for repeating joinsutilizing the same columns, joins on large tables, aggregations on large tables, and repeatingqueries as potential candidates for indexed views. Be careful of creating indexed views wherethe result set contains more rows than the base tables as this will be counterproductive.

How to create them?

A view that is to be indexed has to be created with schema binding. This means that once theindexed view is created, the underlying tables cannot be altered in any way that wouldmaterially affect the indexed view unless the view is first altered or dropped. It also meansthat all the tables referenced in the view must be referenced by their two-part name

SQL Server Indexed Views - Speed Up Your Select Queries: Part 1 - CodeProject

http://www.codeproject.com/Articles/199058/SQL-Server-Indexed-Views-Speed-Up-Your-Select-Quer[08/29/2012 4:20:35 PM]

(schemaname.tablename). Below is an example of the CREATE statement for an indexedview, MyView, and its underlying table, MyBigTable. The table is first created, then the viewthat references two of the table’s three columns, and finally the unique clustered index on theview making it an indexed view.

Once this index is created, the result set of this view is stored in the database just like anyother clustered index. Any query that explicitly uses the view will be able to take advantageof the index on the view. Queries that contain a predicate similar to the view and that fallinto the range defined by the view may also reap the optimization rewards of having thatindex available (assuming the execution cost is non-trivial). Consider the following query:

Even though the query does not use the indexed view, the optimizer has the option of usingthe clustered index created on the view if it provides better performance than the clustered ornon-clustered indexes on the base table. If you want the optimizer to always choose theindexed view over the base tables when optimizing a query containing an index view, youmust use the hint NOEXPAND. Conversely, if you’d like to see how a query containing anindexed view would perform utilizing the base tables instead, you can specify the optionEXPAND VIEWS, thus saving you the time substituting the base tables yourself.

Constraints

An index cannot be created on just any view. Several constraints exist that a view must meetin order for the index creation to be successful. We discussed WITH SCHEMABINDING andtwo-part table names above. Here are some other constraints:

The view must have been created with certain SET options, such asQUOTED_IDENTIFIER and CONCAT_NULL_YIELDS_NULL set to ON.The session creating the index must also have the correct SET options.Any user-defined function referenced by the view must have been created using WITHSCHEMABINDING.The view must be deterministic (consistently providing the same result given the sameinput).The base tables must have been created with the proper ANSI_NULLS setting.The result set of the view is physically stored in the database, thus storage space forthe clustered index is also a constraint to consider.

In addition to this, there are constraints on the contents of the view. For instance, the viewmay not contain EXISTS or NOT EXISTS, OUTER JOIN, COUNT(*), MIN, MAX, subqueries,table hints, TOP, UNION, and much more. Check the SQL Server Development Center onMSDN for a complete listing.

References

http://technet.microsoft.com/en-us/library/cc917715.aspx

License

This article, along with any associated source code and files, is licensed under The CodeProject Open License (CPOL)

About the Author

andrewwasfy

Software Developer

CREATE TABLE OAGTable(ID INT PRIMARY KEY, Depart VARCHAR(3),arrival VARCHAR(3),DurationinMin int )GO

CREATE VIEW MyView WITH SCHEMABINDING ASSELECT ID, DurationFROM dbo. OAGTableWHERE DurationinMin > 300GOCREATE UNIQUE CLUSTERED INDEX idx_MyView ON MyView(DurationinMin)

SELECT IDFROM OAGTableWHERE DurationinMin > 400

SQL Server Indexed Views - Speed Up Your Select Queries: Part 1 - CodeProject

http://www.codeproject.com/Articles/199058/SQL-Server-Indexed-Views-Speed-Up-Your-Select-Quer[08/29/2012 4:20:35 PM]

Article Top

Search this forum

(Senior)Majisa

Egypt

Member

Comments and Discussions

You must Sign In to use this message board.

Profile popups Noise Medium Layout Normal Per page 25

Refresh First Prev Next

Abed-elfattah Yacoub 0:57 6 Aug '11

kornakar 21:39 19 May '11

Sign Up to vote Poor Excellent

My vote of 4

My vote of 2