Troubleshooting DBCC Errors on Indexed Views

In SQL Server 2000, you can create indexed views that contain table hints such as NOLOCK. Sometimes, this can lead to indexed view corruption. If your view contains table hints, drop the view, edit its definition to remove such hints, and re-create it. Then, re-create the index on the view.

If so, are the only differences between the indexed view and computed view in the aggregated column, and are the differences small for corresponding stored and computed rows? If they are not significant, considering your data and application, no corrective action is required.

If the differences are significant, drop the index on the view and re-create it. The corruption in this case is probably because of the approximate nature of floating-point arithmetic. The order in which numbers are added during indexed view maintenance can sometimes have a generally small effect on the final result. For more information about how to use approximate data types, see Using decimal, float, and real Data. If your application uses floating-point types, but your requirements can be met using an exact decimal type (numeric, money, or decimal), consider using numeric instead in a revised version of your indexed view.

If the indexed view does not contain an aggregate over values of type float or real and you receive errors 8907 or 8708, drop the index on the view and re-create it.

Do not use ALTER INDEX REBUILD to try to remove the differences between the stored and the computed view, because ALTER INDEX REBUILD does not recalculate the view before rebuilding the index.

After recreating the index on the view, run DBCC CHECKTABLE on the view to verify that no differences remain. If differences do remain, consider hardware or other issues as a possible cause.

If the differences between your indexed view and computed view are anything other than small differences in a SUM aggregate of a float or real value, the differences are probably significant. In these cases, you should drop and re-create the index on the view. If there is a difference between views only in a SUM over a float or real value and the view is small, compare the indexed and calculated view visually and decide whether the differences are significant. For a view ViewName, obtain the calculated and stored values as follows:

SELECT * FROM ViewName OPTION(EXPAND VIEWS) -- Get calculated view.
SELECT * FROM ViewName WITH(NOEXPAND)       -- Get stored view.

You may find it useful to look at the absolute or percentage difference for nonequal rows for the same group in an aggregate view to help determine whether the differences are significant. For example, the following script shows how to compare an indexed view and a computed view that differ slightly. The script displays pairs of rows for the same group that have different stored and calculated sums.

IF OBJECT_ID('v') IS NOT NULL DROP VIEW v
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
go
CREATE TABLE t
   (id int NOT NULL PRIMARY KEY, 
    a int NOT NULL, 
    b float(53) NOT NULL)
GO
CREATE VIEW v WITH SCHEMABINDING AS
SELECT a, SUM(b) AS sum_b, COUNT_BIG(*) AS c
FROM dbo.t
GROUP BY a
GO
CREATE UNIQUE CLUSTERED INDEX idx ON v(a)
GO
INSERT t VALUES(1, 1,1.0e1)
INSERT t VALUES(2, 1,1.0e2)
INSERT t VALUES(3, 2, 1.0e0)
INSERT t VALUES(4, 2, 5.0e-17)
INSERT t VALUES(5, 2, 5.0e-17)
INSERT t VALUES(6, 2, 5.0e-17)
GO
DELETE FROM t WHERE id=3
GO
DBCC CHECKTABLE ('v')
GO
-- Show the groups that have different SUMs, 
-- and the difference between the sums.
SELECT *, v1.sum_b - v2.sum_b AS sum_b_diff
FROM (SELECT * FROM v WITH (NOEXPAND)) AS v1,
     (SELECT * FROM v) AS v2
WHERE v1.a=v2.a
AND (v1.sum_b - v2.sum_b) <> 0
OPTION(EXPAND VIEWS)
GO

The result shows that only one group has a different SUM value, and that the difference sum_b_diff is very small.