Mastering SQL Views and Materialized Views for Optimal Database Management

In the article, we’ll make an overview of SQL views and materialized views. In database management, SQL views are critical for simplifying complex queries, enhancing data security, and providing a layer of abstraction that enables efficient data access. In turn, materialized views play a key role by offering improved query performance and offline access at the cost of periodic refreshes and contributing to optimized database management and reporting capabilities.

Contents

• What are SQL views?
• What are materialized views?
• Comparing SQL views and materialized views
• Querying with views
• Ownership and permissions
• Advanced topics
• Best practices

What are SQL views?

A SQL view functions as a virtual table that stores a SQL query retrieving data from one or multiple tables called ‘base tables’. The view requires a unique name and serves as a means to simplify data display without actually storing the data.

Suppose there is a complex table containing employee information. In that case, a SQL view can be created to selectively showcase specific data, such as displaying only employees from the IT department.

Each time you query the view, it executes the stored query associated with it. The following image illustrates the flow of a SQL view:

You can benefit from the following features, including:

• Data abstraction: Views allow users to interact with the data without directly accessing the underlying tables, providing a layer of abstraction.
• Security: Views can be used to restrict access to specific columns or rows, ensuring that users only see the data they are authorized to view.
• Simplified queries: Views simplify complex queries by encapsulating the logic within the view, making it easier for users to retrieve the required information.

What are materialized views?

A materialized view is a physical copy of the table that stores retrieved data from multiple tables in memory, which may result in higher query performance.

In practice, a user creates a materialized view with an embedded SQL query and runs it; the results of this query are persistently stored in the database. Each time a user executes the materialized query, it retrieves the data directly stored within the view, bypassing the execution of the view’s query against the underlying tables.

In other words, a materialized view operates like a special notebook where the answers to questions you might ask the database are already written down. Let’s say you maintain a database of store sales; a materialized view could display the total sales for each month. When you inquire about January’s sales, the database doesn’t recalculate all the deals for January every time; it just opens a “notebook” and shows you the already calculated result.

So, materialized views can be helpful if you have queries with stable answers and want to quickly get a ready result without wasting time on recalculation.

Take a look at the image that illustrates the materialized view flow:

The key features include:

• Performance improvement: Materialized views precompute and store the results of a query, reducing the need to execute complex queries, thus improving performance repeatedly.
• Offline access: Since materialized views store data physically, they can be used for offline analysis and reporting, even when the source tables are unavailable.
• Data aggregation and summary: Materialized views are beneficial for summarizing and aggregating large volumes of data, providing quick access to precomputed results.
• Periodic refresh: Materialized views may need to be periodically refreshed to maintain the stored data up-to-date with changes in the source tables.

Comparing SQL and materialized views

Let’s now outline the key features and differences between SQL and materialized views to understand their roles in database management.

Feature	SQL Views	Materialized Views
Physical storage	No physical storage; virtual representation	Physical storage of query results
Data retrieval	Dynamically retrieves data when queried	Precomputed results; it may require refreshing
Data persistence	Does not store data; it reflects real-time data	Stores data persistently
Performance impact	May have a slight performance overhead	Improved performance due to pre-computation
Data abstraction	Provides abstraction over underlying tables	Offers abstraction and offline accessibility
Security	Limits access to specified columns or rows	Security measures similar to base tables
Query complexity	Suitable for less complex queries	Efficient for complex and repetitive queries
Offline access	Is not applicable; real-time data representation	Supports offline analysis with stored data
Name uniqueness	Requires a unique name within a database	Requires a unique name within a database
Refreshing data	No data refreshing is needed; real-time view	Required the manual update of the stored results
Update data	A view is always updated once executed	A materialized view is updated manually or by applying triggers to it
Use cases	Simplifies queries, enhances security	Improves performance, supports offline use

Querying with views

Let’s now see how to query SQL views and materialized views on particular examples. If we try to illustrate the process of creating views, it may look as follows:

