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

Sql ServerTsqlViewIndexing

Sql Server Problem Overview


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 ?

Sql Server Solutions


Solution 1 - Sql Server

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

Solution 2 - Sql Server

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.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionJagadeeshView Question on Stackoverflow
Solution 1 - Sql ServerJoe StefanelliView Answer on Stackoverflow
Solution 2 - Sql ServerManoj PandeyView Answer on Stackoverflow