Problems with partitioned views

Question

Post reply

Problems with partitioned views

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

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

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 1

pmadhavapeddi22

SSCertifiable

Points: 5306

January 8, 2015 at 6:15 am

#1768756

thanks for the interesting question

Andre Ranieri SSCrazy Points: 2819 More actions · Answer 2

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: 104773 More actions · Answer 3

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 SSCarpal Tunnel Points: 4962 More actions · Answer 4

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: 104773 More actions · Answer 5

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