Indexed Views/Materialized Views/NOEXPAND

Share this blog:
  •  
  •  
  •  
  •  
  •  

An indexed view is a view with a unique clustered index created on it. It is also called Materialized View.

If a query contains references to columns that are present both in an indexed view and base tables, and the query optimizer determines that using the indexed view provides the best method for executing the query, the query optimizer uses the index on the view.

NOEXPAND is a Table Hint. NOEXPAND applies only to indexed views. This option forces the optimizer to use index defined on an indexed view.  SQL Server will only automatically create statistics on an indexed view when a NOEXPAND table hint is used. 

EXPAND VIEWS is a query hint. It’s the opposite of NOEXPAND. It will force all statements within the query defined to not use indexed views and instead to expand them out. This is the opposite situation where expanding the views may work faster than letting the optimizer choose to access the materialized view. 5

Leave a Reply

Your email address will not be published. Required fields are marked *

8 − 7 =