There are some nice articles about setting up this feature in SQL Azure.
I prefer this one: https://ppolyzos.com/2016/07/30/cross-database-queries-in-azure-sql-databases/
but you could also go for this ‘official’ Microsoft doc at https://docs.microsoft.com/en-US/azure/sql-database/sql-database-elastic-query-getting-started-vertical
But in both of the articles I couldn’t find anything about the usage of views, so I tried it out myself.
I will go on with the article on ppolyzos.com, so I assume you will have a Db1Table in Db1 and a Db2Table in Db2, where the Db2Table is available as an EXTERNAL TABLE from Db1.
But what if you have 2 tables in Db2 you want to query in Db1?
So lets’ create another table in Db2
--Execute in Db2 CREATE TABLE dbo.Db2Table2 ( ID int IDENTITY(1, 1) NOT NULL PRIMARY KEY, CustomerId INT, Email NVARCHAR(255), Gender NVARCHAR(1) ); INSERT INTO dbo.Db2Table2(CustomerId, Email, Gender) VALUES ( 1, 'aaaaaaa@test.com' , 'M' ), ( 2, 'bbbbbbb@test.com' , 'F'), ( 3, 'ccccccc@test.com' , 'M'), ( 4, 'ddddddd@test.com' , 'M'), ( 5, 'eeeeeee@test.com' , 'F');
You could simply add a new EXTERNAL TABLE in Db1and execute a query.
--Execute in Db1 CREATE EXTERNAL TABLE [dbo].[Db2Table2] ( [ID] int NOT NULL, [CustomerId] INT, [Email] NVARCHAR(255) ) WITH ( DATA_SOURCE = my_datasource ) SELECT * FROM Db2Table2
One thing to note is the external table, doesn’t have to include all the columns from the source table, in this example I’m not using the Gender field in the EXTERNAL TABLE.
But what if we want to get the Country and the Email in Db1?
You can do something like this to get the correct data
--Execute in Db1 SELECT t1.CustomerId , t1.Country , t2.Email FROM Db2Table t1 INNER JOIN Db2Table2 t2 ON t2.CustomerId = t1.CustomerId
But what about performance? What if we create a VIEW to get the data and make an EXTERNAL TABLE from the view.
Let’s create the view in Db2.
--Execute in Db2 CREATE VIEW Db2View AS SELECT t1.CustomerId , t1.Country , t2.Email FROM Db2Table t1 INNER JOIN Db2Table2 t2 ON t2.CustomerId = t1.CustomerId
And create an EXTERNAL TABLE in Db1 for the VIEW.
--Execute in Db1 CREATE EXTERNAL TABLE Db2View ( [CustomerId] INT, [Country] NVARCHAR(50), [Email] NVARCHAR(255) ) WITH ( DATA_SOURCE = my_datasource )
apparently this works, so now we can query the db2 View in db1.
--Execute in Db1 SELECT * FROM Db2View
Now let’s have a look at performance between querying the external view and the external tables in Db1.
--Execute in Db1 SELECT t1.CustomerId , t1.Country , t2.Email FROM Db2Table t1 INNER JOIN Db2Table2 t2 ON t2.CustomerId = t1.CustomerId SELECT * FROM Db2View
Here are the execution plans for both queries.
So I guess it’s good to know you can create EXTERNAL TABLES for both VIEWS and TABLES.
To drop the EXTERNAL TABLE:
DROP EXTERNAL TABLE Db2Table DROP EXTERNAL TABLE Db2Table2 DROP EXTERNAL TABLE Db2View
Hope you enjoyed it…