Thursday, April 28, 2011

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
------------------------------
From stackoverflow
  • 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

  • This article explains schema binding in detail.

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

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

    Ben Schwehn : Sql Server should give a descriptive error such as "incorrect settings: 'ANSI_NULLS" in those cases, never a Error 1939
  • 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

0 comments:

Post a Comment