SQL optimization - execution plan changes based on constraint value - Why?

SQL Server is smart enough to take CHECK constraints into account when optimizing the queries Your f. FieldNumber 1 This means that for larger values of m the fullscan is more efficient SQL Server is aware of that and changes execution plans accordingly to the constants that affect the data distribution in the set operations TO do this SQL Server maintains statistics : aggregated histograms of the data distribution in each indexed column and uses them to build the query plans So changing the integer in the WHERE condition in fact affects the size and the data distribution of the underlying sets and makes SQL Server to reconsider the algorithms best fit to work with the sets of that size and layout.

SQL Server is smart enough to take CHECK constraints into account when optimizing the queries. Your f. FieldNumber 1.

This means that for larger values of m the fullscan is more efficient. SQL Server is aware of that and changes execution plans accordingly to the constants that affect the data distribution in the set operations. TO do this, SQL Server maintains statistics: aggregated histograms of the data distribution in each indexed column and uses them to build the query plans.So changing the integer in the WHERE condition in fact affects the size and the data distribution of the underlying sets and makes SQL Server to reconsider the algorithms best fit to work with the sets of that size and layout.

I don't see any mention of a CHECK constraint in OP... – Remus Rusanu Mar 5 '10 at 19:13 I will post the plans, but I have to make a couple changes so my real tables don't give any identifying information (my example is doctored). – Cory Mar 5 '10 at 20:41 @Remus: When I read it I believed that when the FieldNumber constraint is Thanks! – Cory Mar 6 '10 at 17:56.

It gets replaced with a whole bunch of Parallelism blocks Try this: SELECT iv. ItemNumber, ,MAX(CASE WHEN f. FieldNumber = 1 THEN iv.

Value ELSE NULL END) Field1 ,MAX(CASE WHEN f. FieldNumber = 2 THEN iv. Value ELSE NULL END) Field2 ,MAX(CASE WHEN f.

FieldNumber = 3 THEN iv. Value ELSE NULL END) Field3 ... ,MAX(CASE WHEN f. FieldNumber = 51 THEN iv.

Value ELSE NULL END) Field51 FROM ItemField f LEFT JOIN ItemValue iv ON f. ID = iv. FieldID WHERE f.FieldNumber.

So this actually works, but you can't put OPTION in a view as I need it. – Cory Mar 5 '10 at 18:55.

At 66 you are hitting some internal cost estimate threshold that decides it is better to use one plan vs. the other. What that threshold is and why it happens is not really important. Note that your query differ with each FieldNumber value, as you are not only changing the WHERE: you also change the pseudo-'pivot' projected fields.

Now I don't know all the details of your table and your queries and insert/update/delete/pattern, but for the particular query you posted the proper clustered index structure for the ItemValue table is this: CREATE CLUSTERED INDEX cdxItemValue ON ItemValue (FieldID, ItemNumber); This structure eliminate the need to intermediate sort the results for this 'pivot' query.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.