Some query hints Errors should have a Warning Only option - by Lakusha

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 293508 Comments
Status Closed Workarounds
Type Suggestion Repros 2
Opened 8/15/2007 4:16:08 PM
Access Restriction Public


Using the NOEXPAND hint can result in an error:

Msg 8171, Level 16, State 2, Line 1
Hint 'noexpand' on object 'dbo.ervStats' is invalid.

That behavior can cause problems and I would expect a Warning to be raised, not an error since the problem does not affect the result of the query at all. 



 OLTP application using one or more indexed view to speed up some functionalities. This can happen as some db are OLTP but also used for real time reporting that can't be done on a mirror. At some point you may find out 1 indexed view is the cause of excessive locking for 1 specific site, so you remove the index....but must now recompile and redeploy the application to hundreds of desktops to remove that 8171 error message.

 If a warning was issued instead of an error, the problem would not occur. Nothing is changed at the LOGICAL level and the query would run as expected is the hint was ignored. 

Some concept would hold true for other hints (index hint on a dropped indexed for example).
Sign in to post a comment.
Posted by JeremyWeir on 3/9/2017 at 8:31 PM
It should be possible to switch a view from being schema bound to not schema bound without breaking calling queries.

Another suggestion would be to have a connection setting to make all indexed views forced to be with (noexpand), like


Posted by Branko Dimitrijevic on 10/30/2015 at 1:18 AM
It's 2015 now, and this is still a problem.

If it's too difficult changing NOEXPAND for you guys, maybe implementing "TRYNOEXPAND" on top of it would be easier?
Posted by Microsoft on 1/30/2012 at 1:30 AM

Thank you for submitting this suggestion, but given its priority relative to the many other items in our queue, it is unlikely that we will actually complete it. As such, we are closing this suggestion as “won’t fix”. If you feel that this is worth reconsidering, feel free to respond to this message and we will take another look.

Thank you,
Boris Baryshnikov.
SQL Server Engine
Posted by AKuz on 6/2/2009 at 4:54 PM
I completely agree.
Posted by Microsoft on 12/17/2007 at 2:58 PM
Dear customer,
Thank you for the valuable feedback! Your suggestion regarding a warning instead of an error when using the NOEXPAND hint is a worthwhile idea. However, for the time being, the issue is beyond the scope of Katmai and will be considered again for our next major release.
Thank you and best regards.