Pls help. Cannot index the view...It contains one or more disallowed constructs

Thank you for your help.
What am I missing here?
I read the BOL. I just dont see what is the problem.
SET ANSI_PADDING ON
GO
SET ANSI_PADDING ON
GO
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[OrderLineSAItems]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[OrderLineSAItems] (
[olnID] [int] NOT NULL ,
[saiID] [int] NOT NULL ,
[olnsQuantity] [int] NOT NULL
CONSTRAINT [PK_OrderLineSAItems_olnID] PRIMARY KEY CLUSTERED
(
[olnID]
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[OrderLineOptions]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[OrderLineOptions] (
[olnID] [int] NOT NULL ,
[optID] [int] NOT NULL ,
[olnoValue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[olnoEngValue] [decimal](19, 6) NULL ,
CONSTRAINT [PK_OrderLineOptions_olnID_optID] PRIMARY KEY CLUSTERED
(
[olnID],
[optID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET ARITHABORT ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
IF OBJECT_ID('dbo.OrderLineOptionChecksums') IS NOT NULL and
objectproperty(OBJECT_ID('dbo.OrderLineOptionChecksums'),'IsView') = 1
DROP VIEW dbo.OrderLineOptionChecksums
GO
CREATE VIEW dbo.OrderLineOptionChecksums
WITH SCHEMABINDING
AS
SELECT
olns.olnID
,CHECKSUM_AGG(
binary_checksum (
olns.saiID
, olno.optID
, olno.olnoValue
, olno.olnoEngValue
)
)
as OptionsChecksum
,count_big(*) as CntBig
FROM dbo.OrderLineSAItems olns
JOIN dbo.OrderLineOptions olno ON olno.olnID = olns.olnID
GROUP BY olns.olnID
GO
CREATE UNIQUE CLUSTERED INDEX UX_OrderLineOptionChecksums_olnID ON
dbo.OrderLineOptionChecksums ( olnID )
go
CREATE NONCLUSTERED INDEX IX_OrderLineOptionChecksums_OptionsCheck
sum ON
dbo.OrderLineOptionChecksums ( OptionsChecksum )
go
-- $Log$
GOThank you, Jacco, for your explanation. It's not what I wanted to hear
though. I think it's also quite a limitation. That means that I need to use
trigger to maintain this checksum.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23eiAV$0UFHA.3076@.TK2MSFTNGP12.phx.gbl...
>I think the problem is with CHECKSUM_AGG. The idea behind the
>implementation of indexed views is that when a row is
>inserted/updated/deleted in one of the underlying tables, the new values in
>the indexed views can be calculated from just the changes in the underlying
>tables, without having to access any other rows in the table(s). I don't
>think this is the case with CHECKSUM_AGG, or in other words, if for example
>you delete a row from a table, you can't calculate the new value for the
>CHECKSUM_AGG by deducting the checksum for the deleted row from the value
>that was in the view previously. You would have to access the other rows in
>the table to recalculate the checksum.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Farmer" <someone@.somewhere.com> wrote in message
> news:%23YeZeqyUFHA.1044@.TK2MSFTNGP10.phx.gbl...
>|||Jacco
I was just reading more on indexed views and I have a question.
How would you explain
SUM(X), COUNT_BIG(X)
being allowed then? It has to go and re-read all other rows to get a new sum
if a row is deleted/updated/inserted.
Thanks
Vlad
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23eiAV$0UFHA.3076@.TK2MSFTNGP12.phx.gbl...
>I think the problem is with CHECKSUM_AGG. The idea behind the
>implementation of indexed views is that when a row is
>inserted/updated/deleted in one of the underlying tables, the new values in
>the indexed views can be calculated from just the changes in the underlying
>tables, without having to access any other rows in the table(s). I don't
>think this is the case with CHECKSUM_AGG, or in other words, if for example
>you delete a row from a table, you can't calculate the new value for the
>CHECKSUM_AGG by deducting the checksum for the deleted row from the value
>that was in the view previously. You would have to access the other rows in
>the table to recalculate the checksum.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Farmer" <someone@.somewhere.com> wrote in message
> news:%23YeZeqyUFHA.1044@.TK2MSFTNGP10.phx.gbl...
>|||No, because if you add a row (or multiple rows) to a base table for an
indexed view you can calculate the new values for SUM and BIG_COUNT from the
existing values for SUM and BIG_COUNT and the values for the inserted
row(s).
If you have a table
CREATE TABLE t(i INT IDENTITY PRIMARY KEY, v INT NOT NULL)
with an indexed view SELECT SUM(v) as sum_v, COUNT_BIG(*) as cnt
If you have 2 rows in the table:
1,2 and 2,3
The indexed view will have the values: 5, 2
When you insert another row into the table with v = 4, you can update the
view by adding 4 to the value for sum_v that is already there (5), and
increasing the value for cnt by 1. There is no need to revisit the rows that
are already in the table. This doesn't work for CHECKSUM_AGG though.
Jacco Schalkwijk
SQL Server MVP
"Farmer" <someone@.somewhere.com> wrote in message
news:%23IgX3PYVFHA.544@.TK2MSFTNGP15.phx.gbl...
> Jacco
> I was just reading more on indexed views and I have a question.
> How would you explain
> SUM(X), COUNT_BIG(X)
> being allowed then? It has to go and re-read all other rows to get a new
> sum if a row is deleted/updated/inserted.
> Thanks
> Vlad
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:%23eiAV$0UFHA.3076@.TK2MSFTNGP12.phx.gbl...
>|||Thank you.
Your explanation makes total sense and your logic is very sound.
However, I am still disapointed that this is the case, even though you are
right, that SQL does not deal with this problem.
It would have been such a powerful feature.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:OvC5WzYVFHA.3544@.TK2MSFTNGP12.phx.gbl...
> No, because if you add a row (or multiple rows) to a base table for an
> indexed view you can calculate the new values for SUM and BIG_COUNT from
> the existing values for SUM and BIG_COUNT and the values for the inserted
> row(s).
> If you have a table
> CREATE TABLE t(i INT IDENTITY PRIMARY KEY, v INT NOT NULL)
> with an indexed view SELECT SUM(v) as sum_v, COUNT_BIG(*) as cnt
> If you have 2 rows in the table:
> 1,2 and 2,3
> The indexed view will have the values: 5, 2
> When you insert another row into the table with v = 4, you can update the
> view by adding 4 to the value for sum_v that is already there (5), and
> increasing the value for cnt by 1. There is no need to revisit the rows
> that are already in the table. This doesn't work for CHECKSUM_AGG though.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Farmer" <someone@.somewhere.com> wrote in message
> news:%23IgX3PYVFHA.544@.TK2MSFTNGP15.phx.gbl...
>|||Yes, but it wouldn't have been possible to have all the performance
optimizations that indexed views provide if constructs like CHECKSUM_AGG
were allowed. And you can still do what you want with a non-indexed view or
a trigger.
Jacco Schalkwijk
SQL Server MVP
"Farmer" <someone@.somewhere.com> wrote in message
news:efPG8QZVFHA.548@.tk2msftngp13.phx.gbl...
> Thank you.
> Your explanation makes total sense and your logic is very sound.
> However, I am still disapointed that this is the case, even though you are
> right, that SQL does not deal with this problem.
> It would have been such a powerful feature.
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:OvC5WzYVFHA.3544@.TK2MSFTNGP12.phx.gbl...
>