Transact-SQL features supported in Azure Synapse SQL

Azure Synapse SQL is a big data analytic service that enables you to query and analyze your data using the T-SQL language. You can use standard ANSI-compliant dialect of SQL language used on SQL Server and Azure SQL Database for data analysis.

Transact-SQL language is used in Synapse SQL serverless and provisioned model can reference different objects and has some differences in the set of supported features. In this page, you can find high-level Transact-SQL language differences between consumption models of Synapse SQL.

Database objects

Consumption models in Synapse SQL enables you to use different database objects. The comparison of supported object types is shown in the following table:

Provisioned Serverless
Tables Yes No, serverless model can query only external data placed on Azure Storage
Views Yes. Views can use query language elements that are available in provisioned model. Yes. Views can use query language elements that are available in serverless model.
Schemas Yes Yes
Temporary tables Yes No
Procedures Yes No
Functions Yes Yes, only inline table-valued functions.
Triggers No No
External tables Yes. See supported data formats. Yes. See supported data formats.
Caching queries Yes, multiple forms (SSD-based caching, in-memory, resultset caching). In addition, Materialized View are supported No
Table variables No, use temporary tables No
Table distribution Yes No
Table indexes Yes No
Table partitions Yes No
Statistics Yes Yes
Workload management, resource classes, and concurrency control Yes No

Query language

Query languages used in Synapse SQL can have different supported features depending on consumption model. The following table outlines the most important query language differences in Transact-SQL dialects:

Provisioned Serverless
SELECT statement Yes. Transact-SQL query clauses FOR XML/FOR JSON, and MATCH are not supported. Yes. Transact-SQL query clauses FOR XML, MATCH, PREDICT, and query hints are not supported. OFFSET/FETCH and PIVOT/UNPIVOT can be used to query system objects (not external data).
INSERT statement Yes No
UPDATE statement Yes No
DELETE statement Yes No
MERGE statement Yes No
Transactions Yes No
Labels Yes No
Data load Yes. Preferred utility is COPY statement, but the system supports both BULK load (BCP) and CETAS for data loading. No
Data export Yes. Using CETAS. Yes. Using CETAS.
Types Yes, all Transact-SQL types except cursor, hierarchyid, ntext, text, and image, rowversion, Spatial Types, sql_variant, and xml Yes, all Transact-SQL types except cursor, hierarchyid, ntext, text, and image, rowversion, Spatial Types, sql_variant, xml, and Table type
Cross-database queries No Yes, including USE statement.
Built-in functions (analysis) Yes, all Transact-SQL Analytic, Conversion, Date and Time, Logical, Mathematical functions, except CHOOSE, IIF, and PARSE Yes, all Transact-SQL Analytic, Conversion, Date and Time, Logical, Mathematical functions.
Built-in functions (text) Yes. All Transact-SQL String, JSON, and Collation functions, except STRING_ESCAPE and TRANSLATE Yes. All Transact-SQL String, JSON, and Collation functions.
Built-in table-value functions Yes, Transact-SQL Rowset functions, except OPENXML, OPENDATASOURCE, OPENQUERY, and OPENROWSET Yes, Transact-SQL Rowset functions, except OPENXML, OPENDATASOURCE, and OPENQUERY
Aggregates Transact-SQL built-in aggregates except, except CHECKSUM_AGG and GROUPING_ID Transact-SQL built-in aggregates except STRING_AGG
Operators Yes, all Transact-SQL operators except !> and !< Yes, all Transact-SQL operators
Control of flow Yes. All Transact-SQL Control-of-flow statement except CONTINUE, GOTO, RETURN, USE, and WAITFOR Yes. All Transact-SQL Control-of-flow statement except RETURN and SELECT query in WHILE (...) condition
DDL statements (CREATE, ALTER, DROP) Yes. All Transact-SQL DDL statement applicable to the supported object types Yes. All Transact-SQL DDL statement applicable to the supported object types

Security

Synapse SQL enable you to use built-in security features to secure your data and control access. The following table compares high-level differences between Synapse SQL consumption models.

