T-SQL differences between SQL Server and Azure SQL Database
When migrating your database from SQL Server to Azure SQL Database, you may discover that your SQL Server databases require some re-engineering before they can be migrated. This article provides guidance to assist you in both performing this re-engineering and understanding the underlying reasons why the re-engineering is necessary. To detect incompatibilities and migrate databases to Azure SQL Database, use Data Migration Assistant (DMA).
Note
Microsoft Entra ID was previously known as Azure Active Directory (Azure AD).
Most T-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. There are, however, a few T-SQL differences in DDL (data definition language) and DML (data manipulation language) elements resulting in T-SQL statements and queries that are only partially supported (which we discuss later in this article).
In addition, there are some features and syntax that isn't supported at all because Azure SQL Database is designed to isolate features from dependencies on the system databases and the operating system. As such, most instance-level features are not supported in SQL Database. T-SQL statements and options aren't available if they configure instance-level options, operating system components, or specify file system configuration. When such capabilities are required, an appropriate alternative is often available in some other way from SQL Database or from another Azure feature or service.
For example, high availability is built into Azure SQL Database. T-SQL statements related to availability groups are not supported by SQL Database, and the dynamic management views related to Always On Availability Groups are also not supported.
For a list of the features that are supported and unsupported by SQL Database, see Azure SQL Database feature comparison. This page supplements that article, and focuses on T-SQL statements.
The core DDL statements are available, but DDL statement extensions related to unsupported features, such as file placement on disk, are not supported.
- In SQL Server,
CREATE DATABASE
andALTER DATABASE
statements have over three dozen options. The statements include file placement, FILESTREAM, and service broker options that only apply to SQL Server. This may not matter if you create databases in SQL Database before you migrate, but if you're migrating T-SQL code that creates databases you should compare CREATE DATABASE (Azure SQL Database) with the SQL Server syntax at CREATE DATABASE (SQL Server T-SQL) to make sure all the options you use are supported.CREATE DATABASE
for Azure SQL Database also has service objective and elastic pool options that apply only to SQL Database. - The
CREATE TABLE
andALTER TABLE
statements haveFILETABLE
andFILESTREAM
options that can't be used on SQL Database because these features aren't supported. CREATE LOGIN
andALTER LOGIN
statements are supported, but do not offer all options available in SQL Server. To make your database more portable, SQL Database encourages using contained database users instead of logins whenever possible. For more information, see CREATE LOGIN and ALTER LOGIN and Manage logins and users.
In addition to T-SQL statements related to the unsupported features described in Azure SQL Database feature comparison, the following statements and groups of statements aren't supported. As such, if your database to be migrated is using any of the following features, re-engineer your application to eliminate these T-SQL features and statements.
- Collation of system objects.
- Connection related: Endpoint statements. SQL Database doesn't support Windows authentication, but does support Microsoft Entra authentication. This includes authentication of Active Directory principals federated with Microsoft Entra ID (formerly Azure Active Directory). For more information, see Connecting to SQL Database or Azure Azure Synapse Analytics By Using Microsoft Entra authentication.
- Cross-database and cross-instance queries using three or four part names. Three part names referencing the
tempdb
database and the current database are supported. Elastic query supports read-only references to tables in other MSSQL databases. - Cross database ownership chaining and the
TRUSTWORTHY
database property. EXECUTE AS LOGIN
. UseEXECUTE AS USER
instead.- Extensible key management (EKM) for encryption keys. Transparent Data Encryption (TDE) customer-managed keys and Always Encrypted column master keys may be stored in Azure Key Vault.
- Eventing: event notifications, query notifications.
- File properties: Syntax related to database file name, placement, size, and other file properties automatically managed by SQL Database.
- High availability: Syntax related to high availability and database recovery, which are managed by SQL Database. This includes syntax for backup, restore, Always On, database mirroring, log shipping, recovery models.
- Syntax related to snapshot, transactional, and merge replication, which is not available in SQL Database. Replication subscriptions are supported.
- Functions:
fn_get_sql
,fn_virtualfilestats
,fn_virtualservernodes
. - Instance configuration: Syntax related to server memory, worker threads, CPU affinity, trace flags. Use service tiers and compute sizes instead.
KILL STATS JOB
.OPENQUERY
,OPENDATASOURCE
, and four-part names.- .NET Framework: CLR integration
- Semantic search
- Server credentials: Use database scoped credentials instead.
- Server-level permissions:
GRANT
,REVOKE
, andDENY
of server level permissions are not supported. Some server-level permissions are replaced by database-level permissions, or granted implicitly by built-in server roles. Some server-level DMVs and catalog views have similar database-level views. SET REMOTE_PROC_TRANSACTIONS
SHUTDOWN
sp_addmessage
sp_configure
andRECONFIGURE
. ALTER DATABASE SCOPED CONFIGURATION is supported.sp_helpuser
sp_migrate_user_to_contained
- SQL Server Agent: Syntax that relies upon the SQL Server Agent or the
msdb
database: alerts, operators, central management servers. Use scripting, such as PowerShell, instead. - SQL Server audit: Use SQL Database auditing instead.
- SQL Server trace.
- Trace flags.
- T-SQL debugging.
- Server-scoped or logon triggers.
USE
statement: To change database context to a different database, you must create a new connection to that database.
For more information about T-SQL grammar, usage, and examples, see T-SQL Reference (Database Engine).
The T-SQL reference includes articles related to all recent SQL Server versions. Below the article title there's an icon bar, listing MSSQL platforms, and indicating applicability. For example, availability groups were introduced in SQL Server 2012. The CREATE AVAILABILITY GROUP article indicates that the statement applies to SQL Server (starting with 2012). The statement doesn't apply to SQL Server 2008, SQL Server 2008 R2, Azure SQL Database, Azure Azure Synapse Analytics, or Parallel Data Warehouse.
In some cases, the general subject of an article can be used in a product, but there are minor differences between products. The differences are indicated at midpoints in the article as appropriate. For example, the CREATE TRIGGER
article is available in SQL Database. But the ALL SERVER
option for server-level triggers, indicates that server-level triggers can't be used in SQL Database. Use database-level triggers instead.
For a list of the features that are supported and unsupported by SQL Database, see Azure SQL Database feature comparison.
To detect compatibility issues in your SQL Server databases before migrating to Azure SQL Database, and to migrate your databases, use Data Migration Assistant (DMA).