Export (0) Print
Expand All

Azure SQL Database Transact-SQL Reference

Updated: December 11, 2014

Most SQL Server 2014 Transact-SQL statements are fully supported in Microsoft Azure 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 2014 are not supported in SQL Database.

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.

CautionCaution
This topic discusses SQL Database Update (preview) which is a preview release for testing and education. Test databases, database copies, or new databases, are good candidates for upgrading to the preview. SQL Database Update (preview) is not supported for production databases. (Get it.)

Additional database features are available by upgrading to the free SQL Database Update (preview). For more information about this preview, see SQL Database Preview What's New. This preview release adds performance and manageability improvements, as well as support for additional features. The added features are listed below, separated into the features that become fully supported, and the features with increased support but which are still partially supported. Some of the added capabilities depend upon the SQL Database service tier. For more information about service tiers, see Azure SQL Database Service Tiers and Performance Levels.

The following features are available in the SQL Database Update (preview) when using the premium service tier.

  • In-Memory Columnstore Indexes

  • Table Partitioning

  • Parallel Queries

  • Online Indexing

These features are available in the SQL Database Update (preview) when using the basic and standard service tiers.

  • ALTER AUTHORIZATION (for supported classes)

  • ALTER DATABASE

  • Application Roles

  • Columnstore Indexes

  • Contained Database Users

  • CREATE/DROP AGGREGATE

  • CREATE/DROP FUNCTION

  • DATABASEPROPERTY

  • DBCC commands:

     

    DBCC CHECKALLOC

    DBCC CLEANTABLE

    DBCC SHOWCONTIG

    DBCC CHECKCONSTRAINTS

    DBCC DBREINDEX

    DBCC SQLPERF

    DBCC CHECKDB

    DBCC INDEXDEFRAG

    DBCC TRACESTATUS

    DBCC CHECKFILEGROUP

    DBCC INPUTBUFFER

    DBCC UPDATEUSAGE

    DBCC CHECKIDENT

    DBCC OPENTRAN

    DBCC USEROPTIONS

    DBCC CHECKTABLE

    DBCC PROCCACHE

  • DMV's: An additional 44 of the SQL Server DMV's are exposed to support the newly supported features.

  • Change Tracking

  • CHECKPOINT statement

  • Safe binary bit CLR Assemblies

  • Extended Events

  • Heaps

  • SET statements: SET ANSI_DEFAULTS, SET CONCAT_NULL_YIELDS_NULL, SET CONTEXT_INFO, SET OFFSETS

  • Analytic functions: CUME_DIST, FIRST_VALUE, LAG, LAST_VALUE, LEAD, PERCENTILE_CONT, PERCENTILE_DISC, PERCENT_RANK

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

  • UTF-16 collations

  • Views WITH ENCRYPTION Option

  • XML Indexes, WITH XMLNAMESPACES, and XML SCHEMA COLLECTION

(Top)

Without the SQL Database Update (preview), the following SQL Database Transact-SQL statements support some but not all of the arguments that exist in the corresponding SQL Server 2014 Transact-SQL statements. Refer to the linked syntax topics for details about the supported areas of each statement. As described in the previous section, some items have more support in the SQL Database Update (preview).

(Top)

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)

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

Show:
© 2014 Microsoft