I am having a few problems with this view. I will like to add an index but I am getting this error
 "Server: Msg 1936, Level 16, State 1, Line 1
Cannot index the view 'ccsa.dbo.propertyList_test'. It contains one or more disallowed constructs."

Can anybody offer some assistance or recommendation?


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[propertyList_test]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[propertyList_test]


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.propertyList_test with schemabinding
AS
SELECT A.FOLIO_NBR, A.LOT, A.SA_nbr, RTRIM(u.OWN_NAME) AS Property_owner, A.SITE_NO, RTRIM(A.SITE_ADDR) AS Site_addr,
                      RTRIM(A.Address) AS Property_Address, RTRIM(A.Modi_Prop) AS Modi_prop, u.ZIP, A.STRAP, A.TWP, A.SUBDIVISION, A.CDES_NBR, A.PCN, u.CITY,
                      RTRIM(A.SITE_ADDR) + ' ' + RTRIM(A.SITE_TYPE) AS Street_Extension,
                          (SELECT SUM(CONVERT(decimal(15), coll_units)) AS totalunits
                            FROM dbo.building
                            WHERE sa_nbr = a.sa_nbr AND coll_billcode = 'C2') AS coll_units, A.ACTIVE
FROM dbo.locations A INNER JOIN
                      dbo.owners u ON A.SA_nbr = u.SA_nbr



GO
SET ANSI_NULLS ON
GO
create unique clustered index vfolionbrind on dbo.propertyList_test (Folio_nbr)
GO
SET QUOTED_IDENTIFIER OFF

__________________
Jaime E. Maccou

I found this in an old usenet post:

You'll need to look in books online, there are a whole bunch of
restrictions about what you can and can't do for a view with an index.
(I think aggregates are one of those things)

Here is what I found in SQL Help:

Restrictions on indexed views
The SELECT statement defining an indexed view must not have the TOP, DISTINCT, COMPUTE, HAVING, and UNION keywords. It cannot have a subquery.

The SELECT list may not include asterisks (*), 'table.*' wildcard lists, DISTINCT, COUNT(*), COUNT(<expression>), computed columns from the base tables, and scalar aggregates.

Nonaggregate SELECT lists cannot have expressions. Aggregate SELECT list (queries that contain GROUP BY) may include SUM and COUNT_BIG(<expression>); it must contain COUNT_BIG(*). Other aggregate functions (MIN, MAX, STDEV,...) are not allowed.

Complex aggregation using AVG cannot participate in the SELECT list of the indexed view. However, if a query uses such aggregation, the optimizer is capable of using this indexed view to substitute AVG with a combination of simple aggregates SUM and COUNT_BIG.

A column resulting from an expression that either evaluates to a float data type or uses float expressions for its evaluation cannot be a key of an index in an indexed view or on a computed column in a table. Such columns are called nonprecise. Use the COLUMNPROPERTY function to determine if a particular computed column or a column in a view is precise.

Indexed views are subject to these additional restrictions:

The creator of the index must own the tables. All tables, the view, and the index, must be created in the same database.


The SELECT statement defining the indexed view may not contain views, rowset functions, inline functions, or derived tables. The same physical table may occur only once in the statement.


In any joined tables, no OUTER JOIN operations are allowed.


No subqueries or CONTAINS or FREETEXT predicates are allowed in the search condition.


If the view definition contains a GROUP BY clause, all grouping columns as well as the COUNT_BIG(*) expression must appear in the view's SELECT list. Also, these columns must be the only columns in the CREATE UNIQUE CLUSTERED INDEX clause.
The body of the definition of a view that can be indexed must be deterministic and precise, similar to the requirements on indexes on computed columns. See Creating Indexes on Computed Columns.

Thank you for the information. I do have some of those restriction and will try to adjust my view.