Transact-SQL syntax not supported in Azure SQL Database

In-house SQL Vs Azure SQL database

Most Transact-SQL features that applications use are fully supported in both Microsoft SQL Server and Azure SQL Database. For example, the core SQL components such as data types, operators, string, arithmetic, logical, and cursor functions, work identically in SQL Server and SQL Database.

Regular T-SQL

  • Collation of system objects
  • Connection related: Endpoint statements. SQL Database does not support Windows authentication, but does support the similar Azure Active Directory authentication. Some authentication types require the latest version of SSMS. For more information, see Connecting to SQL Database or SQL Data Warehouse By Using Azure Active Directory Authentication.
  • Cross database queries using three or four part names. (Read-only cross-database queries are supported by using elastic database query.)
  • Cross database ownership chaining, TRUSTWORTHY setting
  • EXECUTE AS LOGIN Use ‘EXECUTE AS USER’ instead.
  • Encryption is supported except for extensible key management
  • Eventing: Events, event notifications, query notifications
  • File placement: Syntax related to database file placement, size, and database files that are automatically managed by Microsoft Azure.
  • Log reader: Syntax that relies upon the log reader, which is not available on SQL Database: Push Replication, Change Data Capture. SQL Database can be a subscriber of a push replication article.
  • Transact-SQL debugging
  • USE statement: To change the database context to a different database, you must make a new connection to the new database.

Advance T-SQL

  • High availability: Syntax related to high availability, which is managed through your Microsoft Azure account. This includes syntax for backup, restore, Always On, database mirroring, log shipping, recovery modes.
  • Triggers: Server-scoped or logon triggers
  • Trace flags: Some trace flag items have been moved to compatibility modes.
  • NET Framework: CLR integration with SQL Server
  • SQL Server Agent: Syntax that relies upon the SQL Server Agent or the MSDB database: alerts, operators, central management servers. Use scripting, such as Azure PowerShell instead.
  • SQL Server audit: Use SQL Database auditing instead.
  • SQL Server trace
  • SET REMOTE_PROC_TRANSACTIONS
  • SHUTDOWN
  • sp_addmessage
  • Functions: fn_get_sqlfn_virtualfilestatsfn_virtualservernodes
  • Hardware: Syntax related to hardware-related server settings: such as memory, worker threads, CPU affinity, trace flags. Use service tiers and compute sizes instead.
  • KILL STATS JOB
  • OPENQUERYOPENROWSETOPENDATASOURCE, and four-part names
  • .Semantic search
  • Server credentials: Use database scoped credentials instead.
  • Server-level items: Server roles, sys.login_tokenGRANTREVOKE, and DENY of server level permissions are not available though some are replaced by database-level permissions. Some useful server-level DMVs have equivalent database-level DMVs.
  • sp_configure options and RECONFIGURE. Some options are available using ALTER DATABASE SCOPED CONFIGURATION.
  • sp_helpuser
  • sp_migrate_user_to_contained

Transact-SQL syntax statements with partial differences

  • CREATE and ALTER DATABASE statements have over three dozen options. 
  • The CREATE and ALTER TABLE statements have FileTable options that cannot be used on SQL Database because FILESTREAM is not supported.
  • CREATE and ALTER login statements are supported but SQL Database does not offer all the options. 

Find more on https://docs.microsoft.com/en-us/azure/sql-database/sql-database-transact-sql-information

Please follow and like us:
error

Leave a Comment

Your email address will not be published. Required fields are marked *

error

Enjoy this blog? Please spread the word :)