CODEWITHSUNDEEP
sql-serversql-server-2005indexed-view
Jose
Jose

Reputation: 11091

Indexed view in Sql Server 2005 Error

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

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

Upvotes: 3

Views: 4874

Answers (3)

Pavel Urbancik
Pavel Urbancik

Reputation:

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

Upvotes: 1

u07ch
u07ch

Reputation: 13692

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: 

ANSI_NULLS 
ANSI_PADDING 
ANSI_WARNINGS 
ARITHABORT 
CONCAT_NULL_YIELDS_NULL 
QUOTED_IDENTIFIERS

The NUMERIC_ROUNDABORT option must be set to OFF.

Upvotes: 1

Ben Schwehn
Ben Schwehn

Reputation: 4565

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

Upvotes: 5

Related Questions