Object creation order causes views to fail when using NOEXPAND - by SQL_DBA_OTENT

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


1
0
Sign in
to vote
ID 776322 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 1/11/2013 4:06:13 AM
Access Restriction Public

Description

When using the ..Tasks..Generate Scripts method the order that objects are scripted are critical when using indexed views and objects that reference them using the NOEXPAND hint.

If you have view2 that references view1 (which is an indexed view) and use the NOEXPAND hint in view2 the script will fail because all the view definitions are created first before the clustered indexes.  This means that when view2 gets created it complains about the NOEXPAND because at that point view1 is not considered to be an indexed view.  The index for view1 is added further down the script.

To solve this issue the clustered index for view1 should be created directly after the view1 definition, this would then make the NOEXPAND hint inside view2 valid.
Sign in to post a comment.
Posted by Microsoft on 4/26/2013 at 9:14 AM
We are happy to inform you that the issue is fixed in the next SQL release.
Thanks,
Olga Pechuk [MSFT SQL SERVER]
Posted by Microsoft on 2/19/2013 at 2:39 PM
Thank you for reporting this issue - we are investigating and will update you when we have more information.

Thanks,

Alex Grach [MSFT SQL SERVER]