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'),'IsIndexable'), 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