%3CLINGO-SUB%20id%3D%22lingo-sub-369167%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%2362%3A%20Using%20Cross%20Database%20Queries%20in%20Azure%20SQL%20Managed%20Instance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-369167%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3EFirst%20published%20on%20MSDN%20on%20Jan%2019%2C%202019%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20Hello%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20This%20week%20I%20worked%20a%20service%20request%20when%20our%20customer%20reported%20%3CA%20href%3D%22https%3A%2F%2Fblogs.msdn.microsoft.com%2Fazuresqldbsupport%2F2019%2F01%2F13%2Flesson-learned-56-external-tables-and-performance-issues%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20performance%20issues%20%3C%2FA%3E%20obtaining%20data%20from%20%3CA%20href%3D%22https%3A%2F%2Fblogs.msdn.microsoft.com%2Fazuresqldbsupport%2F2018%2F11%2F14%2Fcross-database-query-in-azure-sql-database%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20External%20Tables%20%3C%2FA%3E%20using%20Azure%20SQL%20Database.%20They%20followed%20our%20article%20about%20how%20to%20configure%20it.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20I%20suggested%20to%20use%20Cross%20Database%20Queries%20in%20the%20similar%20way%20that%20we%20have%20in%20SQL%20Server%20using%20Azure%20SQL%20Managed%20Instance.%20I%20provided%20an%20example%20to%20do%20it%2C%20even%20using%20a%20distributed%20transaction.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%5Bcode%20language%3D%22SQL%22%5D%20%3CBR%20%2F%3E%20--%20%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20--%20CREATE%20DATABASE%20%3CBR%20%2F%3E%20--%20%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20CREATE%20DATABASE%20DatabaseA%20%3CBR%20%2F%3E%20GO%20%3CBR%20%2F%3E%20CREATE%20DATABASE%20DatabaseB%20%3CBR%20%2F%3E%20GO%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20--%20%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20--%20Connected%20to%20the%20database%20B%2C%20create%20table%20and%20insert%20some%20data%20%3CBR%20%2F%3E%20--%20%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20USE%20DatabaseB%20%3CBR%20%2F%3E%20go%20%3CBR%20%2F%3E%20create%20table%20TableExample%20(ID%20INT%2C%20Name%20VARCHAR(20))%20%3CBR%20%2F%3E%20GO%20%3CBR%20%2F%3E%20INSERT%20INTO%20TableExample%20values(1%2C'Data%20in%20DBB')%20%3CBR%20%2F%3E%20GO%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20--%20%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20--%20Connected%20to%20the%20database%20A%2C%20create%20table%20and%20insert%20some%20data%20%3CBR%20%2F%3E%20--%20%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20USE%20DatabaseA%20%3CBR%20%2F%3E%20GO%20%3CBR%20%2F%3E%20create%20table%20TableExample%20(ID%20INT%2C%20Name%20VARCHAR(20))%20%3CBR%20%2F%3E%20go%20%3CBR%20%2F%3E%20INSERT%20INTO%20TableExample%20values(1%2C'Data%20in%20DBA')%20%3CBR%20%2F%3E%20GO%20%3CBR%20%2F%3E%20SELECT%20*%20FROM%20DatabaseB.dbo.TableExample%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20--%20%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20--%20Also%2C%20it%20is%20possible%20to%20use%20distributed%20query%20running%20in%20the%20same%20transaction.%20%3CBR%20%2F%3E%20--%20%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20BEGIN%20TRANSACTION%20%3CBR%20%2F%3E%20INSERT%20INTO%20DatabaseB.dbo.TableExample%20values(2%2C'Data%20in%20DBB')%20%3CBR%20%2F%3E%20INSERT%20INTO%20DatabaseA.dbo.TableExample%20values(2%2C'Data%20in%20DBA')%20%3CBR%20%2F%3E%20COMMIT%20TRANSACTION%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20SELECT%20*%20FROM%20DatabaseB.dbo.TableExample%20%3CBR%20%2F%3E%20SELECT%20*%20FROM%20DatabaseA.dbo.TableExample%20%3CBR%20%2F%3E%20%5B%2Fcode%5D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Enjoy!!%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-369167%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Jan%2019%2C%202019%20Hello%2CThis%20week%20I%20worked%20a%20service%20request%20when%20our%20customer%20reported%20performance%20issues%20obtaining%20data%20from%20External%20Tables%20using%20Azure%20SQL%20Database.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-369167%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Ecross%20database%20query%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Edistributed%20transaction%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Emanaged%20instance%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESQL%20Azure%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
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!!