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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: