SQL with Manoj

 
This error occurs when you try to create an Index on top of a View which is not created by using “WITH SCHEMABINDING” option.

When you create Index on top of a View they are called “Indexed View” or “Materialized View”.
 

–> Let’s check below:

use [AdventureWorks2014]
go

DROP VIEW IF EXISTS vw_Person
GO

CREATE VIEW vw_Person
AS
SELECT 
	[BusinessEntityID], 
	[FirstName], [MiddleName], [LastName], 
	[Suffix], 
	[EmailPromotion], 
	[ModifiedDate]
FROM [Person].[Person]
GO

CREATE UNIQUE CLUSTERED INDEX CIX_vw_Person ON [vw_Person]([BusinessEntityID])
GO

Msg 1939, Level 16, State 1, Line 18
Cannot create index on view ‘vw_Person’ because the view is not schema bound.

–> So to fix this issue you can ALTER the view definition by adding “WITH SCHEMABINDING” option with ALTER/CREATE VIEW statement as shown below.

Thus, creating an Index on a Schema bound view will not throw this error.

ALTER VIEW vw_Person
WITH SCHEMABINDING --<< here <<
AS
SELECT 
	[BusinessEntityID], 
	[FirstName], [MiddleName], [LastName], 
	[Suffix], 
	[EmailPromotion], 
	[ModifiedDate]
FROM [Person].[Person]
GO

CREATE UNIQUE CLUSTERED INDEX CIX_vw_Person ON [vw_Person]([BusinessEntityID])
GO

–> By adding this rule or restriction SQL Server just wants to make sure if an Index is created on a View, nobody can directly change the definition of Table(s) underneath the View. And if it needs a change then first the View need to be dropped and re-created after altering the tables (error shown below).

ALTER [Person].[Person]
DROP COLUMN [Suffix]

Msg 5074, Level 16, State 1, Line 32
The object ‘vw_Person’ is dependent on column ‘suffix’.
Msg 4922, Level 16, State 9, Line 32
ALTER TABLE DROP COLUMN suffix failed because one or more objects access this column.