Convert View into Tables in MSSQL 2008

Hello there,

I have taken some complex SQL query and converted them into View so I can us in my Java application. Now I want to know, if I convert these Views into Table will the report be faster and Can I in the first place create tables directly from those query instead of first creating a View and then Table.

cheers
Zolf
zolfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargBusiness Intelligence DeveloperCommented:
Hello,

Views can improve performance compare to queries.

After a unique clustered index is created on the view, the view's result set is materialized immediately and persisted in physical storage in the database, saving the overhead of performing this costly operation at execution time.

So all you need is to apply indexing on the view.
Indexed view is definitely faster than the query.

If you make table from the view you have to have index on that table for faster retrieval.
So its better to apply index on views for better performance.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
The advantages of the Views is that the data are dynamic and don't requires storage. You don't need to insert/update or delete to change data, since the data are already in the tables that are part of the View.
If you transform a View into a table, then you'll need to manage the new table, running the same operations that runs in the main tables. Can't see why you want to do that.

Not all Views can be materialized. Only deterministic Views can. Also, will require storage and if you need to change the View in the future will need to drop the index and recreate the View. But can be a solution instead of creating a new table.
0
zolfAuthor Commented:
Vitor Montalvão

Thanks for your feedbacks.

The advantages of the Views is that the data are dynamic and don't requires storage.
Do you mean if the data in those table from which I created the view will change the view will get those data updated automatically or I need to again run the create View to update those data.
CAn you please tell me how can I create index on the view I created.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Do you mean if the data in those table from which I created the view will change the view will get those data updated automatically
Yes. Everytime you run a SELECT * FROM ViewName it will return the actual data. If you transform a View into a Table then the data will be static unless you have a mechanism that will update the new Table.

CAn you please tell me how can I create index on the view I created.
Exactly the same way you do it for a table but the View need to be created by using the WITH SCHEMABINDING option.
Also don't forget that only deterministic Views can be indexed.
But before going for creating an indexed View, the tables that are in the View definition have the participating columns indexed?
0
zolfAuthor Commented:
thanks, can you please refer me to some good article where I can create index for View.

Also don't forget that only deterministic Views can be indexed.
Can you please elaborate on deterministic Views

Also how can I know if the tables column has index
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you please elaborate on deterministic Views
A View is deterministic if all columns used in the View (in SELECT, WHERE and GROUP BY clause) are deterministic. You can verify if a column is deterministic or not by running the following command:
SELECT COLUMNPROPERTY (OBJECT_ID( ‘TableNameHere‘ ), ‘ColumnNameHere‘, ‘IsDeterministic‘)
If returns 1 then is deterministic otherwise is not and then you can't index the View.

Also how can I know if the tables column has index
You need to check the table definition. You can run the following command for each table participating in the View definition:
sp_help 'TableNameHere'
Look for a line with INDEX_NAME on with.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zolfAuthor Commented:
Thanks guys
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.