If you have large table on which you regularly only need to process a subset or perform an aggregation you may have come across indexed views. An indexed view allows you to defined a view just like any other view (almost) and have SQL Server persist the results so that when a query is made using the view, the persisted results can be used rather than combining all the data from the source tables that make up the views query. The intention being that it increases the performance of your code by reducing IO/CPU etc.
The thing that makes indexed views easy to use is that SQL Server maintains the persisted data when the data in the underlying tables is changed, so you don't have to do anything.
To create an indexed view you need to create a schema bound view, this stops the underlying tables from being changed without changing the view. You then create a unique clustered index on the set of columns that define a unique row in the table. if you know anything about an clustered index you know that it defines the how the data for the table is stored in pages in the database. This is the same for the indexed view.
Once the unique clustered index is created you can create what ever other indexes you like on the table.
Now here is the gotcha, with a normal table, if you update a column that is not included in an index the data on the data page is changed and thats it (assuming no page split). However with SQL 2000, if you update ANY column in that is part of an indexed view, even if it is not in the wher clause or the columns of one of the indexes, the engine does a deferred update i.e. a DELETE and then an INSERT.
So whats the impact. Well because of the delete any clustered index on the base table has to be modified, then because the clustered index is updated, ALL non clustered indexes have to be updated. Thats just the base table, the same applies to the indexed view. So imagine this, you have table BaseTableA that has 4 indexes, with column NotUsed. You then have an Indexed View View1 that is based on BaseTableA and includes the NotUsed column and the view that has 3 indexes, none of which include the NotUsed column.
If you update the NotUsed column, it will cause 2 clustered indexes and 5 non-clustered indexes to be modified. Be warned
The good news is that it has been fixed in SQL 2005 so that updates to a column involved in a non-aggregate indexed view that is not in the clustered index or the where clause does not cause and insert and delete.
You can see the impact by running the script from http://sqlblogcasts.com/files/4/sql_server_engine/entry415.aspx.