The basic syntax to create a SQL view is as follows:

CREATE VIEW [ schema_name . ] view_name AS select_query;

where:

• schema_name is the name of the schema to which the view belongs.
• view_name is the name of the view you create.
• select_query is the SELECT statement that defines the view and whose output you want to store. You can include one or multiple base tables and other views. The creation of the view requires appropriate permissions to select from the objects referenced in the SELECT clause.

For example, consider the following data from the Purchasing.Vendor table.

Suppose we need a handy list of vendors whose credit rate is good (‘4’) or higher. Following that, we’ll create a view from the Purchasing.Vendor table that will return the required data upon execution.

CREATE VIEW ListOfVendorsWithGoodRate AS
SELECT
  v.BusinessEntityID
 ,v.AccountNumber
 ,v.Name
 ,v.CreditRating
 ,v.ActiveFlag
FROM Purchasing.Vendor v
WHERE v.CreditRating >= '4';

Now, query the view just as you would query the table and see the result:

Querying materialized views is similar to querying regular tables or SQL views. After you create a materialized view, you can use standard SELECT statements to retrieve data from it.

Here is the syntax to create a materialized view:

CREATE MATERIALIZED VIEW view_name
[REFRESH [FAST|COMPLETE|FORCE] [ON DEMAND|ON COMMIT]]
[BUILD IMMEDIATE|BUILD DEFERRED]
AS
select_query;

• view_name is the name of the materialized view you want to create.
• REFRESH FAST uses an incremental refresh operation leveraging changes made to the underlying tables stored in a log file.
• REFRESH COMPLETE uses a complete refresh operation by re-running the query in the materialized view.
• REFRESH FORCE triggers a fast refresh if possible; otherwise, a complete refresh is executed. It is the default option.
• REFRESH ON DEMAND triggers a manual refresh whenever specific package functions are called. It is the default option.
• REFRESH ON COMMIT executes a fast refresh when a transaction commits changes to the underlying tables.
• BUILD IMMEDIATE populates the materialized view immediately. It is a default option.
• BUILD DEFERRED populates the materialized view on the next refresh operation.
• select_query is the SELECT query to be executed, with the results stored in the materialized view.

Let’s move on to querying materialized views. In SQL Server, they are called Indexed Views because the materialization step refers to creating a regular view and then a clustered index on that view. The view is created with schema binding, which means that the underlying table schema may not be altered while the view exists.

For example, create an indexed view that returns relevant customer details to facilitate querying and reporting.

Upon executing the view, we get the following result compiled from several tables:

Ownership and permissions

In SQL Server, creating views requires specific permissions and object ownership requirements, including:

• CREATE VIEW permission on the database
• ALTER permission on the schema in which the view is being created
• SELECT permission on the base tables of the materialized view
• REFERENCES permission on the schema containing the base tables
• The owner of the materialized view to be created must be the same as the owners of the base tables.
• A materialized view and its base tables can be in different schemas. Upon creation, the schema owner of the view becomes the owner of the materialized view, and this ownership cannot be changed or transferred.

Advanced topics

We have already covered some basic information to simplify your work with views. Now, let’s delve into more specific details to enhance your proficiency in database-related tasks.

• Azure Synapse Analytics and its relevance
• Temporary tables vs. materialized views

Azure Synapse Analytics and its relevance

Azure Synapse Analytics, formerly SQL Data Warehouse, is a cloud-based analytics service provided by Microsoft Azure. It’s designed for large-scale data processing and analytics, enabling businesses to analyze vast data efficiently. In the context of views, Azure Synapse Analytics proves helpful in enhancing the querying and analytical capabilities of the data stored in the system.

Views in Azure Synapse Analytics can help optimize query performance by encapsulating complex logic and aggregations. This is particularly valuable in a data warehousing environment where performance is critical for analytical queries.

After materialized views have been created, they also appear under the views folder of the Azure Synapse Analytics instance.

