July 20, 2010 at 1:20 pm
How do I add indexes to already established views?
Thanks
July 20, 2010 at 2:09 pm
Marv-1058651 (7/20/2010)
How do I add indexes to already established views?
create index command? 🙂
Please check here for details... http://msdn.microsoft.com/en-us/library/aa258260(SQL.80).aspx
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 21, 2010 at 7:09 am
Just understand, you're not adding indexes to a view. A view is nothing but a query. You're creating a new object, a materialized view, that is working within a set of rules as outlined in the link above. Best to go into it with that understanding to avoid disappointment at the reality of the situation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 22, 2010 at 2:45 pm
Thanks for your advice.
I tried that SQL on a view and i recieved the error below:
"Cannot create index on view '[ViewName]' because the view is not schema bound"
July 22, 2010 at 3:02 pm
Marv-1058651 (7/22/2010)
Thanks for your advice.I tried that SQL on a view and i recieved the error below:
"Cannot create index on view '[ViewName]' because the view is not schema bound"
To create an index on a view, the view must be tied (schema bound) to the base table. To do this, use:
CREATE VIEW [dbo].[vw_sampleView] WITH SCHEMABINDING AS
SELECT...
...for a new view, or:
ALTER VIEW [dbo].[vw_sampleView] WITH SCHEMABINDING AS
SELECT...
...for an existing view. This effectively binds the view to the table so that the base table cannot be modified in any way that would break the view.
http://www.mssqltips.com/tip.asp?tip=1610
_________________________________
seth delconte
http://sqlkeys.com
July 23, 2010 at 6:02 am
So, let me see if I understand this:
If you have to use schemabinding for a materialized view, and the view has to have a clustered index, when you create an indexed view you're essentially creating another table with triggers that update/insert/delete from the base table(s)?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
July 23, 2010 at 6:56 am
jcrawf02 (7/23/2010)
So, let me see if I understand this:If you have to use schemabinding for a materialized view, and the view has to have a clustered index, when you create an indexed view you're essentially creating another table with triggers that update/insert/delete from the base table(s)?
Concept is sound.
An "indexed view" a.k.a. materialized view is nothing but a table SQL Server engine will keep updated as base table gets updated.
The beauty of materialized views - this part of the concept works for both SQL Server and Oracle - is that query doesn't have to reference the MV, if query references the base table and optimizer finds out performance will be better hitting the MV, it will hit the MV instead of the base table.
Hope this clarifies.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 23, 2010 at 7:07 am
PaulB-TheOneAndOnly (7/23/2010)
jcrawf02 (7/23/2010)
So, let me see if I understand this:If you have to use schemabinding for a materialized view, and the view has to have a clustered index, when you create an indexed view you're essentially creating another table with triggers that update/insert/delete from the base table(s)?
Concept is sound.
An "indexed view" a.k.a. materialized view is nothing but a table SQL Server engine will keep updated as base table gets updated.
The beauty of materialized views - this part of the concept works for both SQL Server and Oracle - is that query doesn't have to reference the MV, if query references the base table and optimizer finds out performance will be better hitting the MV, it will hit the MV instead of the base table.
Hope this clarifies.
It does, thanks Paul!
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
August 10, 2010 at 8:41 pm
jcrawf02 (7/23/2010)
If you have to use schemabinding for a materialized view, and the view has to have a clustered index, when you create an indexed view you're essentially creating another table with triggers that update/insert/delete from the base table(s)?
The clustered index also has to be unique, and there aren't any triggers involved, but aside from that...:-)
When a base table is referenced by one or more indexed views, any DML changes to the base table are propagated to the view by extra query plan operators in the 'write cursor' side of the plan that updates the base table. So, the operations necessary to maintain the view happen at the 'same time' - in the same query plan - as the base table changes.
Triggers, by contrast, use versioned rows in a system version store held in tempdb, and either replace the DML operation, or run in a separate scope immediately afterward.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply