Sql Server 2005错误中的索引视图

I tried to add an index on a view in Sql Server 2005 an I got this error: "Cannot create index on view 'AllAssignmentNotes' because the view is not schema bound."

我试图在Sql Server 2005中的视图上添加一个索引,我得到了这个错误:“无法在视图上创建索引'AllAssignmentNotes'因为视图不是模式绑定的。”

I didn't want to put too much information here as it might be overload. Just wondering if anyone could give me some help.

我不想在这里提供太多信息,因为它可能超负荷。只是想知道是否有人可以给我一些帮助。

I went to the url the error gave me and got me nowhere. The full error is below.

我去了错误给我的网址,让我无处可去。完整错误如下。

I know you can't make give a real answer because I haven't given you all the information, my apologies.

我知道你不能给出真正的答案,因为我没有给你所有的信息,我的道歉。

TITLE: Microsoft SQL Server Management Studio
------------------------------

Create failed for Index 'IX_AssignmentId'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Index&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Cannot create index on view 'AllAssignmentNotes' because the view is not schema bound. (Microsoft SQL Server, Error: 1939)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=1939&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

4 个解决方案

#1

Just as the error says, you can't have an index on a view that isn't schema bound. To schemabind the view use

就像错误所说的那样,您不能在不受架构限制的视图上拥有索引。要架构视图使用

create view with schemabinding.

all tables referenced int the view must be fully quallified with the schemaname, i.e. dbo.table, not just table

视图中引用的所有表必须使用schemaname完全quallified,即dbo.table,而不仅仅是table

#2

This article explains schema binding in detail.

本文详细介绍了模式绑定。

http://www.mssqltips.com/tip.asp?tip=1610

#3

Assuming you created your view WITH SCHEMABINDING then its possible its the connection settings these SET options must be set to ON when the CREATE INDEX statement is executed:

假设您使用SCHEMABINDING创建了视图,那么在执行CREATE INDEX语句时,必须将这些SET选项设置为ON,这可能是连接设置:

ANSI_NULLS 
ANSI_PADDING 
ANSI_WARNINGS 
ARITHABORT 
CONCAT_NULL_YIELDS_NULL 
QUOTED_IDENTIFIERS

The NUMERIC_ROUNDABORT option must be set to OFF.

NUMERIC_ROUNDABORT选项必须设置为OFF。

#4

Dealing with indexed views was major pain since they were introduced. Rules to actually make a view that supports indexing are very strict and error messages are useless.

处理索引视图是他们引入以来的主要痛苦。实际制作支持索引的视图的规则非常严格,错误消息也没用。

You should check BOL http://msdn.microsoft.com/en-us/library/ms191432(sql.90).aspx

你应该检查BOL http://msdn.microsoft.com/en-us/library/ms191432(sql.90).aspx

#1