When the SELECT query uses functions unsupported in CREATE MATERIALIZED VIEW, such as COUNT, DISTINCT, COUNT(DISTINCT expression), or COUNT_BIG (DISTINCT expression), the Synapse SQL optimizer automatically re-writes those functions in the query to align with existing materialized views.

Temporary tables vs. materialized views

As mentioned before, SQL Server doesn’t have a native syntax for creating materialized views. Instead, indexed views can serve a similar purpose for specific use cases.

Look at the table comparing temporary tables with materialized views to help you choose the option that best suits your project requirements and aims.

Feature	Temporary tables	Materialized views
Purpose	Created for temporary storage within a session or transaction	Stored as physical tables, persisting beyond the session or transaction
Lifecycle	Exists during the session or transaction execution; it is automatically dropped afterward	Persists until explicitly dropped or the underlying data changes
Usage	Commonly used for temporary storage of intermediate results or breaking down complex operations	Used for persistent storage of precomputed query results, balancing performance with periodic data refreshes
Management	Requires explicit creation (CREATE TABLE #TempTable), population, and deletion (DROP TABLE #TempTable); manual management	Requires periodic refreshing (REFRESH MATERIALIZED VIEW) and scheduling; involves management of refresh strategy
Data freshness	Reflects real-time data as they are re-created for each session or transaction	Is dependent on the refresh strategy; and may not always reflect real-time data
Indexing	Can have indexes (CREATE INDEX) for optimizing query performance	Can be indexed (CREATE INDEX) for query performance optimization
Suitability	Suitable for short-term, temporary data storage needs	Suitable for persistent storage and query performance optimization, often at the expense of real-time data
Use cases	Intermediate storage during complex queries or temporary data requirements	Long-term storage of frequently used or complex query results with periodic refreshes

Best practices

To sum up, views are versatile tools that control database data access, security, and maintenance. You can use views to simplify queries, transfer data between SQL Server databases to enhance query performance and partition data. Here are a few uses for them:

• Abstraction: When you need to join data from multiple tables to compile all the data for a specific report, you can create a view that fetches and queries that data as if it were pre-joined and readily available.
• Security: The view helps you restrict access to data. This can be achieved by granting users access to the view instead of the underlying tables. The users will query only the information that is selected in the view.
• Code reusability: Views encapsulate frequently used queries, making it easier to reuse code. Instead of duplicating complex queries, you can create a view and reference it wherever needed.
• Performance optimization: Materialized views, which store the results of a query physically, can be used to optimize the performance of frequently executed and resource-intensive queries.
• Caching query results: Materialized views essentially act as a form of caching for query results, accelerating repetitive queries.
• Reducing query computation costs: If the cost of computing queries in real-time is high, materialized views allow you to offload that computational cost to precomputed results, improving overall system performance.
• Offline analysis: For scenarios where offline analysis or historical data is sufficient, materialized views can be periodically refreshed to capture and store snapshots of the data at specific points in time.

We would like to note that materialized views are not suitable for scenarios requiring real-time and up-to-date data.

Still, views may also have negative aspects influenced by factors like view complexity, underlying tables, and specific queries. They may introduce a performance overhead, especially if they involve complex joins, computations, or aggregations. If a view references multiple tables with a large number of rows, it may result in slower data retrieval due to the need for joining and filtering. Materialized views can improve query performance by precomputing and storing results. However, the data retrieved may not always be up-to-date, and there’s a cost associated with refreshing the materialized view.

Conclusion

Now, you have a solid understanding of fundamental aspects of SQL views and materialized views and how to create and query them in the SQL Server database. Additionally, we’ve outlined the ownership requirements and permissions you need for smooth work with views. In summary, this article contains a lot of useful information that may help you decide in which scenarios you can use SQL views and materialized views.

For demo purposes, we used dbForge Studio for SQL Server, a powerful and versatile IDE that can enhance productivity when developing, administering, testing, and deploying SQL Server databases. Don’t hesitate to download a 30-day trial version for free to try all the features and capabilities the tool provides.