Sql Server Indexed Views Complete Guide

Prerequisites

1. Basic Understanding of SQL: You should have a foundational understanding of SQL, including SELECT, JOIN, WHERE, GROUP BY, and ORDER BY.
2. SQL Server Knowledge: Familiarity with SQL Server databases, tables, indexes, and views.
3. Database Schema: A sample database schema to work with. We’ll use a simple example here.

Sample Database Schema

Let’s assume we have the following two tables in our database:

Products Table

| ProductID (PK) | Name | CategoryID (FK) | Price | |----------------|------------|-----------------|-------| | 1 | Laptop | 1 | 800 | | 2 | Mouse | 2 | 25 | | 3 | Keyboard | 2 | 45 |

Categories Table

| CategoryID (PK) | CategoryName | |-----------------|--------------| | 1 | Electronics | | 2 | Accessories |

Objective

The goal is to create an indexed view that combines these tables and performs aggregate functions, which can then be queried for faster results.

Step 1: Create a View

First, let’s create a simple non-indexed view that joins Products and Categories and calculates the total price and number of products per category.

CREATE VIEW v_ProductsByCategory
WITH SCHEMABINDING -- Required for indexed views
AS
SELECT 
    c.CategoryID,
    c.CategoryName,
    SUM(p.Price) AS TotalPrice,
    COUNT_BIG(*) AS ProductCount -- Use COUNT_BIG for indexed views if you expect more than 2 billion rows.
FROM 
    dbo.Products p
INNER JOIN 
    dbo.Categories c ON p.CategoryID = c.CategoryID
GROUP BY 
    c.CategoryID, c.CategoryName;

Notes:

• WITH SCHEMABINDING: Ensures that the underlying table schema cannot be changed as long as the view exists.
• COUNT_BIG: Recommended instead of COUNT for indexed views due to larger row count capabilities.

Step 2: Create an Index on the View

Once the view is created with schema binding, we can create a unique clustered index on it to make it an indexed view.

CREATE UNIQUE CLUSTERED INDEX IX_v_ProductsByCategory ON v_ProductsByCategory(CategoryID);

Notes:

• Indexed views must have a unique clustered index.
• The indexed view will act as a materialized view, storing the result set on disk.

Step 3: Verify the Indexed View Creation

To verify that the view has been successfully created with an index, we can use the sys.indexes system catalog view.

SELECT 
    name,
    index_id,
    type_desc
FROM 
    sys.indexes
WHERE 
    object_id = OBJECT_ID('v_ProductsByCategory');

Expected Output: | name | index_id | type_desc | |---------------------|----------|-----------------| | IX_v_ProductsByCategory | 1 | CLUSTERED |

Step 4: Query the Indexed View

Now that we have our indexed view, let’s perform a query to see the performance improvement.

SELECT * FROM v_ProductsByCategory;

Expected Output: | CategoryID | CategoryName | TotalPrice | ProductCount | |------------|--------------|------------|--------------| | 1 | Electronics | 800 | 1 | | 2 | Accessories | 70 | 2 |

When you run this query, SQL Server uses the materialized data from the index instead of performing the join and aggregation at runtime, resulting in faster performance.

Step 5: Considerations for Using Indexed Views

1. Query Complexity: Complex queries or views with many joins or filters should be evaluated for performance.
2. Maintenance Overhead: Inserting, updating, or deleting data in the base tables can be slower with indexed views, as the view's index needs to be updated.
3. Data Consistency: Indexed views ensure that the data is always consistent with the base tables because they are based on schema-bound views.

Example Scenario

Imagine you have a sales report that needs to calculate the total revenue and number of orders for each customer frequently. To improve performance, you can create an indexed view like this:

Base Tables

Assume we have the following tables:

• Customers: Stores customer information.
• Orders: Stores order details, including the customer ID and order total.

Customers Table

| CustomerID (PK) | FirstName | LastName | CustomerSince | |-----------------|-----------|----------|----------------| | 1 | John | Doe | 2020-01-01 | | 2 | Jane | Smith | 2019-06-15 |

Orders Table

| OrderID (PK) | CustomerID (FK) | OrderDate | OrderTotal | |--------------|-----------------|-----------|------------| | 1001 | 1 | 2022-01-02| 500 | | 1002 | 1 | 2022-02-25| 1200 | | 1003 | 2 | 2022-03-15| 300 |

Indexed View

Create a view that summarizes the revenue and order counts for each customer.

CREATE VIEW dbo.v_CustomerSalesSummary
WITH SCHEMABINDING -- Required
AS
SELECT 
    o.CustomerID,
    c.FirstName,
    c.LastName,
    SUM(o.OrderTotal) AS TotalRevenue,
    COUNT_BIG(*) AS OrderCount
FROM 
    dbo.Orders o
INNER JOIN 
    dbo.Customers c ON o.CustomerID = c.CustomerID
GROUP BY 
    o.CustomerID, c.FirstName, c.LastName;

Create a unique clustered index on the view.

CREATE UNIQUE CLUSTERED INDEX IDX_CustomerSalesSummary ON dbo.v_CustomerSalesSummary(CustomerID);

Query the Indexed View

Now, querying the sales summary is much faster:

SELECT * FROM dbo.v_CustomerSalesSummary;

Expected Output: | CustomerID | FirstName | LastName | TotalRevenue | OrderCount | |------------|-----------|----------|--------------|------------| | 1 | John | Doe | 1700 | 2 | | 2 | Jane | Smith | 300 | 1 |

Conclusion

Indexed Views in SQL Server are a powerful tool for optimizing performance in scenarios involving frequent and complex queries. They come with some maintenance considerations, but when used appropriately, they can significantly reduce the burden on the server by precomputing results and storing them physically.