The next step to materializing a view is to create a unique clustered index on the view. To create the index, you need to have a unique identifier in the view. Here, you really have to know your data.
As the code in Listing 2 shows, I had to include TerritoryID rather than just TerritoryDescription in the view query because TerritoryDescription isn't a unique identifier. The code creates the unique clustered index on a concatenation of TerritoryID plus EmployeeID. By itself, EmployeeID isn't unique in the view result set. EmployeeID plus TerritoryDescription doesn't produce a unique string, either, because TerritoryDescription isn't a candidate key; the TerritoryDescription column contains duplicate values. You must include TerritoryID in the view query, then combine TerritoryID with EmployeeID to form a unique identifier to create the index on.
Managing Indexed Views
To confirm that a view is materialized, you can run the command sp_spaceused, using the indexed view name as the argument. Figure 5 shows what the result should look like. After materializing the view, you can create additional, nonclustered indexes on it. However, for transactional databases, be very conservative about the number of indexes you create because indexed views impose extra overhead during inserts, updates, and deletes. SQL Server automatically synchronizes the indexed-view content whenever an underlying table is modified, sacrificing CPU cycles and physical I/O for consistently accurate data.
Note that you can't materialize every view. To apply an index to a view, you have to follow a lot of rules and regulations, most of which you can find in Kalen Delaney's May 2000 article "Introducing Indexed Views," InstantDoc ID 8410.
You manage an indexed view much the same way you would any other view. The sp_help and sp_helptext procedures show you the columns in a view and the code that created the view, respectively. If you need to remove an indexed view from the database, you need only a DROP VIEW command. If you prefer to simply drop the clustered index that materializes the view, you can use the DROP INDEX command. Be advised that this command will also drop any nonclustered indexes you've built on the view as well because, just as in a table index, the nonclustered indexes are built on the clustered index. If you need to modify the view, you can use the ALTER VIEW command, but it will also drop all the indexes. So, you have to rebuild all indexes after modifying a view.
Faster Queries, Slower Updates
Everything you do in a database environment involves trade-offs. When you're contemplating creating indexed views to speed up your query retrieval, you have to consider the performance hit that data inserts, updates, and deletes will take. There's no substitute for careful planning and testing in a development environment (separate from your production systems). But in documenting the performance impact of indexed views, Itzik Ben-Gan in his December 2002 article, "Points of (Indexed) Views" (InstantDoc ID 26812), calculates query-performance enhancements on the order of 17:1 for date comparison and 23:1 for finding distinct values.
The environments that will benefit most from materialized views are data warehouses and decision-support systems that are primarily read-only and have few data updates. However, some transactional environments can also benefit from materialized views. If you have a large table (in the gigabyte range) that you repeatedly aggregate or join to other large tables, you'll want to test the effect a materialized view would have on those tables. But transactional databases that have heavy insert and update activity and databases that support random record retrieval on fields other than the cluster key aren't good candidates for materialized views. The overhead of maintaining the view indexes would almost certainly degrade the database's performance.
Materialized views—when used judiciously and with care—can give your transactional database a big query-performance boost. However, indexed views aren't a solution to every performance problem. Don't use them without careful testing because they might cause significant degradation during data inserts and modifications. But when you use them appropriately, the benefits in the production environment can be well worth the time you spend in testing.