Lesson Learned #62: Using Cross Database Queries in Azure SQL Managed Instance


Hello,

This week I worked a service request when our customer reported performance issues obtaining data from External Tables using Azure SQL Database. They followed our article about how to configure it.

I suggested to use Cross Database Queries in the similar way that we have in SQL Server using Azure SQL Managed Instance. I provided an example to do it, even using a distributed transaction.

-- ==============================
-- CREATE DATABASE
-- ==============================
CREATE DATABASE DatabaseA
GO
CREATE DATABASE DatabaseB
GO

-- ===============================
-- Connected to the database B, create table and insert some data
-- ===============================
USE DatabaseB
go
create table TableExample (ID INT, Name VARCHAR(20))
GO
INSERT INTO TableExample values(1,'Data in DBB')
GO

-- ===============================
-- Connected to the database A, create table and insert some data
-- ===============================
USE DatabaseA
GO
create table TableExample (ID INT, Name VARCHAR(20))
go
INSERT INTO TableExample values(1,'Data in DBA')
GO
SELECT * FROM DatabaseB.dbo.TableExample

-- ===============================
-- Also, it is possible to use distributed query running in the same transaction.
-- ===============================
BEGIN TRANSACTION 
INSERT INTO DatabaseB.dbo.TableExample values(2,'Data in DBB')
INSERT INTO DatabaseA.dbo.TableExample values(2,'Data in DBA')
COMMIT TRANSACTION

SELECT * FROM DatabaseB.dbo.TableExample
SELECT * FROM DatabaseA.dbo.TableExample

Enjoy!!

Comments (0)

Skip to main content