Solved

SQL 2005 How do you create a Index Over a View

Posted on 2010-09-08
7
320 Views
Last Modified: 2012-05-10
I can create a View in SQL 2005 by Choosing Views and then create a view.
Now if I want to create a index over that view where do I do that ?
Within my stored procedure ?
I don't want to recreate my view index everytime that stored procedure is called ?
And where is that view index stored in SQL 2005 ?
Looks like creating a index over a view is not as simplified as creating a index over a table.
0
Comment
Question by:thayduck
7 Comments
 
LVL 3

Expert Comment

by:Marbleman
ID: 33631183
As the name "view" states, your are just "viewing" data with it and not storing this selection somewhere.

Therefor you cannot add an index to a a view. I cannot think of a situation that would make this necessary...

However you could do something like that with "SELECT INTO newTable..." but this duplicates the data.
0
 
LVL 5

Accepted Solution

by:
David Christal CISSP earned 42 total points
ID: 33631242
You are correct, indexed views are not as simple as indexed tables.  Here is an article describing the ins and outs of creating indexes on views.  

http://technet.microsoft.com/en-us/library/cc917715.aspx#XSLTsection124121120120

Generally, they are not needed if the related tables are well indexed.  Per the article: "Frequently occurring aggregations and joins are the best candidates for indexed views."
0
 

Author Comment

by:thayduck
ID: 33631257
This IBM site talks about Indexed Views. Thats what got me thinking.

 http://technet.microsoft.com/en-us/library/cc917715.aspx
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 8

Expert Comment

by:mustaccio
ID: 33631273
There are some limitations as to what views can be indexed, but generally you indeed can create indexes over views. That essentially materializes the view results.

You create an index for a view with a regular CREATE VIEW statement. The first index must be unique and clustered.

You will find more details here: http://technet.microsoft.com/en-us/library/cc917715.aspx
0
 

Author Comment

by:thayduck
ID: 33631347
Sorry dchristal, sent answer before I read your response.
We saw same thing.

I have created 3 indexes over the table that I then created a view over.

I created the view because there are approx 8 flds in the table that could contain a $ amt and if they do then I include record into the report. If all flds are 0 then I ignore the record. I thought it would be better to create a view and only include records that have a $ amt in 1 of these 8 flds. But after I did that I realized I probably lost my index's  since they are over the table and not the view. Thats what made me curious about View Index's..

Will probably re think and put a better Where clause in my query.. and forget about the View..
0
 
LVL 24

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 42 total points
ID: 33631412
When you use a View you don't lose the indexes.  A view is meta-data and still knows about the views.  The only time you lose the power of indexes is if you use a (SELECT query) inside a select query, which creates a Virtual Table.  Then you lose the indexes.

So you should not forget about the view, it should be just fine to use it.  But you could materialize the view with SCHEMABINDING to make it into a psuedo table and only have those columns in the table.  Sometimes this is useful when you have a lot of columns in the table and you don't want to scan or go after the base table.
0
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 41 total points
ID: 33631816
Something to bear in mind, though, is that an Indexed View is, indeed, instantiated and changes to the underlying table will result in the recreation of that view.  If you have a lot of INSERTS, UPDATES, or DELETES impacting the underlying table, you can be adding a good bit more load on the database because each of them will trigger the INDEXED VIEW rebuild.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need help with a query 6 67
Grid querry results 41 72
Insert from Stored Procedure where some field/s > 0 7 43
Help with SQL - TOP 10 by date and by group 13 34
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now