Exploring Cross-Database Queries with External Tables in Azure SQL Database – Elastic Query

Introduction

In Azure SQL Database, the ability to perform cross-database queries is essential for consolidating data and extracting insights from multiple databases. One powerful feature that enables this capability is the use of external tables. In this blog post, we will delve into the concept of external tables, demonstrate an example using the “Orders” and “Customers” tables residing in different databases, and provide step-by-step instructions to set up and execute a cross-database query.

Learn more about it from Microsoft document

What are External Tables?

External tables in Azure SQL Database allow you to define a table structure that references data residing outside the current database. It provides a seamless way to query and join data from disparate databases, allowing you to access and combine information across different database boundaries.

Example

Consider a scenario where you have two databases, “OrderDB” and “CustomerDB”. The “Orders” table resides in “OrderDB”, while the “Customers” table is located in “CustomerDB”. The CustomerDB is a master database for customer records. We want to retrieve information from both tables using a single query.

Add a database user; skip if you already have one

Run the following scripts to add the user to the master database. We will need this user for the cross-database query. When a new SQL Server is created in Azure, the master database is automatically set as the default database.

CREATE LOGIN ExternalTableTestUser WITH password='Password12345';

Create Database

In our example, we will work with two databases CustomerDb and OrderDb. You can create new databases using Azure Portal.

Database User and Permission

Add the database user to the CustomerDb. This user is required to access tables in CustomerDb.

CREATE USER CrossDatabaseUser FROM LOGIN CrossDatabaseUser;

EXEC sp_addrolemember 'db_datareader', 'CrossDatabaseUser';

Or you can give permission to a specific table.

GRANT SELECT ON [dbo].[TableName] TO CrossDatabaseUser;

Create Tables

Create the “Customers” Table in CustomerDb.

CREATE TABLE Customers
(
    CustomerId INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

Insert Sample Data into the “Customers” Table

INSERT INTO Customers (CustomerId, FirstName, LastName)
VALUES
    (1, 'John', 'Doe'),
    (2, 'Jane', 'Smith'),
    (3, 'Michael', 'Johnson');

Create the “Orders” Table in OrderDb.

CREATE TABLE Orders
(
    OrderId INT PRIMARY KEY,
    CustomerId INT,
    ProductName VARCHAR(50),
    Quantity INT
);

Insert Sample Data into the “Orders” Table

INSERT INTO Orders (OrderId, CustomerId, ProductName, Quantity)
VALUES
    (1, 1, 'Product A', 5),
    (2, 1, 'Product B', 3),
    (3, 2, 'Product C', 2),
    (4, 3, 'Product D', 1);

Setting Up External Tables

To enable cross-database queries, we need to follow these steps:

Create a Master Key:

• Connect to each database separately. Must perform this step in OrderDb and CustomerDb
• Execute the following SQL command to create a master key:

CREATE MASTER KEY;

Create an External Data Source in OrderDb:

Execute the following SQL command to create an external data source that references CustomerDb:

CREATE EXTERNAL DATA SOURCE RemoteCustomerDb
WITH (
    TYPE = RDBMS,
    LOCATION = '<server-name>.database.windows.net', 
    DATABASE_NAME = 'CustomerDb',
    CREDENTIAL = ElasticQueryCredential
);

Replace <server-name> with the name of the server hosting CustomerDb.

Create an External Table in OrderDb

Execute the following SQL command to create an external table in OrderDb referencing the “Customers” table in CustomerDb.

CREATE EXTERNAL TABLE Customers
(
    CustomerId INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Address VARCHAR(500)
)
WITH
(
    DATA_SOURCE = RemoteCustomerDb,
    SCHEMA_NAME = 'dbo',
    OBJECT_NAME = 'Customers'
);

Executing the Cross-Database Query

Now that we have set up the external tables, we can perform a cross-database query to retrieve information from both tables:

SELECT C.CustomerId, C.FirstName, C.LastName, O.OrderId, O.ProductName, O.Quantity
FROM Customers AS C
JOIN Orders AS O ON C.CustomerId = O.CustomerId;

In this example query, we are joining the “Customers” table from CustomerDb with the “Orders” table from OrderDb, using the common column “CustomerId”. By executing this query, we can seamlessly access and combine data from both databases.

Conclusion

External tables provide a powerful mechanism for performing cross-database queries in Azure SQL Database. By referencing tables from other databases, you can consolidate and analyze data effectively, enabling better insights and decision-making. In this blog post, we explored the concept of external tables and demonstrated a practical example with the “Orders” and “Customers” tables residing in different databases. By following the provided steps, you can set up and execute your own cross-database queries using external tables in Azure SQL Database.