Home Dashboard Directory Help
Search

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


Status: 

Closed
 as Fixed Help for as Fixed


1
0
Sign in
to vote
Type: Bug
ID: 776322
Opened: 1/11/2013 4:06:13 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

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.
Details
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]
Sign in to post a workaround.