Parameter sniffing does not behave correctly with partitioned views - by Tryout User

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<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 766094 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 10/4/2012 7:43:11 AM
Access Restriction Public


When a query with a local variable issued against a partitioned view, all member tables are queried, regardless the value of the local variable.

When you change a local variable in a query to a value, only one table queried instead.
Sign in to post a comment.
Posted by Microsoft on 10/5/2012 at 10:37 AM
Thanks for taking the time to file this matter. The behavior is actually by design. The execution plan is cached and must therefore be capable of dealing with any value of @i. Note that there is a "startup expression" above each clustered index seek. That expression ensures that if the actual value of @i at runtime indicates there can be no qualifying rows in the corresponding index, then the server will not look inside that index.

Campbell, SQL Development