Let us discuss how to create the materialized views and the concept of materialized views in SQL Server and discuss their benefits over regular views. By understanding this article, we hope that our readers will get a better idea on how to create and implement the materialized views in SQL Server.
Definition of Materialized Views in SQL Server
A strong database object called a materialised view has several benefits over a regular view. Materialised views use a different strategy from traditional views which are virtual and don’t hold the data on their own. They store the outcomes of a query as a physical table that contains the instantly accessible pre-computed data. The query performance is significantly impacted by the important difference between materialised views and normal views. The database engine obtains the data immediately from the stored table when a query is run against a materialised view, negating the need to run the original query each time.
The ability of materialized views to store the pre-computed data brings a large number of benefits. By avoiding the need to execute the expensive calculations repeatedly, materialized views drastically reduce the query execution time, making them an invaluable tool for improving the overall database performance. Moreover, materialized views can reduce the load on underlying tables and indexes as the computed results are already stored separately.
Create a Sample Table
Let us create a sample table that we will use as a basis for our examples throughout this article. Let’s create a table named “Orders” with four columns such as the “OrderID” which serves as the primary key, the “CustomerName” which stores the customer identifier, the “OrderDate” which records the date that the order was placed, and the “TotalAmount” which holds the total value of the order.
OrderID INT PRIMARY KEY,
CustomerName VARCHAR(100),
OrderDate DATE,
TotalAmount DECIMAL(10, 2)
);
GO
INSERT INTO Orders (OrderID, CustomerName, OrderDate, TotalAmount)
VALUES
(1, 'Ravi Kumar', '2023-05-10', 150.50),
(2, 'Priya Patel', '2023-05-15', 200.75),
(3, 'Sanjay Sharma', '2023-03-20', 350.00),
(4, 'Meera Gupta', '2023-04-05', 100.25),
(5, 'Rajesh Singh', '2023-03-12', 450.80),
(6, 'Pooja Verma', '2023-04-18', 300.50);
Output:
OrderID CustomerName OrderDate TotalAmount
1 Ravi Kumar 2023-05-10 150.50
2 Priya Patel 2023-05-15 200.75
3 Sanjay Sharma 2023-03-20 350.00
4 Meera Gupta 2023-04-05 100.25
5 Rajesh Singh 2023-03-12 450.80
6 Pooja Verma 2023-04-18 300.50
Use of Materialized Views in SQL Server
1. Materialized Views with Aggregate Functions
Materialized views offer powerful capabilities when it comes to incorporating the aggregate functions. Using these functions, we can perform the calculations on pre-computed data within the view itself, simplify the queries, and improve the overall performance. Suppose we create a materialized view called “CustomerOrderStatistics” that provides the aggregated information about each customer’s orders.
AS
SELECT
m.month_name,
COUNT(DISTINCT o.CustomerName) AS CustomerCount
FROM
Orders o
INNER JOIN
customer_info c ON o.CustomerName = c.CustomerName
INNER JOIN
months m ON MONTH(o.OrderDate) = m.month_id
GROUP BY
m.month_name;
Here in the T-SQL query, we create a materialized view that aggregates the order data based on the month and counts the distinct customer names. We join the “Orders” table with the “customer_info” table using the customer’s name as the common field and then join with the “months” table based on the month ID that is extracted from the order date. Using and creating this materialized view, we can easily check the monthly customer purchase statistics. We can now query the “MonthlyCustomerPurchases” view directly using the SELECT statement.
month_name,
CustomerCount
FROM
MonthlyCustomerPurchases;
Output:
May 2
March 2
April 2
The T-SQL query provides the desired output. The result shows us the month name and the count of distinct customers who made the purchases in each month based on the data in the materialized view.
2. Materialized View with Inner Join
Let’s create a materialized view using an inner join operation. This allows us to combine the data from multiple tables and store the results in a materialized view, providing a simplified and efficient way to query the data. Suppose there are two tables, “Orders” and “CustomerInfo”. Now, let us create the materialized view using an inner join between the “Orders” and “CustomerInfo” tables.
SELECT
o.OrderID,
o.OrderDate,
o.TotalAmount,
c.CustomerName,
c.Hometown,
c.Gender
FROM
Orders o
INNER JOIN
CustomerInfo c ON o.CustomerID = c.CustomerID;
Here in the T-SQL query, the “CustomerOrderDetails” materialized view combines the data from the “Orders” and “CustomerInfo” tables using an inner join. It selects the order ID, order date, total amount, customer name, hometown, and gender from both tables and stores the results in the materialized view. To view the output, we can type them following the T-SQL query.
OrderID,
OrderDate,
TotalAmount,
CustomerName,
Hometown,
Gender
FROM
CustomerOrderDetails;
Output:
1 2023-05-10 150.50 Ravi Kumar Mumbai Male
2 2023-05-15 200.75 Priya Patel Delhi Female
3 2023-03-20 350.00 Sanjay Sharma Bangalore Male
4 2023-04-05 100.25 Meera Gupta Hyderabad Female
5 2023-03-12 450.80 Rajesh Singh Chennai Male
6 2023-04-18 300.50 Pooja Verma Goa Female
Here, the result represents the combined details of customer orders from the “CustomerOrderDetails” materialized view.
Conclusion
Materialized views in SQL Server offer significant advantages over regular views. The materialized views provide improved query performance, reduced database load, and simplified queries by storing the pre-computed data. By understanding and using the materialized views in SQL Server, we can use its benefits and it allows for a more efficient and effective use of the SQL Server databases.
