Index data that exists in multiple tables

I want to query some data in multiple tables as if it were one table. There are about 10 tables, all with different columns but with 5 matching columns. Ideally I would re-design the tables so that the shared columns go into one table and I could then create relations between the other tables. Unfortunately this is not an option as I can’t change the existing tables.

What would be the best approach for accessing and indexing the data? I was thinking of creating a view or stored procedure with UNION ALLs e.g.

SELECT COL1, COL2, COL3 FROM TABLE1
UNION ALL
SELECT COL1, COL2, COL3 FROM T开发者_开发问答ABLE2
UNION ALL
SELECT COL1, COL2, COL3 FROM TABLE3
UNION ALL
SELECT COL1, COL2, COL3 FROM TABLE4

But then how would I index this? Would a view or stored procedure be best? Or perhaps a completely different approach?

SQL Server has indexed views but it doesn't support UNIONs in the view.

If you have to query data over all tables as if it were one, and you can't change the current schema, then I'd suggest using triggers to maintain another single table that can be indexed etc.

View with union all would do the job. You should index individual tables that participate in the view. Actually it can benefit performance (optimizer may figure out that some tables does not need to be scanned in particular queries).

Stored proc means complications because it is not so easy to use it in from clause (but still possible with OPENQUERY like 'SELECT * FROM OPENQUERY(YourServer, 'SET FMT_ONLY OFF EXEC stored_proc')). It performs better when generating your results involves some procedural logic (e.g. complicated report).