Creating Index on View

prince500 · May 23rd, 2007, 10:53 AM

Hi,

I am trying to create in dexes on view but it need to bound with Schema bound (which is false on view).
Colud you please advise
a) how can I change Schema bound to True (if we can do by GUI)
b) Or we can create index without that

thaks

AA

sshelper · May 23rd, 2007, 11:43 AM

a. To make your schema bound, in Enterprise Manager right-click on your view and select "Design View". Right-click on the window again and select Properties from the context menu. Then put a check mark in the "Bind to Schema" option.

b. You cannot create an indexed view without schema binding.

SQL Server Helper
How well do you know SQL? Find out with the free test assessment from SQL Server Helper!!!
http://www.sql-server-helper.com/free-test/default.aspx

Got a SQL Server Question? Ask us here: http://www.sql-server-helper.com/forums/default.asp

prince500 · May 23rd, 2007, 12:02 PM

I am using SQL 2005 :

On my view I did right-click but I don't see "Design View" but there is anotner option "New View" should I select this?

I am not sure if I am doing something wrong??
Thanks

AA

aldwinenriquez · May 24th, 2007, 01:44 AM

Right click on the view and Script View as Alter to a new query window. then add this line of code

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[VW_ViewName] with schemabinding
AS

Make sure SET QUOTED_IDENTIFIER is ON

"Dont you ever give up!"

prince500 · May 24th, 2007, 06:53 AM

Thanks for advise - it worked :)

AA

telmessos · September 7th, 2011, 07:35 AM

Hi,

I couldn't understand properly. My view code is as shown below:

Code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[B2]
AS
 
"SQL QUERY"

GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

Shall I only change "ALTER VIEW [dbo].[B2]" section with "ALTER VIEW [dbo].[B2] with schemabinding"??

Many thanks
telmessos