view schema binding: error 8668: view contains a non-aggregate express 1

[jmeckley]

view. red text throws error
Server: Msg 8668, Level 16, State 1, Line 1
An index cannot be created on the view 'order_balance' because the select list of the view contains a non-aggregate expression.

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
--ALTER  views
alter  view order_balance
WITH SCHEMABINDING as
 select	cast(a.apply_to_no as int) as order_number,
	sum(a.amt_1 + a.amt_2) as balance,
	count_big(*) as cnt
 from	dbo.AROPNFIL_SQL a
 group by a.apply_to_no

go
[COLOR=red]create unique clustered index order_balance__order_number_ind on order_balance(order_number)[/color]
EXEC sp_spaceused 'order_balance'

GO

table structure

CREATE TABLE [AROPNFIL_SQL] (
	[apply_to_no] [int] NOT NULL ,
	[amt_1] [decimal](14, 2) NULL ,
	[amt_2] [decimal](14, 2) NULL
[COLOR=blue]	--addtional columns removed for simplicity[/color]
) ON [PRIMARY]
GO

can this be done, or am I missing something?

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[ptheriault]

No it can not.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[jmeckley]

is it because amt1 and amt2 are nullable?

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[ptheriault]

No, you can't use schema binding on a view that has an aggregate. And you can't index a view unless you use schema binding. You also can't bind an index that uses outer or left joins. Basically, you can only bind a view that contains a simple select statement.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[jmeckley]

according to this sqlteam article i should be able to and this executes succesfully on my sql server.

the only difference I can tell is my amt1/amt2 fields are nullable

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[ESquared]

From the article you just posted, jmeckley:

5. View definition cannot contain the following:
e. SUM on a nullable expression

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[jmeckley]

thank you, i overlooked this.

Jason Meckley
Programmer
Specialty Bakers, Inc.