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?
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.