Azure SQL Database Transact-SQL differences
Most of the Transact-SQL features that applications depend on are 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, etc., work without differences from SQL Server.
Why some Transact-SQL is not supported
Azure SQL Database is designed to isolate features from dependencies on the master database and the operating system. As a consequence many server-level activities are inappropriate for SQL Database. Transact-SQL statements are usually not available if they configure server-level options, operating system components, or specify file system configuration. When features that are outside of the user database are necessary, an appropriate alternative is often available in some other way from SQL Database or from another Azure feature or service.
For example, Always On is replaced with Active Geo-replication. For that reason, any Transact-SQL statements related to availability groups are not supported by SQL Database, and the dynamic management views related to Always On are not supported.
For a list of the features that are supported and unsupported by SQL Database, see Azure SQL Database considerations, guidelines and features.
Syntax that is deprecated in SQL Server is generally not supported in SQL Database.
Transact-SQL syntax partially supported in SQL Database
SQL Database supports some but not all the arguments that exist in the corresponding SQL Server 2016 Transact-SQL statements. For example, the
CREATE PROCEDURE statement is available however all the options of
CREATE PROCEDURE are not available. Describing the full syntax here would be confusing and redundant. Refer to the linked syntax topics for details about the supported areas of each statement.
- Databases: CREATE/ALTER DATABASE
- Functions: CREATE/ALTER FUNCTION
- Logins: CREATE/ALTER LOGIN
- Stored procedures: CREATE/ALTER PROCEDURE
- Tables: CREATE/ALTER TABLE
- Types (custom): CREATE TYPE
- Users: CREATE/ALTER USER
- Views: CREATE/ALTER VIEW
Transact-SQL syntax not supported in SQL Database
In addition to Transact-SQL statements related to the unsupported features described in Azure SQL Database considerations, guidelines and features, the following statements and groups of statements, are not supported.
- Collation of system objects
- Connection related: Endpoint statements,
ORIGINAL_DB_NAME. 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,
EXECUTE AS LOGINUse 'EXECUTE AS USER' instead.
- Encryption is supported except for extensible key management
- Eventing: events, event notifications, query notifications
- Syntax related to database file placement, size, and database files that are automatically managed by Microsoft Azure.
- 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.
- 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.
- Syntax that relies upon the SQL Server Agent or the MSDB database: alerts, operators, central management servers. Use scripting, such as Azure PowerShell instead.
- Global temporary tables
- Syntax related to hardware-related server settings: memory, worker threads, CPU affinity, trace flags, etc. Use service levels instead.
KILL STATS JOB
BULK INSERT, and four-part names
- .NET Framework CLR integration with SQL Server
- Semantic search
- Server credentials. Use database scoped credentials instead.
- Server-level items: Server roles,
DENYof server level permissions are not available though some are replaced by database-level permissions. Some useful server-level DMVs have equivalent database-level DMVs.
RECONFIGURE. Some options are available using ALTER DATABASE SCOPED CONFIGURATION.
- SQL Server audit. Use SQL Database auditing instead.
- SQL Server trace
- Trace flags. Some trace flag items have been moved to compatibility modes.
- Transact-SQL debugging
- Triggers: Server-scoped or logon triggers
USEstatement: To change the database context to a different database, you must make a new connection to the new database.
Full Transact-SQL reference
For more information about Transact-SQL grammar, usage, and examples, see Transact-SQL Reference (Database Engine) in SQL Server Books Online.
About the "Applies to" tags
The Transact-SQL reference includes topics related to SQL Server versions 2008 to the present. Below the topic title there is an icon bar, listing the four SQL Server platforms, and indicating applicability. For example, availability groups were introduced in SQL Server 2012. The CREATE AVAILABILTY GROUP topic indicates that the statement applies to SQL Server (starting with 2012). The statement does not apply to SQL Server 2008, SQL Server 2008 R2, Azure SQL Database, Azure SQL Data Warehouse, or Parallel Data Warehouse.
In some cases, the general subject of a topic can be used in a product, but there are minor differences between products. The differences are indicated at midpoints in the topic as appropriate.