Provisioned Serverless
Logins N/A (only contained users are supported in databases) Yes
Users N/A (only contained users are supported in databases) Yes
Contained users Yes. Note: only one Azure AD user can be unrestricted admin Yes
SQL username/password authentication Yes Yes
Azure Active Directory (AAD) authentication Yes, Azure AD users Yes, Azure AD logins and users
Storage Azure Active Directory (AAD) passthrough authentication Yes Yes
Storage SAS token authentication No Yes, using DATABASE SCOPED CREDENTIAL in EXTERNAL DATA SOURCE or instance-level CREDENTIAL.
Storage Access Key authentication Yes, using DATABASE SCOPED CREDENTIAL in EXTERNAL DATA SOURCE No
Storage Managed Identity authentication Yes, using Managed Service Identity Credential Yes, using Managed Identity credential.
Storage Application identity authentication Yes No
Permissions - Object-level Yes, including ability to GRANT, DENY, and REVOKE permissions to users Yes, including ability to GRANT, DENY, and REVOKE permissions to users/logins on the system objects that are supported
Permissions - Schema-level Yes, including ability to GRANT, DENY, and REVOKE permissions to users/logins on the schema Yes, including ability to GRANT, DENY, and REVOKE permissions to users/logins on the schema
Permissions - Database-level Yes Yes
Permissions - Server-level No Yes, sysadmin and other server-roles are supported
Permissions - Column-level security Yes Yes
Roles/groups Yes (database scoped) Yes (both server and database scoped)
Security & identity functions Some Transact-SQL security functions and operators: CURRENT_USER, HAS_DBACCESS, IS_MEMBER, IS_ROLEMEMBER, SESSION_USER, SUSER_NAME, SUSER_SNAME, SYSTEM_USER, USER, USER_NAME, EXECUTE AS, OPEN/CLOSE MASTER KEY Some Transact-SQL security functions and operators: CURRENT_USER, HAS_DBACCESS, HAS_PERMS_BY_NAME, IS_MEMBER', 'IS_ROLEMEMBER, IS_SRVROLEMEMBER, SESSION_USER, SUSER_NAME, SUSER_SNAME, SYSTEM_USER, USER, USER_NAME, EXECUTE AS, and REVERT. Security functions cannot be used to query external data (store the result in variable that can be used in the query).
DATABASE SCOPED CREDENTIAL Yes Yes
SERVER SCOPED CREDENTIAL No Yes
Row-level security Yes No
Transparent Data Encryption (TDE) Yes No
Data Discovery & Classification Yes No
Vulnerability Assessment Yes No
Advanced Threat Protection Yes
Auditing Yes No
Firewall rules Yes Yes
Private endpoint Yes Yes

SQL pool and SQL on-demand use standard Transact-SQL language to query data. For detailed differences, look at the Transact-SQL language reference.

Tools

You can use various tools to connect to Synapse SQL to query data.

Provisioned Serverless
Synapse Studio Yes, SQL scripts Yes, SQL scripts
Power BI Yes Yes
Azure Analysis Service Yes Yes
Azure Data Studio Yes Yes, version 1.18.0 or higher. SQL scripts and SQL Notebooks are supported.
SQL Server Management Studio Yes Yes, version 18.5 or higher

Note

You can use SSMS to connect to SQL on-demand (preview) and query. It is partially supported starting from version 18.5, you can use it to connect and query only.

Most of the applications use standard Transact-SQL language can query both provisioned and serverless consumption models of Synapse SQL.

Storage options

Data that is analyzed can be stored on various storage types. The following table lists all available storage options:

Provisioned Serverless
Internal storage Yes No
Azure Data Lake v2 Yes Yes
Azure Blob Storage Yes Yes
Azure CosmosDB analytical storage No Yes using Synapse Link (under gated preview)

Data formats

Data that is analyzed can be stored in various storage formats. The following table lists all available data formats that can be analyzed:

Provisioned Serverless
Delimited Yes Yes
CSV Yes (multi-character delimiters not supported) Yes
Parquet Yes Yes, including files with nested types
Hive ORC Yes No
Hive RC Yes No
JSON Yes Yes
Avro No No
Delta-lake No No
CDM No No

Next steps

Additional information on best practices for SQL pool and SQL on-demand can be found in the following articles: