We have this large database (>1TB) that we intend to "shrink". The database revolves around one main entity, let's call it "Visit". For discussion, let's say it is a database for a medical practice.
There are a total of 30 visit "types", such as procedure, annual, follow-up, immunisation etc, each of which is a subsidary table to "Visit", e.g. "visit_immuno".
The database has accummulated some 12 years of data since 2000. Someone has proposed that we keep about 3 years of data in the "live" version and have the rest live in an "old_data" database. The date is ONLY stored in the "Visit" table since it is normalised. The Visit table also contains a
ROWVERSION column and a
BIGINT pseudo-identity (clustered) column. For all intents and purposes, let's say the clustering key is populated by a SEQUENCE (SQL Server 2012 Enterprise) - we shall name it
visit.date is not always in the same order as the clustering key, for example when a doctor goes on extended visitations and returns with his "briefcase" of data, it gets merged into the main table. There are also some updates to the "visit" table that will cause the
ROWVERSION column to be out of sync with both the
date columns - to put it simply, neither
cid would make suitable partition keys for this reason.
The business rule for removing data from the "live" is that the
visit.date must be greater than 36 months and a child
visit_payment record must exist. Also, the "old_data" database does not contain any of the base tables except
So we end up with:
Live DB (daily use) - All tables
Old-Data DB - older data for the
The proposal calls for a Combined DB that is a shell containing Synonyms to ALL the base tables in the
Live DB (except
visit%) plus Views that UNION ALL across the
visit% tables in the two databases.
Assuming the same indexes are created in the
Old-Data DB, will the queries perform well on the UNION-ALL Views? What type of query patterns might trip up the execution plan for the UNION-ALL Views?