Indexed views are typically only truly useful on the largest of systems. They are useful when you have a common query against two or more tables where you need a single index against both tables to improve the performance of the query.

In my 10 years of being a DBA I've needed them maybe twice.

To create an indexes view, setup your tables that you wish to create the view on. They should have primary keys defined, as well as indexes created on the columns which will be used for the JOIN syntax.

Setup your view, and be sure to use the SCHEMABINDING clause when creating the view.

You can now create a clustered index, and non-clustered indexes on the view.

Do keep in mind that all inserts, updates and deletes to any of the underlying tables will now take longer than they did before as these additional indexes will need to be updated. If you remove a large amount of data from any of these tables (such as a data purge) the increased amount of time to be used will be very noticeable.

Please complete the following information:

REGISTER / LOG-IN TO DISCUSS

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Submit your e-mail address below to continue

E-mail: 

E-mail: 

Handle:   

Password: 

Forgot Password?

Create Handle: 

Your handle identifies you in ITKnowledgeExchange. Max 30 char.

Create Password: 

Confirm Password: 

Yes, I agree to the IT Knowledge Exchange  Terms and Conditions.

hidden modal window