Cannot create index on view 'View_Table_Name' because the view is not schema bound

Question

I am using Views in my stored Procedure(SQL-Server). For Improving Performance, I have tried to created INDEX of that View.

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER VIEW VW_Table_Name AS SELECT Col1,Col2,Col3 FROM Table_Name  GO CREATE UNIQUE CLUSTERED INDEX Index_Name ON [VW_Table_Name](Col1) GO 

Here I am getting the Error like

Msg 1939, Level 16, State 1, Line 1 Cannot create index on view 'VW_FML' because the view is not schema bound.

Can we created Index for View in SQL Server ?

Answer 1

There are a number of restrictions on indexed views: no subqueries, no unions, no outer joins, etc. See this article for more details. But for your case, you simply need to create the view with schema binding.

CREATE VIEW VW_Table_Name WITH SCHEMABINDING AS SELECT Col1,Col2,Col3 FROM Table_Name  GO 

Answer 2

Because you are trying to create an Indexed View or Materialized View. Its mandatory for a view to have "WITH SCHEMABINDING" option if you are creating a Clustered Index on top of it.

A view is nothing but a stored query, if you are going to create an index on it, then the index is going to use that query and execute it on that table, in this case you have to make sure that the table does not change underneath. Thus by enforcing this constraint SQL Server makes sure everything remains in sync.