| Another option for storing summarized history data is using indexed views. Indexed views are also known as materialized views because they will compute and store the data. What sets them apart from standard views is that they have unique clustered indexes implemented on them to improve performance of queries run against them. In general, the greatest performance gain will be realized when creating indexed views of data that has been summarized and requires many joins to get the results. If the data you want to query has few aggregations or joins or is frequently updated, indexed views will provide little, if any, performance gain. Important  | Indexed views are only supported in SQL Server Enterprise Edition (2000 and 2005 versions). As with all Enterprise-only functionality, you can develop with Enterprise Edition functionality using SQL Server Developer Edition. |
In the following steps, you will be creating an indexed view that will return the individual product sales by salesperson in 2004. Indexed views have many restrictions that prevent you from creating an indexed view based on varying values. In this case, you are using a date range where data most likely will not change. If you want to see this data by month, then you should create a new indexed view for each month. Tip  | If you use this type of view primarily for summarized data, you could implement a set of views that would summarize the data by complete year and only use months during the current year. For example, you could have indexed views for 2004, 2005, Jan 2006, Feb 2006 and so on. At the end of each year, you could remove the monthly views for that year and create the year view. |
Creating an Indexed View for Sales Summary 1. | Create the view by executing the code below (included in the sample files as Create View.sql) in a New Query window in SQL Server Management Studio. Refer to the sidebar "Settings Required to Use Indexed Views" for greater explanation of the sections shown in bold. These sections are required in order to apply the index.
USE AdventureWorks; GO IF EXISTS(SELECT 1 FROM sys.objects WHERE name = N'v_SalesPerson2004ProductSummary') DROP VIEW Sales.v_SalesPerson2004ProductSummary GO SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON GO CREATE VIEW Sales.v_SalesPerson2004ProductSummary WITH SCHEMABINDING AS SELECT hdr.SalesPersonID ,cntc.FirstName AS SalesPersonFirstName ,cntc.LastName AS SalesPersonLastName ,prod.ProductID ,prod.Name AS ProductName ,COUNT_BIG(*) AS OrderLineCount ,SUM(dtl.OrderQty) as OrderQty ,SUM(dtl.LineTotal) as LineTotal FROM Sales.SalesOrderHeader hdr INNER JOIN Sales.SalesOrderDetail dtl ON hdr.SalesOrderID = dtl.SalesOrderID INNER JOIN HumanResources.Employee emp ON hdr.SalesPersonID = emp.EmployeeID INNER JOIN Person.Contact cntc ON emp.ContactID = cntc.ContactID INNER JOIN Production.Product prod ON dtl.ProductID = prod.ProductID WHERE hdr.OrderDate BETWEEN CONVERT(DATETIME, '1/1/2004 00:00:00',120) AND CONVERT(DATETIME,'12/31/2004 23:59:59',120) GROUP BY hdr.SalesPersonID ,cntc.FirstName ,cntc.LastName ,prod.ProductID ,prod.Name; GO
| 2. | Next, you must add the unique, clustered index to the view to make it an indexed view. In this case, you cannot use the same clustered index that was used on the table as it was not unique. In order to make the index unique, you will need to add the ProductID field to the index. Execute the following code (included in the sample files as AddIndex.sql).
USE AdventureWorks GO CREATE UNIQUE CLUSTERED INDEX cidx_v_SalesPerson2004ProductSummary ON Sales.v_SalesPerson2004ProductSummary (SalesPersonID ,ProductID); GO
|
Settings Required to Use Indexed Views While using indexed views can increase performance when retrieving summary data from your environment, there are a number of required settings and restrictions. The view created in the section "Creating an Indexed View for Sales Summary" has been designed to handle some of those restrictions. ANSI_NULLS and QUOTED_IDENTIFIER options need to be set to ON when creating the view. The ANSI_NULLS option must have been turned on for the underlying base tables as well. You must create the view using the SCHEMA_BINDING option. This will bind the schema to the schema of the underlying tables. When using aggregations and the GROUP BY clause, you must include COUNT_BIG(*) in the SELECT list. Only deterministic functions are permitted in the view syntax. You cannot use GETDATE() as it is not deterministic. You also must convert the string-formatted dates to deterministic date formats. In this example, you converted the string expressions to the DATETIME datatype with the ODBC Canonical Standard style (120).
There are a number of other settings and restrictions to be aware of. See the SQL Server Books Online topic "Creating Indexed Views" for a complete list. Be sure to refer to this list before committing to using indexed views as the design might end up being overly complicated and fail to provide the desired benefit. |
