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 cid
.
The 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 cid
and date
columns - to put it simply, neither ROWVERSION
nor 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 visit%
.
So we end up with:
Live DB (daily use) - All tables
Old-Data DB - older data for the visit%
tables
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?