DataDog (10/22/2012)
2 days 2 years 2 millenia won't make any difference as they are all linked in a chain!
Eventually everything in a shipment/order would have to age past the 2 year mark, at which point I'd archive. Even though yes, they chain, at some point everything in some order and some shipment would have to get past 2 years.
If I needed another alternative, what I'd most likely end up doing is talking with business and finding out when they're unlikely to ever need shipment information again. Once an order archived, I'd copy the shipment header to the archive if it wasn't there and then move that order's pieces of the shipment over, removing them from the primary DB. Eventually a shipment would be empty in the primary and I'd remove the header, finally fully archiving it.
If neither of those work, then I'm missing something important about your data-design.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA