Thursday, December 12, 2019

Transact-SQL syntax not supported in Azure SQL Database

 

Comparing SQL Server and Azure SQL Database: T-SQL Compatibility and Key Differences

In-house SQL Vs Azure SQL Database

When working with Microsoft SQL Server and Azure SQL Database, developers will find that most Transact-SQL (T-SQL) features behave similarly across both platforms. Core functionalities such as data types, operators, and standard SQL functions—like string, arithmetic, logical, and cursor operations—are fully supported in both environments. However, there are certain differences worth understanding, especially when migrating applications or developing hybrid solutions.

Core T-SQL Features and Differences

Fully Supported T-SQL Features

For general application development, the majority of standard T-SQL features are identical in both environments. These include:

  • Common data types and operators

  • Arithmetic, logical, and string functions

  • Cursors and control-of-flow constructs

However, differences emerge in system-level operations and administrative features.

Notable Differences in Standard T-SQL

While basic SQL usage remains consistent, some areas of T-SQL functionality differ between SQL Server and Azure SQL Database:

Key Differences

  • Collation Rules: System object collation may vary.

  • Authentication: Azure SQL does not support Windows authentication. Instead, it supports Azure Active Directory (AAD) authentication.

  • Connection Endpoints: Some endpoint-related statements are unsupported.

  • Cross-Database Queries: Direct multi-database queries using three- or four-part names are not supported, but elastic queries can be used for read-only access across databases.

  • Ownership Chaining: Features like TRUSTWORTHY and cross-database ownership chaining are not available.

  • Security Context: EXECUTE AS LOGIN is not supported. Use EXECUTE AS USER instead.

  • Encryption: Supported with limitations—extensible key management is not available.

  • Events and Notifications: Event notifications and query notifications are not supported.

  • File Management: File placement, sizing, and management are handled by Azure and cannot be manually configured.

  • Log Reader Features: Features depending on log readers (e.g., Change Data Capture) are not available.

  • Debugging: T-SQL debugging is not supported.

  • USE Statement: Changing the database context requires a new connection.

Advanced T-SQL Feature Differences

Some advanced features that rely on hardware access or deep server configuration are either limited or unavailable in Azure SQL Database:

  • High Availability Configurations: Managed directly via Azure—traditional features like Always On or log shipping are unavailable.

  • Server-Level Triggers: Logon and server-scoped triggers are not supported.

  • Trace Flags: These are mostly deprecated or replaced by compatibility levels.

  • CLR Integration: .NET CLR features are not supported.

  • SQL Server Agent: Job scheduling must be handled using alternatives like Azure Automation or PowerShell.

  • SQL Server Audit: Use Azure’s built-in auditing capabilities instead.

  • SQL Trace: Unsupported—use Extended Events or auditing.

  • Remote Procedure Transactions: Features like SET REMOTE_PROC_TRANSACTIONS are not available.

  • Administrative Procedures: System stored procedures like sp_addmessage, sp_helpuser, or sp_migrate_user_to_contained have limited or no support.

  • DMVs and Server Roles: Some server-level views and permissions are replaced with database-scoped alternatives.

  • Configuration Settings: sp_configure options are replaced by ALTER DATABASE SCOPED CONFIGURATION.

  • Semantic Search and FileTable: Not supported in Azure SQL Database.

  • Linked Server Features: Statements like OPENQUERY, OPENROWSET, or four-part names are restricted.

  • Resource Management: You must configure compute and storage through service tiers instead of hardware tuning.

T-SQL with Partial Support or Restrictions

While certain syntax is still available, it may be limited in scope or functionality:

  • Database Creation and Alteration: Over 30 options are unavailable in Azure SQL for CREATE and ALTER DATABASE commands.

  • FileTable Options: Not supported due to the absence of FILESTREAM.

  • Login Management: Only a subset of options are supported for creating and altering logins in Azure.

Learn More

For a detailed and continuously updated list of supported features, check Microsoft’s documentation:
🔗 Transact-SQL in Azure SQL Database

at

Popular Posts