Cross database queries on views in SQL Azure

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…