CODEWITHSUNDEEP
sql-serverentity-frameworkazure-sql-database
Support Ukraine
Support Ukraine

Reputation: 1026

Cross database queries.How to proper use cross database features?

I am investigating the possibility to split one DB into multiple. We decided to move some tables into another database, but we have queries with join on these tables. I found a few solutions about how to achieve that:

  1. Azure SQL Database elastic query
  2. EXTERNAL DATA SOURCE

But I don`t know what the difference between them and what to choose.

Thanks for any help!

Upvotes: 2

Views: 2298

Answers (2)

Alberto Morillo
Alberto Morillo

Reputation: 15618

Azure SQL Database Elastic Queries and External data sources are two names for the same concept.

My suggestion is to avoid cross database queries and avoid splitting one database into multiples because query performance involving external data sources won't be the same no matter what strategy you choose to query those external tables.

If you still want to stick with the plan of splitting the database into multiple databases, then know that cross database queries show good performance when the remote tables are not big. When remote tables are big, this article shows you how to perform joins remotely using table variables and improve performance. This other article shows you also how to push parameterized operations to remote databases and improve performance.

Upvotes: 2

Meyyappan
Meyyappan

Reputation: 344

if you are thinking to split your DB into multiple SQL server DB with the different host then you can prefer Linked server which has flexible to join across SQL servers

Upvotes: 0

Related Questions