Implementing indexed views on a SQL Server

Tags:
Indexed views
SQL Server
SQL Server administration
SQL Server configuration
SQL Server performance
How can I implement indexed views on our SQL Server? Is there an example that shows how they would improve query performance?

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following