Creating INDEX in VIEWS

Creating view with SCHEMABINDING

1. Ensure the view is created with the SCHEMABINDING option.

Important Considerations:

1. SCHEMABINDING: The view must be created with the SCHEMABINDING option, which means you cannot alter the underlying tables without first dropping the view.
2. Unique Clustered Index: You must create a unique clustered index on the view before creating any non-clustered indexes.
3. Indexed View Limitations: There are certain limitations and restrictions on what can be included in an indexed view. For example, you cannot use OUTER JOIN(LEFT or RIGHT JOIN), UNION, TOP, DISTINCT, SUM, AVG, MAX, MIN, COUNT, SUBQUERIES, etc.

Creating Index

•  in SQL Server, you cannot create a non-clustered index on a view without first creating a unique clustered index on that view. This is a requirement for indexed views (materialized views). The unique clustered index is necessary to ensure the view's data is stored in a consistent and efficient manner.

  Steps to Create Indexes on a View

  1. Create the View with SCHEMABINDING: Ensure the view is created with the SCHEMABINDING option.
  2. Create a Unique Clustered Index on the View: This is mandatory before creating any non-clustered indexes.
  3. Create Non-Clustered Indexes: Once the unique clustered index is created, you can create non-clustered indexes on the view.