SQL Server中索引视图的问题，错误8646

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:

我只是将一个新系统原型化,推迟某些操作,直到我们的一个数据库出现故障。我想出了一个非常简单的架构(我认为)。我是第一次在SQL Server 2005 Express上进行原型设计,但在2008 Developer上确认了同样的问题。我得到的错误是:

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.

消息8646,级别21,状态1,过程取消,第6行无法在数据库'xxxxxx'中找到表277576027的索引ID 1中的索引条目。指示的索引已损坏或当前更新计划存在问题。运行DBCC CHECKDB或DBCC CHECKTABLE。如果问题仍然存在,请与产品支持部门联系。

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).

我已经看到其他一些人遇到这样的问题,但他们似乎要么在他们的观点中有聚合(并且MS的消息说他们已经删除了在2005 SP 1中创建这样的索引视图的能力),或者他们解决了它通过在其join子句中应用合并连接(但我没有)。

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.

最初,Deferrals表中没有计算列,视图中的where子句分别测试CompletedAt和CancelledAt列的NULL。但我改变了以上只是为了看看我是否可以引发不同的行为。

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.

我所有的SET选项看起来都适合使用索引视图,如果不是,我希望抛出一个不那么暴力的错误。

Any ideas?

2 个解决方案

#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.

你有索引损坏。运行checkdb并查看它给你的错误。您可以做的最简单的事情是重建索引。

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.

另请注意,将主键放在GUID列上会在其上创建聚簇索引,这是您可以做的最糟糕的性能。

#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.

如果视图是作为CREATE SCHEMA语句的一部分创建的,则会产生某种错误。如果我将CREATE SCHEMA分成它自己的批处理,然后在不同的批次中创建表和视图,一切正常。

Long overdue edit - I raised this on Connect here. It was confirmed as being an issue in SQL Server 2008.

早就应该编辑了 - 我在Connect这里提出了这个。它被确认为SQL Server 2008中的一个问题。

Internal builds (in 2010) indicated it was no longer an issue, and I have (just now, 2016) confirmed that the script in the question does not generate the same error in SQL Server 2012. The fix was not back-ported to SQL Server 2008.

内部版本(2010年)表明它不再是一个问题,我(刚才,2016)确认问题中的脚本不会在SQL Server 2012中生成相同的错误。修复程序没有后端移植到SQL Server 2008。

#1