Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6

    Unanswered: 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.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Thrasymachus
    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?



    ...but I needed to rant.
    I know the feeling well.

    Take care,

    hmscott
    Have you hugged your backup today?

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by pootle flump
    - 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
    Have you hugged your backup today?

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by hmscott
    What a coincidence, 78.3% of all statistics are made up on the spot.
    I thought it was 79.2?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by pootle flump
    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.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by pootle flump
    I thought it was 79.2?
    As my old artillery instructor used to say, "what's one percent among friends?"


    Regards,

    hmscott
    Have you hugged your backup today?

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by 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.

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    it appears that your partitioning column has to be the first column in the primary key. not that this written down anywhere obvious.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    that's because it's self-evident.


  11. #11
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    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.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •