Books On Lies - Partitioned Views

From BOL...

CHECK constraints are not needed for the partitioned view to return the correct results. However, if the CHECK constraints have not been defined, the query optimizer must search all the tables instead of only those that cover the search condition on the partitioning column. Without the CHECK constraints, the view operates like any other view with UNION ALL. The query optimizer cannot make any assumptions about the values stored in different tables and it cannot skip searching the tables that participate in the view definition.

Then why am I getting index scans on my partitioning column on tables that fail the search value based on their check constraint?

Not looking for an answer because I know the query optimizer is a fickle b*tch and I did not post any code, but I needed to rant.Now I have no experience here but I will attempt to dredge something from memory...

I believe, based on some discussions on SQL Team I skimmed, that the view will look through ALL tables on its first run but subsequent to that it should only hit the relevant tables.

Have a search of the SQL Team threads if you want to nail this - I am 78.3% sure I have got that about right. :)|||Not looking for an answer because I know the query optimizer is a fickle b*tch...

Have you had these feelings for a long time?

:D

...but I needed to rant.

I know the feeling well.

Take care,

hmscott|||- I am 78.3% sure I have got that about right. :)

What a coincidence, 78.3% of all statistics are made up on the spot.

Regards,

hmscott|||What a coincidence, 78.3% of all statistics are made up on the spot.I thought it was 79.2? :(|||Now I have no experience here but I will attempt to dredge something from memory...

I believe, based on some discussions on SQL Team I skimmed, that the view will look through ALL tables on its first run but subsequent to that it should only hit the relevant tables.

Have a search of the SQL Team threads if you want to nail this - I am 78.3% sure I have got that about right. :)

nope. same execution plan everytime. costly scans on the partitioning column with my check constraint on tables that fail the search condition. I just re-read the rules for partititioned views. everything looks ok and I am getting pissed.|||I thought it was 79.2? :(

As my old artillery instructor used to say, "what's one percent among friends?"
:shocked:

Regards,

hmscott|||As my old artillery instructor used to say, "what's one percent among friends?"

nothing, as long as your shells are 1% more explosive than your friend's shells.|||it appears that your partitioning column has to be the first column in the primary key. not that this written down anywhere obvious.|||that's because it's self-evident.

;)|||well the other part of the primary key is more selective being a surrogate incrementor, so nsince I had to use a composite key in this case I wanted it first in the index. i have had girlfriends I would call surrogate incrementors. Whoops, a TallCowboy moment.