Bug in indexed view?

We use SQL server 2000 sp3 database server in Windows 2000 Advanced server OS.
In production environment, one of the fact tables in our datamart has over
150 mil rows. A indexed view is built upon this fact table for reporting
purposes.
The problem is that when queried for the same criteria, the indexed view
returns different result than the underlying table. This problem has occured
twice in a single month. The problem was resolved upon rebuilding the indexed
view.
FYI. We have lots of data inserts as part of overnight batch process.
Queries:
SELECT sum(value)
FROM <<FactTableName>> (NOLOCK)
WHERE portfolioid = 100
and cob = '30 Jan 2006'
OPTION (EXPAND VIEWS)
SELECT sum(value)
FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
WHERE portfolioid = 100
and cob = '30 Jan 2006'
Any help on this is appreciated.
Cheers
Hi
Does the view based on a single table?
"Space_AD" <SpaceAD@.discussions.microsoft.com> wrote in message
news:43DC2E60-C5E6-4B31-8B2F-893453A3C14C@.microsoft.com...
> We use SQL server 2000 sp3 database server in Windows 2000 Advanced server
> OS.
> In production environment, one of the fact tables in our datamart has over
> 150 mil rows. A indexed view is built upon this fact table for reporting
> purposes.
> The problem is that when queried for the same criteria, the indexed view
> returns different result than the underlying table. This problem has
> occured
> twice in a single month. The problem was resolved upon rebuilding the
> indexed
> view.
> FYI. We have lots of data inserts as part of overnight batch process.
> Queries:
> --
> SELECT sum(value)
> FROM <<FactTableName>> (NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> OPTION (EXPAND VIEWS)
> SELECT sum(value)
> FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> Any help on this is appreciated.
> --
> Cheers
>
|||Yes, the indexed view is based on a single table.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW <<view>> WITH SCHEMABINDING
AS
SELECT<<col1>>
,<<col2>>
,<<col3>>
,<<col4>>
,<<col5>>
,<<col6>>
,SUM(<<numeric col1>>)
,SUM(<<numeric col2>>)
,COUNT_BIG(*)
FROM dbo.<<facttable>>AS pv
WHEREBalance = 1
GROUP
BY <<col1>>
,<<col2>>
,<<col3>>
,<<col4>>
,<<col5>>
,<<col6>>
GO
SET
ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS
ON
SET NUMERIC_ROUNDABORT OFF
GO
if exists (select * from dbo.sysindexes where name = N'<<idxname>>' and
id = object_id(N'[dbo].[<<view>>]'))
DROP INDEX [dbo].[<<view>>].[<<idxname>>]
GO
CREATE UNIQUE CLUSTERED INDEX [<<idxname>>] ON [dbo].[<<view>>] (
<<col1>>, <<col2>>, <<col3>>,<<col4>>, <<col5>>, <<col6>>)
GO
Thanks,
Arun
|||Hi!
Try remove "nolock" hint from your queries.
Micle.
"Space_AD" <SpaceAD@.discussions.microsoft.com> wrote in message
news:43DC2E60-C5E6-4B31-8B2F-893453A3C14C@.microsoft.com...
> We use SQL server 2000 sp3 database server in Windows 2000 Advanced server
> OS.
> In production environment, one of the fact tables in our datamart has over
> 150 mil rows. A indexed view is built upon this fact table for reporting
> purposes.
> The problem is that when queried for the same criteria, the indexed view
> returns different result than the underlying table. This problem has
> occured
> twice in a single month. The problem was resolved upon rebuilding the
> indexed
> view.
> FYI. We have lots of data inserts as part of overnight batch process.
> Queries:
> --
> SELECT sum(value)
> FROM <<FactTableName>> (NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> OPTION (EXPAND VIEWS)
> SELECT sum(value)
> FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> Any help on this is appreciated.
> --
> Cheers
>
|||I have already tried it. It does not do any dirty reads..
FYI.. The bug has occured again today.
Cheers,
Arun
|||CREATE CI on col1 and col2 ONLY
<arun.ns@.gmail.com> wrote in message
news:1138796568.572051.8640@.g49g2000cwa.googlegrou ps.com...
> Yes, the indexed view is based on a single table.
>
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE VIEW <<view>> WITH SCHEMABINDING
> AS
> SELECT <<col1>>
> , <<col2>>
> , <<col3>>
> , <<col4>>
> , <<col5>>
> , <<col6>>
> , SUM(<<numeric col1>>)
> , SUM(<<numeric col2>>)
> , COUNT_BIG(*)
> FROM dbo.<<facttable>> AS pv
> WHERE Balance = 1
> GROUP
> BY <<col1>>
> , <<col2>>
> , <<col3>>
> , <<col4>>
> , <<col5>>
> , <<col6>>
> GO
>
> SET
> ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS
> ON
> SET NUMERIC_ROUNDABORT OFF
> GO
> if exists (select * from dbo.sysindexes where name = N'<<idxname>>' and
> id = object_id(N'[dbo].[<<view>>]'))
> DROP INDEX [dbo].[<<view>>].[<<idxname>>]
> GO
> CREATE UNIQUE CLUSTERED INDEX [<<idxname>>] ON [dbo].[<<view>>] (
> <<col1>>, <<col2>>, <<col3>>,<<col4>>, <<col5>>, <<col6>>)
> GO
> Thanks,
> Arun
>
|||Space_AD wrote:
> We use SQL server 2000 sp3 database server in Windows 2000 Advanced
> server OS.
> In production environment, one of the fact tables in our datamart has
> over 150 mil rows. A indexed view is built upon this fact table for
> reporting purposes.
> The problem is that when queried for the same criteria, the indexed
> view returns different result than the underlying table. This problem
> has occured twice in a single month. The problem was resolved upon
> rebuilding the indexed view.
> FYI. We have lots of data inserts as part of overnight batch process.
> Queries:
> --
> SELECT sum(value)
> FROM <<FactTableName>> (NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> OPTION (EXPAND VIEWS)
> SELECT sum(value)
> FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> Any help on this is appreciated.
And there is no additional criterium that limits records in the view?
Could your problem be caused by NULL values? Just some 0.01 cents...
robert
|||The value column is not nullable.
Cheers,
Space_AD
"Robert Klemme" wrote:

> Space_AD wrote:
> And there is no additional criterium that limits records in the view?
> Could your problem be caused by NULL values? Just some 0.01 cents...
> robert
>
|||We found that this problem occured after some deadlock in the underlying
tables.
But not sure how dead locks affect the indexed view .
Cheers,
Space_AD
"arun.ns@.gmail.com" wrote:

> Yes, the indexed view is based on a single table.
>
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE VIEW <<view>> WITH SCHEMABINDING
> AS
> SELECT<<col1>>
> ,<<col2>>
> ,<<col3>>
> ,<<col4>>
> ,<<col5>>
> ,<<col6>>
> ,SUM(<<numeric col1>>)
> ,SUM(<<numeric col2>>)
> ,COUNT_BIG(*)
> FROM dbo.<<facttable>>AS pv
> WHEREBalance = 1
> GROUP
> BY <<col1>>
> ,<<col2>>
> ,<<col3>>
> ,<<col4>>
> ,<<col5>>
> ,<<col6>>
> GO
>
> SET
> ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS
> ON
> SET NUMERIC_ROUNDABORT OFF
> GO
> if exists (select * from dbo.sysindexes where name = N'<<idxname>>' and
> id = object_id(N'[dbo].[<<view>>]'))
> DROP INDEX [dbo].[<<view>>].[<<idxname>>]
> GO
> CREATE UNIQUE CLUSTERED INDEX [<<idxname>>] ON [dbo].[<<view>>] (
> <<col1>>, <<col2>>, <<col3>>,<<col4>>, <<col5>>, <<col6>>)
> GO
> Thanks,
> Arun
>
|||we have found that this problem occured just after a deadlocking scenario
involving the underlying table.
But not sure why this deadlock is causing incorrect data in indexed view.
Cheers,
Space_AD
"Space_AD" wrote:
[vbcol=seagreen]
> The value column is not nullable.
> --
> Cheers,
> Space_AD
>
> "Robert Klemme" wrote: