You can’t query across databases in Azure SQL Database… or can you?

Let’s check. I’ve created two new databases on an existing server:

I’ve created two tables on each respective database:

CREATE TABLE dbo.DB1Table (
     ID INT IDENTITY(1, 1)
            NOT NULL
            PRIMARY KEY,
     Val VARCHAR(50)
    );
CREATE TABLE dbo.DB2Table (
     ID INT IDENTITY(1, 1)
            NOT NULL
            PRIMARY KEY,
     Val VARCHAR(50)
    );

Now, let’s query the DB2 table from the DB1 database:

SELECT  *
FROM    DB2.dbo.DB2Table AS dt;

And here’s the lovely error message:

Msg 40515, Level 15, State 1, Line 35

Reference to database and/or server name in ‘DB2.dbo.DB2Table’ is not supported in this version of SQL Server.

So, like I said, you can’t do three part name cross-database queries in Azure SQL Database… oh wait, that’s not quite what I said is it. Let’s do this. Let’s create a new security credential within DB1 for a login that can get us into DB2:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'securitymatters';
CREATE DATABASE SCOPED CREDENTIAL DB2Security 
WITH IDENTITY = 'Grant',
SECRET = 'securitymatters';

Then, we’ll use that to define an external data source:

CREATE EXTERNAL DATA SOURCE DB2Access
WITH (
TYPE=RDBMS,
LOCATION='myservernotyours.database.secure.windows.net',
DATABASE_NAME='DB2',
CREDENTIAL= DB2Security);

With this, we can put Elastic Query (corrected from Polybase see note below) to work and create an external table:

CREATE EXTERNAL TABLE dbo.DB2Table (
ID int,
Val varchar(50))
WITH
(
DATA_SOURCE = DB2Access);

And that’s it. If I query dbo.DB2Table from DB1, I get to see data in DB2. In short, you can do a cross database query within Azure SQL Database. Yeah, it’s going to require some setup and possibly some code modifications since you can’t use the old three part naming for performing the query, but, you can do it. Further, note that these are Standard, not Premium databases. Further further, they’re not a part of an elastic pool. It’s just using the external data source and external table to connect the two databases. However, if the one thing keeping you from moving into Azure SQL Database is the ability to query across databases, that’s gone.

The post Cross Database Query in Azure SQL Database appeared first on Home Of The Scary DBA.