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 serverless SQL pool and dedicated 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:

Dedicated 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 dedicated model. Yes. Views can use query language elements that are available in serverless model.
Schemas Yes Yes
Temporary tables Yes No
Procedures Yes Yes
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
Cost control Yes, using scale-up and scale-down actions. Yes, using the Azure portal or T-SQL procedure.

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:

Dedicated Serverless
SELECT statement Yes. Transact-SQL query clauses FOR XML/FOR JSON, MATCH, OFFSET/FETCH are not supported. Yes. Transact-SQL query clauses FOR XML, MATCH, PREDICT, GROUPNG SETS, and query hints are not supported.
INSERT statement Yes No
UPDATE statement Yes No
DELETE statement Yes No
MERGE statement Yes (preview) No
Transactions Yes Yes, applicable on meta-data objects.
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 and PARSE Yes, all Transact-SQL Analytic, Conversion, Date and Time, Logical, Mathematical functions.
Built-in functions (string) 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 functions (Cryptographic) Some No
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.
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 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 pools 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.

Dedicated 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 No
SQL username/password authentication Yes Yes
Azure Active Directory (Azure AD) authentication Yes, Azure AD users Yes, Azure AD logins and users
Storage Azure Active Directory (Azure AD) 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
Server-level roles No Yes, sysadmin, public, and other server-roles are supported
SERVER SCOPED CREDENTIAL No Yes
Permissions - Server-level No Yes
Database-scoped roles Yes Yes
DATABASE SCOPED CREDENTIAL Yes Yes
Permissions - Database-level Yes Yes
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 - 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 - Column-level security Yes Yes
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, SESSION_CONTEXT, 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).
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

Dedicated SQL pool and serverless SQL pool 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.

Dedicated 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 serverless SQL pool 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 dedicated 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:

Dedicated Serverless
Internal storage Yes No
Azure Data Lake v2 Yes Yes
Azure Blob Storage Yes Yes
Azure SQL (remote) No No
Azure CosmosDB transactional storage No No
Azure CosmosDB analytical storage No Yes, using Synapse Link
Apache Spark tables (in workspace) No PARQUET tables only using metadata synchronization
Apache Spark tables (remote) No No
Databricks tables (remote) No No

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:

Dedicated 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 Yes
CDM No No

Next steps

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