SQL Azure cross database queries – evaluation

Existing documentation to create external database seems fine. Links include:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-query-overview

https://ppolyzos.com/2016/07/30/cross-database-queries-in-azure-sql-databases/

Troubleshooting

Error: “The option ‘IDENTITY’ is not supported with external tables for sharded data”
Remove the IDENTITY from the CREATE EXTERNAL TABLE id column

Using a different name in target

If the target is already using the external name you may want to use a different name. See this article. Add following to the with statement.

SCHEMA_NAME = ‘Sales’, OBJECT_NAME = ‘Quote’

https://azure.microsoft.com/en-us/blog/querying-remote-databases-in-azure-sql-db/

One option is to create a view in the source with the desired name. Then in the target reference that. Works.

Performance

Slightly slower than using the source database, but pretty good – certainly usable in most cases.