To create an external table in Azure SQL Database that accesses data across different databases, you need to first establish a “database scoped credential” on the target database, then create an “external data source” referencing that credential, and finally, define your external table using the created data source, essentially allowing you to query data from the remote database as if it were a local table within your current database.

How to create an external table in Azure SQL Database

Step 1, create two Azure SQL Databases

Customers and Orders

Step 2, create two tables and load some data

Execute this code in the Customers database to create a table and insert a row:

create table [dbo].[CustomerInformation](

    [CustomerID] [int] not null identity,
    [CustomerName] [varchar](50) null,
    [Company] [varchar](50) null
    constraint [CustID] primary key clustered ([CustomerID] asc)
)
insert into CustomerInformation
select 'Jack', 'ABC'
from sys.objects O cross join sys.columns C

Execute the next code in the Orders database to create a single table with around 120k rows:

create table [dbo].[OrderInformation](
    [OrderID] [int] not null identity,
    [CustomerID] [int] not null
      constraint OrdId primary key clustered (OrderId asc)
    )
insert into OrderInformation
select o.[object_id] from sys.objects O
cross join sys.columns C

Step 3, create a database scoped credential

In the Orders database, create a database scoped credential:

create master key encryption by password = '<master_key_password>';

create database scoped credential ElasticDBQueryCred
with identity = '<username>',
secret = '<password>';
go

Step 4, create an external data source using the credential:

In the Orders database, create an external data source:

create external data source MyElasticDBQueryDataSrc with
(type = rdbms,
location = '<server_name>.database.windows.net',
database_name = 'Customers',
credential = ElasticDBQueryCred,
) ;
go

Step 5, create an external table using the external data source

In the Orders database, create an external table:

create external table [dbo].[CustomerInformation]
( [CustomerID] [int] not null,
    [CustomerName] [varchar](50) not null,
    [Company] [varchar](50) not null)
with
( data_source = MyElasticDBQueryDataSrc)

How to query an external table in Azure SQL Database

Now that you’ve set up the external table, you’ll be able to query the table CustomerInformation from the Orders database.  It is worth noting that the external table we’ve just created does exist in the local database’s sys.objects DMV.  However, when you query this table, behind the scenes the optimizer is running a remote query against the “real” table in the Customers database.

We can see this when running the following simple query and reviewing the associated execution plan:

select top 10 * from CustomerInformation

Mohammad Gufran Jahangir