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

sql-server tsql view indexing
Jagadeesh picture Jagadeesh · Dec 14, 2011 · Viewed 83.5k times · Source

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

Joe Stefanelli picture Joe Stefanelli · Dec 14, 2011

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

Related questions

Create a view with ORDER BY clause

I'm trying to create a view with an ORDER BY clause. I have create it successfully on SQL Server 2012 SP1, but when I try to re-create it on SQL Server 2008 R2, I get this error: Msg 102, Level 15, State 1, Procedure TopUsers, …

Read More
SQL Server view or table-valued function?

Anyone have a good decisioning tree for deciding when to use a view and when to use a table-valued function in SQL Server?

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More