Problem with Indexed View in SQL Server, Error 8646

Question

I was just prototyping a new system for deferring certain operations until out of hours on one of our databases. I've come up with (what I think) a pretty simple schema. I was first prototyping on SQL Server 2005 Express, but have confirmed the same problem on 2008 Developer. The error I'm getting is:

Msg 8646, Level 21, State 1, Procedure Cancel, Line 6 Unable to find index entry in index ID 1, of table 277576027, in database 'xxxxxx'. The indicated index is corrupt or there is a problem with the current update plan. Run DBCC CHECKDB or DBCC CHECKTABLE. If the problem persists, contact product support.

The schema I'm using is:

create schema Writeback authorization dbo
    create table Deferrals (
     ClientID uniqueidentifier not null,
     RequestedAt datetime not null,
     CompletedAt datetime null,
     CancelledAt datetime null,
     ResolvedAt as ISNULL(CompletedAt,CancelledAt) persisted,
     constraint PK_Writeback_Deferrals PRIMARY KEY (ClientID,RequestedAt) on [PRIMARY],
     constraint CK_Writeback_Deferrals_NoTimeTravel CHECK ((RequestedAt <= CompletedAt) AND (RequestedAt <= CancelledAt)),
     constraint CK_Writeback_Deferrals_NoSchrodinger CHECK ((CompletedAt is null) or (CancelledAt is null))
     /* TODO:FOREIGN KEY */
    )
    create view Pending with schemabinding as
    select
     ClientID
    from
     Writeback.Deferrals
    where
     ResolvedAt is null
go
alter table Writeback.Deferrals add constraint
    DF_Writeback_Deferrals_RequestedAt DEFAULT CURRENT_TIMESTAMP for RequestedAt
go
create unique clustered index PK_Writeback_Pending on Writeback.Pending (ClientID)
go
create procedure Writeback.Defer
    @ClientID uniqueidentifier
as
    set nocount on

    insert into Writeback.Deferrals (ClientID)
    select @ClientID
    where not exists(select * from Writeback.Pending where ClientID = @ClientID)
go
create procedure Writeback.Cancel
    @ClientID uniqueidentifier
as
    set nocount on

    update
     Writeback.Deferrals
    set
     CancelledAt = CURRENT_TIMESTAMP
    where
     ClientID = @ClientID and
     CompletedAt is null and
     CancelledAt is null
go
create procedure Writeback.Complete
    @ClientID uniqueidentifier
as
    set nocount on

    update
     Writeback.Deferrals
    set
     CompletedAt = CURRENT_TIMESTAMP
    where
     ClientID = @ClientID and
     CompletedAt is null and
     CancelledAt is null
go

And the code that provokes the error is as follows:

declare @ClientA uniqueidentifier
declare @ClientB uniqueidentifier
select @ClientA = newid(),@ClientB = newid()

select * from Writeback.Pending
exec Writeback.Defer @ClientA
select * from Writeback.Pending
exec Writeback.Defer @ClientB
select * from Writeback.Pending
exec Writeback.Cancel @ClientB  --<-- Error being raised here
select * from Writeback.Pending
exec Writeback.Complete @ClientA
select * from Writeback.Pending
select * from Writeback.Deferrals

I've seen a few others encountering such problems, but they seem to either have aggregates in their views (and a message back from MS saying they'd remove the ability to create such indexed views in 2005 SP 1), or they resolved it by applying a merge join in their join clause (but I don't have one).

Initially there was no computed column in the Deferrals table, and the where clause in the view was testing the CompletedAt and CancelledAt columns for NULL separately. But I changed to the above just to see if I could provoke different behaviour.

All of my SET options look right for using indexed views, and if they weren't, I'd expect a less violent error to be thrown.

Any ideas?

Answer 1

you have index corruption. run checkdb and see what errors it gives you. the easiest thing you could do is to rebuild your indexes.

also take a look at this KB article if it applies to your sitution.

Also note that putting a primary key on a GUID column will create a clustered index on it which is the worst thing performance wise you could do.

Answer 2

I managed to work out what's causing this error, by trying to build up this script, from scratch, adding in pieces as I went.

It's some kind of bug that's produced if the view is created as part of a CREATE SCHEMA statement. If I separate the CREATE SCHEMA into it's own batch, and then create the table and view in separate batches, everything works fine.

Guess it's time to go to Connect/MSDN and raise some warnings...