Performance in case of View

[manishjoisar]

can creating views will reduce the performance??

Can Nested view can reduce performance

What to do to have Greater Performance using Views

SQL Server 2000
ASP.NET

Thanks

 

[amrita418]

Here are a few links to get you started:

http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part10/c3661.mspx

http://www.sqlteam.com/item.asp?ItemID=1015

Regards,
AA

 

[manishjoisar]

i have gone thru link, it is good.

I have nested index.
It mean if i want to particular view to be index then all the parent view to be WITH SCHEMABINDING

And if i have written SCRIPTS for Creating a DATABASE then it should be deleted in order

Waiting for your reply

Thanks for quick reply

 

[amrita418]

I am not sure what your question is. Do you want to find out the benefits of schemabinding?

Or are you looking for the syntax?

Regards,
AA

 

[manishjoisar]

Create View View_1
SELECT Field1,Field2 FROM Table1

CREATE View View_2 WITH SCHEMABINDING
SELECT Field1,Field2 FROM View_1

will not work as View_1 is not WITH SCHEMABINDING

It means all the PARENT VIEW to be WITH SCHEMABINDING
to CREATE Index on Views ??

Thanks

 

[amrita418]

AFAIK you are right, a view cannot be created with schemabinding if the parent is not schemabinding.

Let us take this step by step:
Creating view_1 with schemabinding option makes sure that the base table is not dropped / altered columns that might affect the view definition.

Creating view_2 with schemabinding option makes sure that the view_1 is not dropped / altered until view_2 is dropped / altered (remove schemabinding). Integrity is maintained till level 2.

On the other hand if you do not create view_1 with schemabinding there is no way for you to make sure the base table is not dropped / altered to affect the view_1 which in turn affects view_2 hence sql server does not allow you to create view_2 with schemabinding option.

Hope this helps
Regards,
AA

 

[amrita418]

Hit return too soon.
Finishing points:

All Indexed views must be created using schema binding and any user-defined functions referenced in the view must also be created with the SCHEMABINDING option.

For creating an indexed view on a table parent must be schemabinded too.

Regards,
AA

 

[manishjoisar]

Thank you very much for quick reply