What is the MS SQL Server equivalent of materialized views?

In Microsoft SQL Server, the equivalent of materialized views is known as an "Indexed View" or "Materialized Indexed View." Indexed views provide a mechanism for precomputing and storing the result set of a query, allowing for improved query performance by avoiding the need to compute the result set each time the view is queried. Here are the key points about Indexed Views in SQL Server: Definition: An indexed view is a view that has been materialized by storing the results of a query in a table-like structure. The view definition includes a SELECT statement that represents the data to be stored. Indexing: Indexed views can have clustered or non-clustered indexes on the columns defined in the view. The indexes on the view improve query performance by allowing SQL Server to use the indexed structure instead of re-evaluating the underlying query. Requirements: To create an indexed view, the base table and view must meet certain criteria, including a specific set of restrictions on the view definition. The underlying tables must have unique clustered indexes. Maintainability: Indexed views are automatically maintained by SQL Server during data modifications to the underlying tables. SQL Server ensures that the indexed view remains synchronized with the base tables. Query Optimization: SQL Server's query optimizer can use indexed views to optimize query plans by selecting the appropriate indexed view based on the query. Here's a simple example of creating an indexed view: sql -- Create base table CREATE TABLE dbo.Orders ( OrderID INT PRIMARY KEY, CustomerID INT, OrderDate DATE ); -- Create a clustered index on the base table CREATE CLUSTERED INDEX IX_Orders_OrderID ON dbo.Orders(OrderID); -- Create an indexed view CREATE VIEW dbo.OrdersView WITH SCHEMABINDING AS SELECT CustomerID, COUNT_BIG(*) AS OrderCount FROM dbo.Orders GROUP BY CustomerID; -- Create an index on the indexed view CREATE UNIQUE CLUSTERED INDEX IX_OrdersView_CustomerID ON dbo.OrdersView(CustomerID); In this example, the OrdersView is an indexed view that aggregates the order count for each customer. The view is created with the WITH SCHEMABINDING option, and a clustered index is defined on the CustomerID column. This allows for efficient querying of the summarized data. Keep in mind that the view definition must adhere to certain restrictions, such as not using non-deterministic functions. read less