Indexed view in Sql Server 2005 Error

Question

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

Answer 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

Answer 2

This article explains schema binding in detail.

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

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

ANSI_NULLS 
ANSI_PADDING 
ANSI_WARNINGS 
ARITHABORT 
CONCAT_NULL_YIELDS_NULL 
QUOTED_IDENTIFIERS

The NUMERIC_ROUNDABORT option must be set to OFF.

Answer 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 hxxp://msdn.microsoft.com/en-us/library/ms191432(sql.90).aspx