In this article, you'll find tips for using T-SQL views and developing solutions with dedicated SQL pool and serverless SQL pool in Azure Synapse Analytics.
Why use viewsViews can be used in a number of different ways to improve the quality of your solution. This article highlights a few examples of how to enrich your solution with views and includes the limitations that need to be considered.
SQL pool - create viewNote
Syntax for CREATE VIEW is not discussed in this article. For more information, see the CREATE VIEW documentation.
Architectural abstractionA common application pattern is to re-create tables using CREATE TABLE AS SELECT (CTAS), which is followed by an object renaming pattern while loading data.
The following example adds new date records to a date dimension. Note how a new table, DimDate_New, is first created and then renamed to replace the original version of the table.
CREATE TABLE dbo.DimDate_New
WITH (DISTRIBUTION = ROUND_ROBIN
, CLUSTERED INDEX (DateKey ASC)
)
AS
SELECT *
FROM dbo.DimDate AS prod
UNION ALL
SELECT *
FROM dbo.DimDate_stg AS stg
;
RENAME OBJECT DimDate TO DimDate_Old;
RENAME OBJECT DimDate_New TO DimDate;
Keep in mind that this approach can result in tables appearing and disappearing from a user's view, and prompts "table does not exist" error messages. Views can be used to provide users with a consistent presentation layer while the underlying objects are renamed.
By providing access to data through views, users don't need visibility to the underlying tables. In addition to a consistent user experience, this layer ensures that analytics designers can evolve the data model. The ability to evolve the underlying tables means designers can use CTAS to maximize performance during the data loading process.
Performance optimizationViews can also be used to enforce performance optimized joins between tables. For example, a view can incorporate a redundant distribution key as part of the joining criteria to minimize data movement.
Forcing a specific query or joining hint is another benefit of using T-SQL views. As such, the views capability ensures that joins are always performed in an optimal fashion. You'll avoid the need for users to remember the correct construct for their joins.
LimitationsViews in Synapse SQL are only stored as metadata. Consequently, the following options aren't available:
For more development tips, see Synapse SQL development overview.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4