How to Create and Use Indexed Views in SQL Server: Step by Step

Explains how to create and use Indexed Views in SQL Server to speed up queries with aggregations and join precomputed data. This technique is useful in read-intensive scenarios where reducing CPU and I/O cost improves query responsiveness.

Prerequisites

• SQL Server (2016+ recommended) with permissions to create indexes and views.
• SQL Server Management Studio (SSMS) or another T-SQL interface.
• Basic T-SQL knowledge: CREATE VIEW, CREATE INDEX, SELECT.

Step 1: Understand when to use Indexed Views

Indexed Views store the results of a materialized view with a clustered index. They are useful for queries with repeated aggregations or joins, reducing execution time. They are not suitable when there are many writes to the database because the index maintenance cost increases.

Step 2: Create a sample table

Start by creating a simple sales table. The example is minimal and functional to test Indexed Views.

CREATE TABLE Sales (
    SalesID INT IDENTITY PRIMARY KEY,
    ProductID INT NOT NULL,
    SaleDate DATE NOT NULL,
    Quantity INT NOT NULL,
    Amount DECIMAL(10,2) NOT NULL
);

-- Inserir alguns dados de teste
INSERT INTO Sales (ProductID, SaleDate, Quantity, Amount)
VALUES
(1, '2026-01-01', 2, 19.98),
(1, '2026-01-02', 1, 9.99),
(2, '2026-01-01', 5, 49.95);

Step 3: Rules and limitations to know

Before creating the Indexed View, comply with rules: the view must be schemabound, cannot use nondeterministic functions or OUTER JOIN, and all referenced columns must be deterministic. The clustered index requires the view to be materialized.

Step 4: Create the view with SCHEMABINDING

The view must use WITH SCHEMABINDING to prevent changes to underlying tables that would invalidate the view.

CREATE VIEW dbo.vw_SalesByProduct
WITH SCHEMABINDING
AS
SELECT
    ProductID,
    COUNT_BIG(*) AS SalesCount,
    SUM(Quantity) AS TotalQuantity,
    SUM(Amount) AS TotalAmount
FROM dbo.Sales
GROUP BY ProductID;

Step 5: Create the clustered index on the view (materialize)

After creating the view, materialize it with a clustered index. Indexing makes the view physical and speeds up queries that use it.

CREATE UNIQUE CLUSTERED INDEX IX_vw_SalesByProduct_ProductID
ON dbo.vw_SalesByProduct (ProductID);

Step 6: Query using the Indexed View

Run queries that benefit from the index. The Query Optimizer may automatically use the materialized view even if the query reads only the base table, but it is possible to force usage with the NOEXPAND hint in Standard/Enterprise editions where applicable.

-- Consulta que usa a view diretamente
SELECT ProductID, TotalQuantity, TotalAmount
FROM dbo.vw_SalesByProduct
WHERE ProductID = 1;

-- Forçar uso da view (em edições onde aplica)
SELECT ProductID, TotalQuantity
FROM dbo.vw_SalesByProduct WITH (NOEXPAND)
WHERE ProductID = 1;

Step 7: Manage maintenance and updates

When inserting, updating or deleting rows in the Sales table, SQL Server automatically updates the Indexed View. This additional cost should be monitored. For write-intensive loads, consider alternatives like periodic ETL to aggregated tables.

-- Exemplo de inserção que actualiza a Indexed View automaticamente
INSERT INTO Sales (ProductID, SaleDate, Quantity, Amount)
VALUES (1, GETDATE(), 3, 29.97);

Verify the result

Confirm that the view is materialized and being used: check for the existence of the index and inspect execution plans. Use sys.indexes and the estimated/actual plan in SSMS.

-- Verificar índice na view
SELECT object_name(object_id) AS ObjectName, name AS IndexName, type_desc
FROM sys.indexes
WHERE object_id = OBJECT_ID('dbo.vw_SalesByProduct');

-- Obter plano estimado no SSMS para ver se usa a view/index
-- (Use a opção "Display Estimated Execution Plan")

Conclusion

Indexed Views in SQL Server are a practical solution to speed up aggregate and repetitive queries, especially in read-intensive scenarios. Experiment with real data, monitor maintenance cost in write scenarios, and consider additional indexes or ETL if write load is high. Tip: test with and without WITH (NOEXPAND) to understand the impact on the plan — what result do you get in your tests?