SQL Server Magazine

PLEASE NOTE: The authors based their SQL Server 2000 articles for this issue on pre-Beta 2 versions, so you might notice some differences between Beta 2 and the behavior or interfaces we describe in this issue. In particular, please note that the indexed views feature will be available only in SQL Server 2000 Enterprise Edition. However, you can install Enterprise Edition on NT 4 Server and Windows 2000 (Win2K) Server. You don't need NT 4.0 Enterprise or Win2K Advanced Server.

Server 2000's powerful new support for indexed views promises to make your life easier while making your applications and queries run faster. Sound too good to be true? Indexed views let you precompute all sorts of joins, aggregations, and computations so that you don't have to write such conditions into every query. And Microsoft is reporting gains of 10 to 100 times in the performance of applications and queries that access indexed views instead of base tables. Although Oracle supports a similar feature called materialized views, SQL Server's new indexed views go far beyond what the competition offers.

Views from the Top
SQL Server has supported views since its earliest incarnation. A view is essentially a stored SELECT statement that acts as a filter. For example, suppose you have a large table containing all of your company's customer records, but sometimes you're interested in only the names and phone numbers of customers who live in your ZIP code. You could create a view similar to the following:

CREATE VIEW local_customers
AS
SELECT name, phone_number
FROM big_customer_list
WHERE zip = '98370'

You can now access this view as if it were a table but without requiring the separate storage space associated with a table. Whenever a query references the view, SQL Server merges the code that defines the view with any other conditions you've specified. SQL Server then optimizes, compiles, and executes the resulting query. Using some of SQL Server 7.0's special caching mechanisms, the database might be able to reuse the plan, but reuse isn't as likely as with SQL Server stored procedures. (For details about plan caching and reuse, see Inside SQL Server, "SQL Server 7.0 Plan Caching," September 1999.)

Views have two main benefits. First, they simplify query writing because they don't require you to directly access tables and because they often already include restrictions, computations, aggregations, and joins—saving you from retyping those conditions. Second, views provide a security mechanism, letting you give users access to an appropriate view of a table's data instead of access to the entire table.

A view contains no saved data; it's only a saved query. Therefore, a view's contents are dynamic. If you add rows to a table or change data values, the view will show the new information. The downside of this behavior is that if the view contains computations based on columns in the table, SQL Server must perform those computations whenever someone accesses the view. In addition, to help process the query, the database can't use an index on any column involved in a computation.

Consider a typical view that shows the day of the week on which each order was taken. The base table has an Orderdate column, but let's say you want to retrieve order information, too. You could create a view like this:

USE northwind
GO
CREATE VIEW OrderDates
AS
SELECT orderId, Day = datepart(month, Orderdate), Orderdate, ShipName, ShipCity, ShipPostalCode
FROM Orders

You can then use the view to retrieve all orders placed in December (to analyze how much your business increased during the holidays), but no index would help find these rows. An index on the Orderdate column would locate rows by the exact date, but not by the month, in which the order was placed.

With SQL Server 2000's indexed views, you can now build a clustered index on a view. A clustered index is the only type of SQL Server index that contains data; the clustered index on a view contains all the data that makes up the view definition. As soon as you create this clustered index, the view is materialized, meaning SQL Server allocates storage space for it. You can then treat the view like a table, building multiple nonclustered indexes on it. (I based the following explanations and examples on SQL Server 2000 Early Adopters Program 4—EAP4—build 047, a build between beta 1 and beta 2.)

Creating Your View
Before creating an indexed view, you need to guarantee the view will always return the same results for the same underlying data. To do so, make sure the following SET conditions are ON:

   Prev. page   [1] 2 3 4 5     next page