Share via

Comparing Limitations of Serverless SQL Pool Views to Traditional SQL Scripts in SSMS

Clover J 200 Reputation points
2024-01-25T01:39:52.21+00:00

I've developed SQL scripts in the serverless SQL pool to execute "select from loaded files..." commands, basically using openrowset queries. Having created views with basic select queries, I'm now interested in understanding any potential limitations they might have in the serverless SQL pool compared to the regular SQL scripts run in SQL Server Management Studio. Is there any resource or website available where I can find more information about this?

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
5,378 questions
0 comments
Accepted answer
  1. Harishga 6,005 Reputation points Microsoft External Staff
    2024-01-25T13:06:35.1+00:00

    Hi @Clover J

    Welcome to Microsoft Q&A platform and thanks for posting your question here.

    In terms of syntax, there isn't much difference between creating views in the serverless SQL pool and creating them in SQL Server Management Studio. However, there are some limitations to using views in the serverless SQL pool. For example, views cannot be indexed, and they cannot be used to create materialized views. Additionally, views cannot be used to reference external tables or views.

    Regarding views in Azure Synapse Analytics are only stored as metadata, and there are some options that are not available, such as the schema binding option, updating base tables through the view, and creating views over temporary tables. There's also no support for the EXPAND/NOEXPAND hints, and there are no indexed views in Synapse SQL.

      You can find more information about creating and using views in the serverless SQL pool in the Azure Synapse Analytics documentation.

    Here's a link to the relevant section:

    https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/create-use-views.

    https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-views
    https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-views#limitations

     Please let me know if you have any further questions or if there's anything else I can assist you with.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Amira Bedhiafi 33,866 Reputation points Volunteer Moderator
    2024-01-25T12:54:18.26+00:00

    Serverless SQL pools in Azure Synapse allow for various performance optimizations. For instance, it's recommended to use appropriate data types and smaller data sizes to improve performance and concurrency. Additionally, manually creating statistics for CSV files can help generate optimal query execution plans. You can check this guide for best practices : https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/best-practices-serverless-sql-pool Serverless SQL pools support the creation of partitioned views, which can improve query performance through partition elimination. However, not all queries support partition elimination, and it's important to avoid subqueries in filters and to use appropriate data types for filter predicates to ensure effective partition elimination. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/create-use-views Views in Synapse SQL can be used to provide a consistent presentation layer while underlying objects are modified, thus offering a stable user experience. They also allow for performance-optimized joins between tables, and can enforce specific query or joining hints, ensuring optimal joins without requiring users to remember the correct constructs https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-views

    1 person found this answer helpful.
    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.