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 <some_type> REFERENCES Companies (CompanyID), CustomerID <some_type> 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.
You can create a partitioned table to horizontally partition you data.
Partitioned tables is an Enterprise edition feature, if you are on a lesser edition you could alternatively create a partitioned view, see this link for an example https://auntkathisql.com/2015/08/26/what-is-a-partitioned-view/
If you are using SQL Server 2016, you could also put all the data in one table and use Row-Level Security to expose the correct data to the relevant customers.