Updating views in sql server
With SQL Server 2000 (Enterprise Edition), Microsoft has introduced the concept of Indexed Views, which can make your applications and queries run faster in the right circumstances.Views have been available throughout the history of Microsoft SQL Server.When you create the indexed view, SQL Server “materializes” the data in the view into physical table so instead of doing complex joins, aggregates, etc, it can queries the data from that “materialized” table. Let’s take a look at that using our favorite Clients and Orders table. So let’s run the query that return the list of the clients who spends more than 900,00 for the orders together with # of orders. And this is the magic – even if you don’t reference the view in the select, SQL Server founds that it can use the view for this select.Before we begin, I’d like to mention that there are quite a few requirements you have to met when you create the indexed views. This is in fact very good optimization technique if you need to deal with 3rd party applications.
Views make queries faster to write, but they don’t improve the underlying query performance.Obviously, other magic, like using the view indirectly would not work either. First of all, if you expect to support different editions of SQL Server backend, you should keep this behavior and noexpand hint in mind.Obviously optimization technique for 3rd party applications would not work either.So in the continuing evolution of the SQL Server product line and in response to Oracle’s Materialized View, Microsoft SQL Server 2000 has a new feature called the View Index.View Indexes give the product the capability to define an index on a view.