Finally, SQL Server 2000 restricts the syntax you can use on the CREATE VIEW statement. The view definition can't contain the following:
- TOP
- text, ntext, or image columns
- DISTINCT
- MIN, MAX, COUNT(*), COUNT(<expression>), STDEV, VARIANCE, or AVG
- SUM on a nullable expression
- a derived table
- the ROWSET function
- another view (you can reference only base tables)
- UNION
- subqueries, OUTER joins, or self-joins
- full-text predicates (CONTAIN or FREETEXT)
- COMPUTE or COMPUTE BY
Also, if the view definition contains GROUP BY, you must include the new aggregate COUNT_BIG(*) in the SELECT list. COUNT_BIG returns a value of the new data type BIGINT, which is an 8-byte integer. A view that contains GROUP BY can't contain HAVING, CUBE, ROLLUP, or GROUP BY ALL. And all GROUP BY columns must appear in the SELECT list.
To verify that you've met all these requirements, use the ObjectProperty function's new 'IsIndexable' value. The following query tells you whether you can build an index on a view:
SELECT ObjectProperty(object_id('Product_Totals'), 'IsIndexable')
A return value of 1 means you've met all requirements and can build an index on the view.
Although these restrictions might seem severe, the benefits of indexed views outweigh the prerequisites you have to meet. Two other new SQL Server 2000 features—user-defined functions (UDF) and indexes on computed columns—also have many of the same requirements. Also, remember that these restrictions apply to the view definitions, not to the queries that might use the indexed views.
Creating an Indexed View
Now that you've seen what you can and can't have inside your view definition, you're ready to create an indexed view. The first step is to define the view, as in Listing 1. Note the with SCHEMABINDING clause and specification of the owner name (dbo) for the table. At this point, you have a typical view—a stored SELECT statement that uses no storage space. In fact, if you run the system stored procedure sp_spaceused on this view, you'll get the error message
Server: Msg 15235, Level 16, State 1,
Procedure sp_spaceused, Line 91
Views do not have space allocated.
Prev. page
1
2
[3]
4
5
next page 
