Problems with partitioned views

  • pmadhavapeddi22

    SSCertifiable

    Points: 5306

    thanks for the interesting question

  • Andre Ranieri

    SSCrazy

    Points: 2759

    Thanks for the great question - I learned something here.

    Wouldn't this solution involve droppping and re creating the check constraint ever day to maintain workability?

    Andre Ranieri

  • TomThomson

    SSC Guru

    Points: 104772

    Andre Ranieri (1/16/2015)


    Thanks for the great question - I learned something here.

    Wouldn't this solution involve droppping and re creating the check constraint ever day to maintain workability?

    Andre Ranieri

    No. Once the constraint is trusted it will remain trusted until checking is switched off. If you never switch checking off, it remains trusted for ever (or until you drop it).

    Tom

  • kevin.l.williams

    SSCarpal Tunnel

    Points: 4916

    TomThomson (1/16/2015)


    Andre Ranieri (1/16/2015)


    Thanks for the great question - I learned something here.

    Wouldn't this solution involve droppping and re creating the check constraint ever day to maintain workability?

    Andre Ranieri

    No. Once the constraint is trusted it will remain trusted until checking is switched off. If you never switch checking off, it remains trusted for ever (or until you drop it).

    I think Andre may have been asking if you would need to drop and recreate the constraint if you implemented a sliding two year window and were moving historical data daily.

  • TomThomson

    SSC Guru

    Points: 104772

    kevin.l.williams (5/29/2015)


    TomThomson (1/16/2015)


    Andre Ranieri (1/16/2015)


    Thanks for the great question - I learned something here.

    Wouldn't this solution involve droppping and re creating the check constraint ever day to maintain workability?

    Andre Ranieri

    No. Once the constraint is trusted it will remain trusted until checking is switched off. If you never switch checking off, it remains trusted for ever (or until you drop it).

    I think Andre may have been asking if you would need to drop and recreate the constraint if you implemented a sliding two year window and were moving historical data daily.

    You may be right. To move stuff from current to historic would need the constraints to be redefined, which to me says that moving stuff every day is not something you do when you are using check constraints on date to show the engine where to insert/look. But I suspect that when the question says "the last two years" and "older" it's referring to calendar years (so currently the last two years are 2015 and 2014) or company financial years or tax years (when a tax year begins varies a lot from country to country, in Spain it's the same as the calendar year, in the UK it's the year beginning 6 April each year). So the constraints only need to be redefined once per year, not every day. And when they are changed doesnt have to be when the curent file hits exactly two years of data - depending on the access pattern for the workload it might be anywhere between when it hits 2 years of content and when it hits 3 years.

    Tom

Viewing 5 posts - 31 through 35 (of 35 total)

You must be logged in to reply to this topic. Login to reply