Home Dashboard Directory Help
Search

Simple Parameterization on Partitioned Tables Disables Partition Elimination by Paul White NZ


Status: 

Resolved
 as Won't Fix Help for as Won't Fix


7
0
Sign in
to vote
Type: Bug
ID: 762219
Opened: 9/11/2012 3:49:22 PM
Access Restriction: Public
1
Workaround(s)
view
2
User(s) can reproduce this bug

Description

Simple parameterization of a char column that is the partitioning column for a table prevents partition elimination when a literal value is used in a predicate on that column, unless an explicit cast is used to reduce the maximum length of the parameterized literal to the defined length of the partitioning column or less. By default, the literal is typed as varchar(8000).

As far as I can tell, SQL Server 2005 did not apply simple parameterization to a partitioned table, so partition elimination succeeds in the same scenario. When forced parameterization is enabled on SQL Server 2005, partition elimination succeeds where it fails on 2008 onward.

Repro in the details section below. See also http://bit.ly/PartElim
Details
Sign in to post a comment.
Posted by Microsoft on 4/26/2013 at 11:17 AM
Hello,

Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we are closing bugs that we will not fix in the current or future versions of SQL Server. Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we are closing bugs that we will not fix in the current or future versions of SQL Server. The reasons for closing this bug is because the scenario reported in the bug are not common enough and due to the risk of implementing a fix it unfortunately does not meet the bar for the current version of the product.

If you have more supporting information and details or feel strongly that we should reconsider, please do resubmit this item for review.

Thanks again for reporting the product issue and continued support in improving our product.
Gus Apostol, SQL Server Program Manager
Posted by Microsoft on 4/26/2013 at 11:17 AM
Hello,

Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we are closing bugs that we will not fix in the current or future versions of SQL Server. Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we are closing bugs that we will not fix in the current or future versions of SQL Server. The reasons for closing this bug is because the scenario reported in the bug are not common enough and due to the risk of implementing a fix it unfortunately does not meet the bar for the current version of the product.

If you have more supporting information and details or feel strongly that we should reconsider, please do resubmit this item for review.

Thanks again for reporting the product issue and continued support in improving our product.
Gus Apostol, SQL Server Program Manager
Posted by Microsoft on 10/2/2012 at 12:03 PM
Thank you for the feedback, Paul. I'll take under consideration for the next release since we are currently in the planning stages.

Rapinder Jawanda
Sr. Program Manager, DW Team
Sign in to post a workaround.
Posted by Paul White NZ on 9/11/2012 at 4:14 PM
OPTION (RECOMPILE) or any query syntax that disables simple parameterization, e.g. adding WHERE 1 <> 2;