Archive

Archive for October, 2011

Indexed View in SQL

October 5, 2011 Leave a comment

Microsoft® SQL Server™ has supported the ability to create virtual tables known as views. Historically, these views served these main purposes:

  • To provide a security mechanism that restricts users to a certain subset of data in one or more base tables.
  • To provide a mechanism that allows developers to customize how users can logically view the data stored in base tables.

Performance Gains From Indexed Views

Using indexes to improve query performance is not a new concept; however, indexed views provide additional performance benefits that cannot be achieved using standard indexes. Indexed views can increase query performance in the following ways:

  • Aggregations can be precomputed and stored in the index to minimize expensive computations during query execution.
  • Tables can be prejoined and the resulting data set stored.
  • Combinations of joins or aggregations can be stored.
CREATE VIEW Vdiscount1 WITH SCHEMABINDING AS
SELECT SUM(UnitPrice*OrderQty) AS SumPrice,
SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS SumDiscountPrice,
COUNT_BIG(*) AS Count, ProductID
FROM Sales.SalesOrderDetail
GROUP BY ProductID
GO

CREATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount1 (ProductID)
Categories: SQL