CODEWITHSUNDEEP
sql-servert-sqlviewindexing
Jagadeesh
Jagadeesh

Reputation: 1790

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

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 ?

Upvotes: 66

Views: 111834

Answers (2)

Manoj Pandey
Manoj Pandey

Reputation: 1397

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.

Upvotes: 9

Joe Stefanelli
Joe Stefanelli

Reputation: 135739

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

Upvotes: 102

Related Questions