MATERIALIZED VIEWS in SQ Server

Views with computed and stored contents are known as materialized views. To put it another way, materialized views are stored on disk and updated on a regular basis according to the query definition. It provides straightforward maintenance operations while enhancing the efficiency of complex queries (usually queries with joins and aggregations).

Comparatively, the materialized views perform better than regular views. This is due to the fact that the data is kept on the disk.

SQL Server OR MySQL do not support CREATE MATERIALIZED VIEW AS SELECT, in contrast to Azure Synapse Analytics, Oracle, and PostgreSQL. We’ll examine how to achieve it with SQL Server as well as an alternative method.

Example (works in Azure Synapse Analytics):
CREATE MATERIALIZED VIEW mv_EmpPayments 
AS
SELECT
SD.iStudentID AS StudentID,
vcStudentName AS StudentName,
SUM(PaidAmount) AS TotalPaidAmount,
CourseFee – (SUM(PaidAmount)) AS RemainingBalance
FROM tbStudentDetails SD
JOIN tbStudentPayments SP ON SD.iStudentID = SP.iStudentID
WHERE vcTrxnStatus = ‘SUCCESS’;

In SQL Server & MySQL:
CREATE TABLE mv_EmpPayments
AS
SELECT
SD.iStudentID AS StudentID,
vcStudentName AS StudentName,
SUM(PaidAmount) AS TotalPaidAmount, 
CourseFee – (SUM(PaidAmount)) AS RemainingBalance
FROM tbStudentDetails SD
JOIN tbStudentPayments SP ON SD.iStudentID = SP.iStudentID
WHERE vcTrxnStatus = ‘SUCCESS’;

This table needs to be updated whenever there is a DML operation on the source tables. Few options exist to do it. Either create and execute a stored procedure manually to update the table entries or create a trigger for DML on the source tables.

Indexed Views in SQL Server:
The materialized view can still be generated in SQL Server even if there is no CREATE MATERIALIZED VIEW AS SELECT command. Instead, it is referred to as a “Indexed view” in SQL Server.

In SQL Server :
CREATE VIEW mv_EmpPayments
WITH SCHEMABINDING
AS
SELECT
SD.iStudentID AS StudentID,
vcStudentName AS StudentName,
SUM(PaidAmount) AS TotalPaidAmount, 
CourseFee – (SUM(PaidAmount)) AS RemainingBalance
FROM tbStudentDetails SD
JOIN tbStudentPayments SP ON SD.iStudentID = SP.iStudentID
WHERE vcTrxnStatus = ‘SUCCESS’;
GO

–Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_mv_EmpPayments
ON mv_EmpPayments (StudentID);
GO

Please check the articles posted on this website for the stored procedure and DML triggers.