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

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

First published on MSDN on Jan 19, 2019
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.

[code language="SQL"]
-- ==============================
-- 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
[/code]

Enjoy!!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.