An indexed view is a view in SQL Server or Azure SQL Database that has a unique clustered index created on it. Unlike a regular view, which is a virtual table and does not store data, an indexed view materializes the results of the query and stores them in the database, similar to a table. This can significantly improve query performance for repetitive or complex queries, especially in read-heavy workloads.

Key Characteristics of Indexed Views

1. Materialized Data:
   • Indexed views store the result set of the view’s query on disk, making it quicker to retrieve data compared to recalculating results each time.
2. Performance Improvement:
   • Ideal for queries that aggregate or join large datasets frequently.
   • Queries accessing the indexed view can benefit from pre-computed results.
3. Clustered Index Requirement:
   • An indexed view must have a unique clustered index before any additional indexes can be created.
4. Maintenance Overhead:
   • The data in the indexed view is automatically updated whenever the underlying base tables are updated, which can add write overhead.

Example of an Indexed View

Create a Base Table

CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    ProductID INT,
    SaleAmount DECIMAL(10, 2),
    SaleDate DATE
);

Create a View

CREATE VIEW SalesSummary
WITH SCHEMABINDING
AS
SELECT 
    ProductID,
    COUNT_BIG(*) AS TotalSales,
    SUM(SaleAmount) AS TotalAmount
FROM dbo.Sales
GROUP BY ProductID;

• SCHEMABINDING: Required for indexed views to ensure the underlying schema cannot change without dropping the view first.

Create a Clustered Index on the View

CREATE UNIQUE CLUSTERED INDEX IX_SalesSummary
ON SalesSummary(ProductID);

This materializes the SalesSummary view and stores its results.

Benefits of Indexed Views

1. Faster Query Performance:
   • Reduces the need to perform expensive calculations like SUM or COUNT repeatedly.
2. Pre-aggregated Data:
   • Ideal for OLAP-style queries that require pre-aggregated or summarized data.
3. Consistent Results:
   • Always updated and in sync with the base tables.

Limitations of Indexed Views

1. Design Restrictions:
   • Must use SCHEMABINDING.
   • Cannot use certain features like:
     • Non-deterministic functions (e.g., GETDATE(), NEWID()).
     • Outer joins.
     • Subqueries, common table expressions (CTEs).
2. Write Overhead:
   • Every insert, update, or delete on the base tables triggers updates in the indexed view.
3. Storage Requirement:
   • Materialized results occupy additional storage.

Use Cases for Indexed Views

1. Data Aggregation:
   • Pre-summarized data like sales totals, counts, or averages.
2. Frequent Joins:
   • Optimizing performance for queries involving complex joins.
3. Static or Slowly Changing Data:
   • Best for scenarios where the underlying data doesn’t change frequently, minimizing maintenance overhead.

Querying an Indexed View

• Direct Query:

SELECT * FROM SalesSummary;

• Indirect Query: If a query on the base table matches the indexed view’s definition, the query optimizer might automatically use the indexed view to retrieve data.

What is criteria for Indexed View

Creating an indexed view involves specific criteria and requirements to ensure it can be materialized and updated efficiently. Here are the key criteria:

1. SCHEMABINDING

• The VIEW must include the WITH SCHEMABINDING option.
• This ensures the underlying tables cannot be modified in a way that affects the view (e.g., dropping or altering columns used in the view).

2. Deterministic Expressions

• All expressions in the view must be deterministic.
  • Deterministic means that the function or expression always produces the same result for the same input (e.g., SUM or COUNT_BIG).
  • Non-deterministic functions like GETDATE(), NEWID(), or RAND() are not allowed.

3. Allowed SQL Constructs

• Permitted:
  • Aggregate functions like SUM, COUNT_BIG, AVG, MIN, and MAX.
  • GROUP BY clauses (for aggregation views).
  • Joins (INNER JOIN only; no OUTER JOIN).
  • Simple SELECT queries with basic computations.
• Not Permitted:
  • Subqueries or Common Table Expressions (CTEs).
  • OUTER JOIN or self-joins.
  • UNION, EXCEPT, or INTERSECT operators.
  • DISTINCT, TOP, or OFFSET-FETCH clauses.

4. Data Types

• Only certain data types are allowed:
  • Supported: INT, DECIMAL, CHAR, VARCHAR, DATE, DATETIME.
  • Not Supported: TEXT, NTEXT, IMAGE, XML, VARBINARY(MAX), GEOGRAPHY.

5. Base Table Requirements

• All referenced base tables must:
  • Belong to the same database and schema as the view.
  • Be referenced with two-part naming convention (schema.table_name).
  • Have the same SET options enabled at creation (e.g., ANSI_NULLS, QUOTED_IDENTIFIER).
• Tables must not include:
  • TEXT, NTEXT, IMAGE, or XML columns.
  • Computed columns, if used in the view.

6. Index Requirements

• A unique clustered index must be created on the view before it becomes an indexed view.

CREATE UNIQUE CLUSTERED INDEX IX_ViewName ON ViewName(column_name);

7. Row Modifications and Maintenance

• Any modification (INSERT, UPDATE, DELETE) on the base tables automatically triggers an update in the indexed view.
• This can result in increased overhead, so indexed views are best suited for read-heavy workloads with minimal writes.

8. Additional Restrictions

• No use of SET ROWCOUNT.
• All tables must include a primary key.
• No use of FULLTEXT indexes or filtered indexes on base tables.

Example of Indexed View Creation

Base Table

CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    ProductID INT,
    SaleAmount DECIMAL(10, 2),
    SaleDate DATE
);

View with SCHEMABINDING

CREATE VIEW SalesSummary
WITH SCHEMABINDING
AS
SELECT 
    ProductID,
    COUNT_BIG(*) AS TotalSales,
    SUM(SaleAmount) AS TotalAmount
FROM dbo.Sales
GROUP BY ProductID;

Creating the Clustered Index

CREATE UNIQUE CLUSTERED INDEX IX_SalesSummary
ON SalesSummary(ProductID);

Best Practices

• Use Indexed Views for Read-Heavy Scenarios: Ideal for OLAP-style queries, aggregations, and joins.
• Monitor Write Overhead: Indexed views can increase the cost of INSERT, UPDATE, and DELETE operations on the base tables.
• Avoid Overuse: Consider the performance trade-offs before creating too many indexed views.