indexes in view

Question

Hi Expert,

How to create indexes in view as it is taking more than hour to run

alter view view1 as select col1,col2,col3... from table1... total 35 columns

Answer 1

Hi @Shambhu Rai

Unlike regular views, indexed views are materialized views that stores data physically like a table hence may provide some the performance benefit if they are used appropriately.

To create an indexed view, you use the following steps:

• First, create a view that uses the WITH SCHEMABINDING option which binds the view to the schema of the underlying tables.
• Second, create a unique clustered index on the view. This materializes the view.

Because of the WITH SCHEMABINDING option, if you want to change the structure of the underlying tables which affect the indexed view’s definition, you must drop the indexed view first before applying the changes.

In addition, SQL Server requires all object references in an indexed view to include the two-part naming
convention i.e., schema.object, and all referenced objects are in the same database.

Refer to this article for more details and samples: SQL Server Indexed View

--add a unique clustered index to the view:
CREATE UNIQUE CLUSTERED INDEX 
       ucidx_product_id 
ON production.product_master(product_id);

--add a non-clustered index to the view:
CREATE NONCLUSTERED INDEX 
    ucidx_product_name
ON production.product_master(product_name);

Note: When the data of the underlying tables changes, the data in the indexed view is also automatically updated. This causes a write overhead for the referenced tables. It means that when you write to the underlying table, SQL Server also has to write to the index of the view. Therefore, you should only create an indexed view against the tables that have in-frequent data updates.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

It is unlikely that an indexed view is the solution to your problem.

Since you did not show the query you are actually running or the full view definition, we cannot say anything more.

Answer 3

You can create indexed views and you can read here the basics of indexed views to improve performance. Take in consideration this indexed views do not support all data types and not all the features. Read more pros and cons about indexed views here, before you use them.

Please try not to create nested views or you will find query plans will get more complicated.

One personal suggestion, sometimes when you want to build a view, you should really build a stored procedure, especially on reporting systems scenarios. Stored procedures allow parameters and can receive a reduced result data set and perform faster.

Answer 4

You can create indexes on views. But indexed views have a lot of restrictions and may cause performance problems, particularly if there are lots of changes to the data in the underlying table(s).

The documentation for creating and using indexed views is available at https://learn.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-ver16

Tom

Answer 5

any index example please