thayduck
asked on
SQL 2005 How do you create a Index Over a View
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This IBM site talks about Indexed Views. Thats what got me thinking.
http://technet.microsoft.com/en-us/library/cc917715.aspx
http://technet.microsoft.com/en-us/library/cc917715.aspx
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
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
ASKER
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..
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..
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.