Why is External Table syntax different in SQL Server 2019 vs Azure SQL Database? `

Question

I have to perform cross DB querying in SQL Server 2019 as well as Azure SQL. I am able to create External data source and external tables in both databases, however the syntax seem to be different. Can someone please guide me if there is one standard script that can work in both cases?

Azure SQL Database

• External Data Source

  CREATE EXTERNAL DATA SOURCE SQLServerInstance2
  WITH (
      TYPE          = RDBMS,
      LOCATION      = 'sourcesqlserver.database.windows.net',
      DATABASE_NAME = 'sourcedb',                    
      CREDENTIAL    = SQLServerCredentials
  );
  

• External Table

  CREATE EXTERNAL TABLE dbo.SourceTable (
      Col1 int NULL,
      Col2 int NULL
  )
  WITH (
      DATA_SOURCE = SQLServerInstance2
  );
  

SQL Server 2019

• External Data Source

  CREATE EXTERNAL DATA SOURCE SQLServerInstance2
  WITH (
      LOCATION   = 'sqlserver://WINSQL2019:58137' ,
      CREDENTIAL = SQLServerCredentials
  );
  

• External Table

  CREATE EXTERNAL TABLE [dbo].[SourceTable] (
      Col1 int NULL,
      Col2 int NULL
  )
  WITH (
      DATA_SOURCE = SQLServerInstance2,
      LOCATION    = N'[SourceDB].[dbo].[SourceTable]'
  );
  

Answer 1

Azure SQL SQL database is a cloud database. Azure SQL Database is a fully managed Platform as a Service (PaaS) Database Engine

Even it almost has the same feature with SQL Server, there are still many difference.

They are running in different platform, that's why the syntax could be difference.

Please reference this document to get Transact-SQL differences between Azure SQL Database and SQL Server.

Across database query in Azure SQL database is more complicated than SQL Server. If we want to achieve the same feature with SQL Server in Azure SQL, there are be a syntax difference.

Hope this helps.