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



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html


MS Sql Server LazyDBA home page