Leveraging Indexed Views for Faster Query Performance in SQL

When working with large databases, some queries can take a long time to execute because SQL Server needs to scan many rows or perform complex calculations. One great way to speed up these queries is by using indexed views. Indexed views precompute and store the results of a query, so SQL Server can retrieve the data faster instead of calculating it on the fly.

An indexed view is a view that has a unique clustered index created on it. This process materializes the view, meaning SQL Server physically stores the result set like a table. As a result, queries that reference the indexed view can run much faster, especially if the underlying query involves aggregations or joins.

Let's start with a simple example to understand how to create and use an indexed view. Suppose you have a sales table and you want to quickly retrieve total sales by product.

CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    ProductID INT,
    Quantity INT,
    Price MONEY
);

INSERT INTO Sales VALUES
(1, 101, 2, 10.00),
(2, 102, 1, 20.00),
(3, 101, 3, 10.00);

We often want to calculate the total sales amount per product using SUM(Quantity * Price). Instead of running a query every time, we can create a view that calculates this and then create an index on it.

CREATE VIEW dbo.vwTotalSalesByProduct
WITH SCHEMABINDING
AS
SELECT
    ProductID,
    SUM(Quantity * Price) AS TotalSales,
    COUNT_BIG(*) AS CountBig
FROM dbo.Sales
GROUP BY ProductID;

Notice the use of WITH SCHEMABINDING. This is mandatory when creating indexed views. The COUNT_BIG(*) column is also required for indexing aggregated views.

CREATE UNIQUE CLUSTERED INDEX IX_TotalSalesByProduct 
ON dbo.vwTotalSalesByProduct(ProductID);

Now, when you run queries that use this view, the results are fetched from the precomputed data, greatly improving performance for large datasets.

For example, retrieving total sales for ProductID 101 becomes very fast:

SELECT TotalSales
FROM dbo.vwTotalSalesByProduct
WHERE ProductID = 101;

Remember, indexed views have some restrictions: the view definition must follow specific rules, and the base tables cannot have features like text, ntext, or image columns. Additionally, every update to the base table means SQL Server must also update the indexed view, so use them when query speed is critical.

To summarize, indexed views are a powerful tool for speeding up queries by storing precomputed results in the database. They can be especially helpful when working with aggregations or complex joins on large tables.