Data Curruption on Stacked Indexed Views

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Data Curruption on Stacked Indexed Views

Author

Topic

Qest
Starting Member

6 Posts

Posted - 2010-07-29 : 14:44:27

Using SQL Server 2005, I am experiencing easily reproducible data corruption when stacking indexed views. The following code should clarify things:

CREATE TABLE TemporaryTable (
	A INT,
	B INT,
	C DECIMAL
)
INSERT INTO TemporaryTable VALUES (1,1,1.0)
GO
CREATE VIEW dbo.AggregateView WITH SCHEMABINDING AS SELECT
	A,
	B,
	SUM(ISNULL(C, 0)) AS C,
	COUNT_BIG(*) AS Ignore
FROM dbo.TemporaryTable GROUP BY A, B
GO
CREATE UNIQUE CLUSTERED INDEX AggregateViewIndex ON AggregateView(A, B)
GO
CREATE VIEW dbo.NestedAggregateView WITH SCHEMABINDING AS SELECT
	A,
	SUM(ISNULL(C, 0)) AS C,
	COUNT_BIG(*) AS [Irrelevant]
FROM dbo.AggregateView WITH (NOEXPAND) GROUP BY A
GO
CREATE UNIQUE CLUSTERED INDEX NestedAggregateViewIndex ON NestedAggregateView(A)
UPDATE TemporaryTable SET C = 2.0
SELECT
	A,
	SUM(ISNULL(C, 0)) AS C
FROM dbo.AggregateView WITH (NOEXPAND) GROUP BY A
SELECT A, C FROM NestedAggregateView WITH (NOEXPAND)
DROP VIEW NestedAggregateView
DROP VIEW AggregateView
DROP TABLE TemporaryTable

I get different results...
Not using index: (1,2)
Using index: (1,1)

Analyzing the update shows that AggregateView was updated, but that NestedAggregateView was not.

Any suggestions other than the obvious solution of simply not nesting the views?

Cheers,
Qest

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-07-29 : 15:32:43

remove the noexpand hints

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-07-29 : 15:40:47

by the way, it's not corruption. you know what that hint does right?

Qest
Starting Member

6 Posts

Posted - 2010-07-29 : 15:44:26

What do you mean?

Removing the NOEXPAND from NestedAggregateView's definition makes it unindexable.

quote:
Cannot create index on view 'Test.dbo.NestedAggregateView' because it references another view 'dbo.AggregateView'. Consider expanding referenced view's definition by hand in indexed view definition.

Removing the NOEXPAND from "SELECT A, C FROM NestedAggregateView WITH (NOEXPAND)" will give the right answer, true, but you aren't actually using the index anymore.

Qest
Starting Member

6 Posts

Posted - 2010-07-29 : 15:49:50

quote:
Originally posted by russell
by the way, it's not corruption. you know what that hint does right?

I thought I did. 'NOEXPAND' prevents the query optimizer from expanding the view definition, thus forcing it to use the index, no?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-07-29 : 16:02:17

it prevents access to the underlying tables

Qest
Starting Member

6 Posts

Posted - 2010-07-29 : 16:08:43

quote:
Originally posted by russell
it prevents access to the underlying tables

That's what I want.

I want the query to use the view's index, not the underlying tables. My problem is that when you update TemporaryTable, NestedAggregateView's index isn't getting updated. AggregateView's is, but not NestedAggregateView's.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-07-29 : 16:37:55

I'm not sure how you managed it, because on both SQL Server 2005 SP3 and SQL 2008 R2 I got:

Cannot create index on view 'dbo.NestedAggregateView' because the view contains a table hint. Consider removing the hint.

When I removed NOEXPAND from the view definition, I then got:

Cannot create index on view 'dbo.NestedAggregateView' because it references another view 'dbo.AggregateView'. Consider expanding referenced view's definition by hand in indexed view definition.

If you're not getting errors then there could be something wrong with your server. Whatever the situation, indexing a view based on another view is not supported, per BOL:

quote:
A view must meet the following requirements before you can create a clustered index on it:

- The view must not reference any other views, only base tables.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources