Home Dashboard Directory Help
Search

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


Status: 

Closed
 as By Design Help for as By Design


1
0
Sign in
to vote
Type: Bug
ID: 766094
Opened: 10/4/2012 7:43:11 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

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