question

amit_adarsh avatar image
amit_adarsh asked

What is EXPAND VIEWS and NOEXPAND ?

What is EXPAND VIEWS and NOEXPAND ?
views
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
Those are both Hints that you can define within SQL Server. NOEXPAND is a Table hint documented [in the Books Online][1], SQL Servers documentation. NOEXPAND applies only to indexed views (also called materialized views) it tells the optimizer to just use the index defined and to not look at the underlying tables that define the view that has been indexed. This may be necessary in some circumstances where the indexed view would work faster than letting the optimizer access the underlying tables. EXPAND VIEWS is a query hint, also documented [in the books online][2]. 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. Always read the online documentation for fundamental issues. All these are well defined. [1]: http://technet.microsoft.com/en-us/library/ms187373.aspx [2]: http://msdn.microsoft.com/en-us/library/ms191432.aspx
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.