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

The syntax is the following:

CREATE VIEW <view name>   
WITH SCHEMABINDING   
AS  
SELECT Column1, Column2, Column3   
FROM <table name>
GO  
CREATE UNIQUE CLUSTERED INDEX <index name> ON <view name>(Column1)  
GO

An example:

CREATE VIEW SalesLT.vOrders
   WITH SCHEMABINDING
   AS  
      SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,
         OrderDate, ProductID, COUNT_BIG(*) AS COUNT
      FROM SalesLT.SalesOrderDetail AS od, SalesLT.SalesOrderHeader AS o
      WHERE od.SalesOrderID = o.SalesOrderID
      GROUP BY OrderDate, ProductID;
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1
   ON Saleslt.vOrders (OrderDate, ProductID);
GO

Answer 3

here is the query