Design decisions and coding techniques for Synapse SQL features in Azure Synapse Analytics
In this article, you'll find a list of resources for dedicated SQL pool and serverless SQL pool functions of Synapse SQL. The recommended articles are split up into two sections: Key design decisions and development and coding techniques.
The goal of these articles is to help you develop the optimal technical approach for the Synapse SQL components within Azure Synapse Analytics.
Key design decisions
The articles below highlight concepts and design decisions for Synapse SQL development:
| Article | dedicated SQL pool | serverless SQL pool |
|---|---|---|
| Connections | Yes | Yes |
| Resource classes and concurrency | Yes | No |
| Transactions | Yes | No |
| User-defined schemas | Yes | Yes |
| Table distribution | Yes | No |
| Table indexes | Yes | No |
| Table partitions | Yes | No |
| Statistics | Yes | Yes |
| CTAS | Yes | No |
| External tables | Yes | Yes |
| CETAS | Yes | Yes |
Recommendations
Below you'll find essential articles that emphasize specific coding techniques, tips, and recommendations for development:
| Article | dedicated SQL pool | serverless SQL pool |
|---|---|---|
| Stored procedures | Yes | Yes |
| Labels | Yes | No |
| Views | Yes | Yes |
| Temporary tables | Yes | Yes |
| Dynamic SQL | Yes | Yes |
| Looping | Yes | Yes |
| Group by options | Yes | No |
| Variable assignment | Yes | Yes |
Benefits & best practices
To learn more on which scenarios are suited for Serverless SQL pool, see Serverless SQL pool benefits article.
Best practices for optimal performance using dedicated SQL pools
T-SQL feature support
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. For more information, see Transact-SQL features supported in Azure Synapse SQL article.
Next steps
For more reference information, see SQL pool T-SQL statements.