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. A partial list of supported features for applications follows:
- Data types.
- String, arithmetic, logical, and cursor functions.
However, Azure SQL Database is designed to isolate features from any dependency on the master database. As a consequence many server-level activities are inappropriate for SQL Database and are unsupported. Features that are deprecated in SQL Server are generally not supported in SQL Database.
This topic discusses the features that are available with SQL Database when upgraded to the current version; SQL Database V12. For more information about V12, see SQL Database V12 What's New.
The following sections list features that are partially supported, and the features that are completely unsupported.
SQL Database V12 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. Refer to the linked syntax topics for details about the supported areas of each statement.
- Databases: CREATE/ALTER
- DMVs are generally available for features that are available.
- Functions: CREATE/ALTER FUNCTION
- Logins: CREATE/ALTER LOGIN
- Stored procedures: CREATE/ALTER PROCEDURE
- Tables: CREATE/ALTER
- Types (custom): CREATE TYPE
- Users: CREATE/ALTER USER
- Views: CREATE/ALTER VIEW
- 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, TRUSTWORTHY setting
- Data Collector
- Database Diagrams
- Database Mail
- DATABASEPROPERTY (use DATABASEPROPERTYEX instead)
- EXECUTE AS logins
- Encryption: extensible key management
- Eventing: events, event notifications, query notifications
- Features related to database file placement, size, and database files that are automatically managed by Microsoft Azure.
- Features that relate to high availability, which is managed through your Microsoft Azure account: backup, restore, AlwaysOn, database mirroring, log shipping, recovery modes. For more information, see Azure SQL Database Backup and Restore.
- Features that rely upon the log reader running on SQL Database: Push Replication, Change Data Capture.
- Features that rely upon the SQL Server Agent or the MSDB database: jobs, alerts, operators, Policy-Based Management, database mail, central management servers.
- Functions: fn_get_sql, fn_virtualfilestats, fn_virtualservernodes
- Global temporary tables
- Hardware-related server settings: memory, worker threads, CPU affinity, trace flags, etc. Use service levels instead.
- KILL STATS JOB
- Linked servers, OPENQUERY, OPENROWSET, OPENDATASOURCE, BULK INSERT, and four-part names
- Master/target servers
- .NET Framework CLR integration with SQL Server
- Resource governor
- Semantic search
- Server credentials. Use database scoped credentials instead.
- Sever-level items: Server roles, IS_SRVROLEMEMBER, sys.login_token. Server level permissions are not available though some are replaced by database-level permissions. Some server-level DMVs are not available though some are replaced by database-level DMVs.
- Serverless express: localdb, user instances
- Service broker
- SET REMOTE_PROC_TRANSACTIONS
- sp_configure options and RECONFIGURE. Some options are available using ALTER DATABASE SCOPED CONFIGURATION.
- SQL Server audit. Use SQL Database auditing instead.
- SQL Server Profiler
- SQL Server trace
- Trace flags. Some trace flag items have been moved to compatibility modes.
- Transact-SQL debugging
- Triggers: Server-scoped or logon triggers
- USE statement: To change the database context to a different database, you must make a new connection to the new database.
For more information about Transact-SQL grammar, usage, and examples, see Transact-SQL Reference (Database Engine) in SQL Server Books Online.
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.