Unfortunately, this query violates one of the rules for indexed views. The
rule (from CREATE INDEX / Restrictions on indexed views) that is violated
is:

-- Nonaggregate SELECT lists cannot have expressions.

This would work in a grouped list, with limitations:

-- 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.

Why did they work sometimes? I have no clue, unless the queries under the
views were aggregate queries.

Brendt


-----Original Message-----
From: Hugh du Toit
[mailto:mssqldba-ezmlmshield-x26907771.[Email address protected]
Sent: Friday, June 30, 2006 6:17 AM
To: LazyDBA Discussion
Subject: RE: non-aggregate error when indexing view



But with other views we indexed, ISNULL works fine.

(Although just now, one of those views we tried to re-index, and it came
back with this same error..?)

Weird, it worked like a charm on some tables but not on others.


-----Original Message-----
From: Wes Wilson [mailto:mssqldba-ezmlmshield-x17160564.[Email address
protected]
Sent: Friday, June 30, 2006 15:00 PM
To: LazyDBA Discussion
Subject: RE: non-aggregate error when indexing view

I tested this using Pubs DB. The index is created but SQL Server is
warning you that it won't be able to use the index. Try it without the
ISNULL(), it worked for me.

Thanks,
Wes


Wes Wilson
REGISTRAT, Inc.
Sr. Systems Administrator / Data Base Administrator


-----Original Message-----
From: Hugh du Toit
[mailto:mssqldba-ezmlmshield-x38700122.[Email address protected]
Sent: Friday, June 30, 2006 7:18 AM
To: LazyDBA Discussion
Subject: non-aggregate error when indexing view

Hi,

We're trying to get to the bottom of this error that doesn't make a lot
of sense.

Creating the view works:

IF sessionproperty('ARITHABORT') = 0 SET ARITHABORT ON IF
sessionproperty('CONCAT_NULL_YIELDS_NULL') = 0 SET
CONCAT_NULL_YIELDS_NULL ON IF sessionproperty('QUOTED_IDENTIFIER') = 0
SET QUOTED_IDENTIFIER ON IF sessionproperty('ANSI_NULLS') = 0 SET
ANSI_NULLS ON IF sessionproperty('ANSI_PADDING') = 0 SET ANSI_PADDING ON
IF sessionproperty('ANSI_WARNINGS') = 0 SET ANSI_WARNINGS ON IF
sessionproperty('NUMERIC_ROUNDABORT') = 1 SET NUMERIC_ROUNDABORT OFF go
CREATE VIEW dbo.v_myView WITH SCHEMABINDING AS
SELECT userid, ISNULL(firstname,'') as Name
FROM users

This returns 1: select
ObjectProperty(object_id('vwVer_IndexedFirstNameSurnameDOB'),'IsIndexabl
e'), so the view is indexable

But creating the index fails:
CREATE UNIQUE CLUSTERED INDEX myIndexName ON myView (userid)

it fails with "Warning: The optimizer cannot use the index because the
select list of the view contains a non-aggregate expression."

What could be causing this error? Couldn't find anything very helpful on
the net... :(

Thanks for the help
Hugh






MS Sql Server LazyDBA home page