Alternative to UNION with indexing I'm on SQL Server 2012 and I have tables for different companies each one with its own customers:

CompanyA_Customers:

ID | Name
---------
11 | Foo
12 | Bar

CompanyB_Customers:

ID | Name
---------
11 | Baz
12 | Qux


And I need a single table to query them, so I did this:

CREATE VIEW Customers AS
    SELECT 'A' AS Company, ID, Name FROM CompanyA_Customers
UNION ALL
    SELECT 'B' AS Company, ID, Name FROM CompanyB_Customers


but I suffer poor performance since as far as I know on a UNION view I can't define any index.

Are there any alternatives to have better performance?
IMHO (and as Paparazzi has pointed out on comments) the easiest way to accomplish it and get better performance is using a single table by adding a CompanyID field.

CREATE TABLE Customers
(
    CompanyID   &lt;some_type&gt; REFERENCES Companies (CompanyID),
    CustomerID  &lt;some_type&gt; REFERENCES Customers (CustomerID),
    Name        text,
    PRIMARY KEY (CompanyID, CustomerID)
);


If you cannot modify your actual table schema, you could use two materialized views, one for each company, and add a clustered index on (company_id, id) or (id, company_id) depending on your queries. 

I've set up a fiddle here

select company_id, id, name
from (
      select company_id, id, name from dbo.vcustA
      union all
      select company_id, id, name from dbo.vcustB
     ) x
where id = 2;


Execution plan for two materialized views using UNION:



But in fact, this is worst than create a new table with all records of both tables. Instead of use two materialized views, I'd suggest to create a new table and, using triggers, add/update/delete all records from customersA and customersB. Or set up a daily job that synchronize this information.