Export (0) Print
Expand All

Azure SQL Database Transact-SQL Reference

Updated: November 14, 2014

This topic shows which features and Transact-SQL statements are fully supported, partially supported, and unsupported in Microsoft Azure SQL Database.

Most SQL Server Transact-SQL statements are fully supported in SQL Database. This includes the SQL Server data types, operators, and the string, arithmetic, logical, cursor functions, and the other Transact-SQL elements that most applications depend upon. Partially or unsupported functions are usually related to differences in how SQL Database manages the database (such as file, high availability, and security features) or for special purpose features such as service broker. Because SQL Database isolates many features from dependency on the master database, many server-level activities are inappropriate and unsupported. Features deprecated in SQL Server are generally not supported in SQL Database. Features not listed in the following sections, are fully supported.

noteNote
Partial support means that some options of the feature or syntax are supported and some are not. For example, the CREATE PROCEDURE statement is available however the WITH ENCRYPTION option of CREATE PROCEDURE is not available.

Partially Supported Features in SQL Database

The following SQL Database Transact-SQL statements support some but not all of the arguments that exist in the corresponding SQL Server Transact-SQL statements. Refer to the linked syntax topics for details about the supported areas of each statement.

(Top)

Features that are Not Supported in SQL Database

The list below contains features that are not supported in Microsoft Azure SQL Database. When a feature is not supported, all associated Transact-SQL statements, catalog views, dynamic management views, and functions used by that feature are also unsupported.

  • Features that rely upon the SQL Server Agent or the MSDB database: jobs, alerts, operators, Policy-Based Management, database mail, central management servers.

  • Features that rely upon the log reader: Replication, Change Data Capture.

  • Features that relate to high availability which is managed through your Microsoft Azure account: backup, restore, CHECKPOINT, AlwaysOn, database mirroring, log shipping, recovery modes. For more information, see Azure SQL Database Backup and Restore.

  • Features related to database file placement, size, partitioning, and database files which are automatically managed by Microsoft Azure.

  • Aggregations

  • Application roles

  • Change Tracking

  • Connection related: Endpoint statements, CONNECTIONPROPERTY, ORIGINAL_DB_NAME

  • CLR assemblies

  • Cross database queries, cross database ownership chaining, TRUSTWORTHY setting

  • DATABASEPROPERTY (use DATABASEPROPERTYEX instead)

  • Data Collector

  • DBCC commands (except DBCC SHOW_STATISTICS)

  • Distributed transactions

  • Encryption: symmetric and asymmetric keys, certificates, extensible key management, transparent data encryption.

  • Eventing: events, event notifications, query notifications

  • FILESTREAM

  • Full-text search, semantic search, CONTAINS, CONTAINSTABLE

  • 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.

  • Indexing: Tables without a clustered index (heaps), in-memory columnstore indexes, INDEXKEY_PROPERTY, KILL STATS JOB

  • Linked servers, OPENQUERY, OPENROWSET, OPENDATASOURCE, BULK INSERT, OPENXML

  • Master/target servers

  • NEWSEQUENTIALID

  • Plan guides

  • Resource governor

  • Rules and defaults

  • SELECT INTO clause

  • Serverless express: localdb, user instances

  • Service broker

  • SETUSER (use EXECUTE AS instead)

  • SHUTDOWN

  • sp_configure options and RECONFIGURE

  • sp_addmessage

  • SQL Server audit (use SQL Database auditing instead)

  • SQL Server trace

  • Text functions: TEXTPTR, WRITETEXT, READTEXT, UPDATETEXT, TEXTVALID

  • Trace flags

  • XML indexes and XML schema collections

  • Security and permissions:

    • GRANT/REVOKE/DENY ALL and ALL PRIVILEGES (Use more granular permissions)

    • GRANT/REVOKE/DENY endpoint, server-level, server principal, and system object permissions and related system tables such as sys.server_principals and sys.server_permissions

    • sys.login_token

    • sys.fn_builtin_permissions

    • sys.fn_translate_permissions

    • HAS_DBACCESS

    • LOGINPROPERTY

    • Contained database users, credentials.

    • Server-scoped or logon triggers

    • SQL Server server roles and user-defined server roles, IS_SRVROLEMEMBER. For more information, see Azure SQL Database Security Guidelines and Limitations.

  • Unsupported set statements:

    • SET ANSI_DEFAULTS

    • SET CONCAT_NULL_YIELDS_NULL

    • SET CONTEXT_INFO

    • SET DISABLE_DEF_CNST_CHK

    • SET OFFSETS

    • SET REMOTE_PROC_TRANSACTIONS

(Top)

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 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, and Microsoft Azure SQL Database. Near the top of each topic is a section indicating which products support the subject of the topic. If a product is omitted, then the feature described by the topic is not available in that product. For example, availability groups were introduced in SQL Server 2012. The CREATE AVAILABILTY GROUP topic indicates it applies to SQL Server (SQL Server 2012 through current version) because it does not apply to SQL Server 2008, SQL Server 2008 R2, or Microsoft Azure SQL Database.

In some cases, the general subject of topic can be used in a product, but all of the arguments are not supported. For example, contained database users were introduced in SQL Server 2012. The CREATE USER statement can be used in any SQL Server product, however the WITH PASSWORD syntax cannot be used with older versions. In this case, additional Applies to sections are inserted into the appropriate argument descriptions in the body of the topic.

(Top)

See Also

Community Additions

ADD
Show:
© 2014 Microsoft