sql - JOIN VIEWs containing UNION ALL: performance disaster? -

sql - JOIN VIEWs containing UNION ALL: performance disaster? -

consider next standard table/subtable relationship in ms sql server db:

parent -id (pk) -other_field kid -id (pk) -parent_id (fk) -other_field

now consider there exist other versions of these tables in db (with same structure): parent_2/child_2, parent_3/child_3, parent_4/child_4.

i want create standard mechanism select info of tables combined in simple query. first thing came mind is:

parent_view = parent union parent_2 union parent_3 union parent_4 child_view = kid union child_2 union child_3 union child_4

but i'm concerned queries containing joins against these views horrendous due each row in parent_view having scan each row in child_view each join. there multiple different kid tables (resulting in multiple joins) , i'll dealing big amounts of info in due course. don't think scale.

ideally want preserve construction of tables (opposed flattening them out).

queries made against combined tables filtered using conditions.

i'm aware of alternative approach of writing queries each parent/child table separately , using union on each result set bring together much more efficient.

i considered conditional join, have not yet explored further.

any suggestions how proceed (ideally dbms science up)?

sql sql-server database join union-all