Indexed Views SQLServer 2005

I have som problems with Indexed Views.
When I need an isnull or coalesce function in the view, the optimizer
ignores to use it !
Does anyone have a clue? Same code works on SQLServer 2000 !
Regards, Joakim
You can try it out with this code:
Use AdventureWorksDW
go
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
-- You need to change Null options to do the test without isnull funciton,
so let′s prepare that first...
-- alter table factresellersales alter column unitprice money not null
-- alter table factresellersales alter column Orderquantity smallint not nul
l
-- alter table factresellersales alter column DiscountAmount float not null
select TOP 5 ProductKey, SUM(UnitPrice*OrderQuantity)-
SUM(UnitPrice*OrderQuantity*(1.00-DiscountAmount)) Rebate
from dbo.factResellerSales
group by ProductKey
order by Rebate desc
create view Vdiscount1 with schemabinding as
select SUM(isnull(UnitPrice,0)*OrderQuantity) SumPrice, -- isnull in the
view, seems to disqualify it...
SUM(UnitPrice*OrderQuantity*(1.00-DiscountAmount)) SumDiscountPrice,
COUNT_BIG(*) Count, ProductKey
from dbo.factResellerSales
group By ProductKey
go
create unique clustered index VDiscountInd on Vdiscount1 (ProductKey)Hi Joakim,
this looks like a known issue: SQL Server 2005 might not match indexed views
if the view definition uses ISNULL on a non-nullable column.
You can easily work around the problem: in the view definition, remove
ISNULL for all columns that are not nullable. Note that this does not change
the semantics of your view since the column cannot return NULL values
anyway.
Hope this helps,
-- Torsten
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"Joakim" <Joakim@.discussions.microsoft.com> wrote in message
news:83CECDE6-263A-49E0-925C-C9CF2D6E03D9@.microsoft.com...
>I have som problems with Indexed Views.
> When I need an isnull or coalesce function in the view, the optimizer
> ignores to use it !
> Does anyone have a clue? Same code works on SQLServer 2000 !
> Regards, Joakim
>
> You can try it out with this code:
> Use AdventureWorksDW
> go
> SET NUMERIC_ROUNDABORT OFF;
> SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
> QUOTED_IDENTIFIER, ANSI_NULLS ON;
> GO
> -- You need to change Null options to do the test without isnull funciton,
> so lets prepare that first...
> -- alter table factresellersales alter column unitprice money not null
> -- alter table factresellersales alter column Orderquantity smallint not
> null
> -- alter table factresellersales alter column DiscountAmount float not
> null
> select TOP 5 ProductKey, SUM(UnitPrice*OrderQuantity)-
> SUM(UnitPrice*OrderQuantity*(1.00-DiscountAmount)) Rebate
> from dbo.factResellerSales
> group by ProductKey
> order by Rebate desc
> create view Vdiscount1 with schemabinding as
> select SUM(isnull(UnitPrice,0)*OrderQuantity) SumPrice, -- isnull in the
> view, seems to disqualify it...
> SUM(UnitPrice*OrderQuantity*(1.00-DiscountAmount)) SumDiscountPrice,
> COUNT_BIG(*) Count, ProductKey
> from dbo.factResellerSales
> group By ProductKey
> go
> create unique clustered index VDiscountInd on Vdiscount1 (ProductKey)
>