Cross-database queries in Azure SQL Database

A limitation with Azure SQL database has been its inability to do cross-database SQL queries.  This has changed with the introduction of elastic database queries, now in preview.  However, it’s not as easy as on-prem SQL Server, where you can just use the three-part name syntax DatabaseName.SchemaName.TableName.  Instead, you have to define remote tables (tables outside your current database), which is similar to how PolyBase works for those of you familiar with PolyBase.

Here is sample code that, from within database AdventureWorksDB, selects data from table Customers in database Northwind:

--Within database AdventureWorksDB, will select data from table Customers in database Northwind

--Create database scoped master key and credentials

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

--Needs to be username and password to access SQL database

CREATE DATABASE SCOPED CREDENTIAL jscredential WITH IDENTITY = '<username>', SECRET = '<password>';

--Define external data source

CREATE EXTERNAL DATA SOURCE RemoteNorthwindDB WITH 
           (TYPE = RDBMS,
            LOCATION = '<servername>.database.windows.net',
            DATABASE_NAME = 'Northwind',  
            CREDENTIAL = jscredential 
            );

--Show created external data sources

select * from sys.external_data_sources; 

--Create external (remote) table.  The schema provided in your external table definition needs to match the schema of the tables in the remote database where the actual data is stored. 

CREATE EXTERNAL TABLE [NorthwindCustomers]( --what we want to call this table locally
	[CustomerID] [nchar](5) NOT NULL,
	[CompanyName] [nvarchar](40) NOT NULL,
	[ContactName] [nvarchar](30) NULL,
	[ContactTitle] [nvarchar](30) NULL,
	[Address] [nvarchar](60) NULL,
	[City] [nvarchar](15) NULL,
	[Region] [nvarchar](15) NULL,
	[PostalCode] [nvarchar](10) NULL,
	[Country] [nvarchar](15) NULL,
	[Phone] [nvarchar](24) NULL,
	[Fax] [nvarchar](24) NULL
)    
WITH
(
  DATA_SOURCE = RemoteNorthwindDB,
  SCHEMA_NAME = 'dbo', --schema name of remote table
  OBJECT_NAME = 'Customers' --table name of remote table
);

--Show created external tables

select * from sys.external_tables; 

--You can now select data from this external/remote table, including joining it to local tables

select * from NorthwindCustomers

--Cleanup

DROP EXTERNAL TABLE NorthwindCustomers;

DROP EXTERNAL DATA SOURCE RemoteNorthwindDB;

DROP DATABASE SCOPED CREDENTIAL jscredential;  

DROP MASTER KEY;  

So this allows you to do cross-database queries, but keep in mind the reasons this solution does not work as well as SQL Server:

  • Additional coding to create the remote tables
  • This is strictly a read of the data.  You can’t use remote tables to write to the tables
  • The databases you are trying to join could be located on different servers, introducing latency
  • You can use Elastic Database Tools instead of using Elastic Database Queries, but this requires even more coding as you have to use C# libraries
  • A work around could be to just put all the tables in one database, but that could require a lot of rewrites if moving an on-prem solution to the cloud, plus there is a 1TB database size limit in SQL Database

More info:

Elastic database query for cross-database queries (vertical partitioning)

About James Serra

James is a big data and data warehousing solution architect at Microsoft. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 25 years of IT experience.
This entry was posted in Azure SQL Database, SQLServerPedia Syndication. Bookmark the permalink.

10 Responses to Cross-database queries in Azure SQL Database

  1. Pingback: Azure SQL Database Cross-Database Queries – Curated SQL

  2. Barry Fitzgerald says:

    So is select * from sys.external_tables; actually pulling all the data from the external table into some kind of temp table in this database? Can you join to the external table without pulling everything?

  3. Jeff Spiller says:

    Can the Object_name value be a view ?

  4. Geri Reshef says:

    So far we can access azure from on premise using linked server,
    and azure from azure using the option from this article.
    Is there a way to access on premise from azure?

  5. Pingback: Azure SQL Database Pricing | James Serra's Blog

  6. Pingback: Multi-tenant databases in the cloud | James Serra's Blog

  7. Dia Gea says:

    Hello -OBJECT_NAME = ‘Customers’ –table name of remote table
    Are you referencing the remote table which exists already?
    I tried this and the cross table seemed to contain the same information as the remote table. What I would like to do is set up a type of freeze database and copy particular records to the freeze database periodically from the main database I have set up. Do you have any suggestions for this in Azure?

    • James Serra says:

      Hi Dia,

      Yes, the remote table already exists. You can setup a local table to have recent data, and a remote table to have older data. Then use a cross-database query to return all records.

  8. Pingback: Azure SQL Database pricing – Cloud Data Architect