April 13, 2012 at 9:06 am
I have an issue. We are dealing with a new system where they have thier schema(which is unbarable) due to the nature of its object intereface it creates tables like tblObject73345, tblObject7347,tblObject7347_1, with fields like Fldnumeric2234,Fldstring34354, and they only use nvarchar(255) and int, and datetime and use no index on huge transactional tables that will reach almost ten million, other then a clustered index on the indetinty column.
Now what i have to do is get data from some transactional tables and feed other systems, as well as create financial reports. So i figured, first to make it better for my team, i will make it easier on them and create a view of the table and alias every column as its actual field name in the system instead of fldstring34343 i say "as SchoolCode".
I also took it a step further and made them indexed views and schemabinded them, and created the same clustered indexed, and indexes where appropriate according to where i see all the table scans happening.
The issue is the third party I am going to have a conference call and get this work i have done approved. We where told we could created stored procedures, and views, but not to touch the schema or tables itself. There is a good article on Indexed Views by MSDN.
http://msdn.microsoft.com/en-us/library/aa933148(v=sql.80).aspx
If you scroll to the bottom this is what they say under considerations:
Consideration
After the clustered index is created, any connection attempting to modify the base data for the view must also have the same option settings required to create the index. SQL Server generates an error and rolls back any INSERT, UPDATE, or DELETE statement that will affect the result set of the view if the connection executing the statement does not have the proper option settings. For more information, see SET Options That Affect Results.
All indexes on a view are dropped if the view is dropped. All nonclustered indexes on the view are dropped if the clustered index is dropped. Nonclustered indexes can be dropped individually. Dropping the clustered index on the view removes the stored result set, and the optimizer returns to processing the view like a standard view.
Although only the columns that make up the clustered index key are specified in the CREATE UNIQUE CLUSTERED INDEX statement, the complete result set of the view is stored in the database. As in a clustered index on a base table, the B-tree structure of the clustered index contains only the key columns, but the data rows contain all of the columns in the view result set.
If you want to add indexes to views in an existing system, you must schema bind any view on which you want to place an index. You can:
•Drop the view and re-create it specifying WITH SCHEMABINDING.
•You can create a second view that has the same text as the existing view but a different name. The optimizer considers the indexes on the new view, even if it is not directly referenced in the FROM clause of queries.
Note Views or tables participating in a view created with the SCHEMABINDING clause cannot be dropped, unless the view is dropped or changed so that it no longer has schema binding. In addition, ALTER TABLE statements on tables that participate in views having schema binding will fail if these statements affect the view definition.
Now I willing to drop all my indexed views if they need to go threw any update of their system and recreate them.
I need firepower durring this meeting cause i need to use these views and not work backwards. If they say no this may interfere with out system they better tell me exactly why.
Cause thier normal process is they wait for something to perform slowly and its sent to their team that takes weeks to add a fiew indexes that any idiot would know where to add, and charge us on top of that.
When we have capable senior db engineers here that can do this work. But we also do not want to loose "support" if something breaks because of a totally unrelated issue to index views, just because we created them.
So my quesions are:
What are the effects indexed views actually have on the base table. They are for our use only?
What troubles do they cause?
Has anyone come accross an issues like this with a vendor?
Thank you for your response and feedback.
Michael Evanchik
April 13, 2012 at 9:51 am
The two main drawbacks to indexed views are the limits caused by schema-binding, and the fact that they do slow down insert/update/delete operations. They also require the usual maintenance actions for any sort of index (use monitoring, defragmentation, rebuilding, et al).
You'll want to make sure they don't violate any support contracts with the company responsible for the database. If they do, you won't be able to use the views, since breaking the contract will probably be worse than keeping it.
On the other hand, regular views, in a different database, can be used to work around that kind of support contract, and will still at least allow you to alias columns and "tables" for queries. Won't have the indexed options, but indexes applied to the underlying tables are usually just as good.
The main advantage to idexed views is that you can have indexes on things like aggregates, or that stretch across multiple normalized tables, or that filter the rows in the view pre-query (though you can get that with filtered indexes on SQL 2008 and later). So, if you're just writing an API for the table, with column aliases, you don't need to use indexed views, and regular ones will work just fine.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 16, 2012 at 8:35 am
Thank you for you feedback! I did not know they slowed down updates and inserts. Hope they dont bring that up.
Although aliasing with a view would make things nice. The performance would be horrible and would have to resort back to tbl10012_55 and fldstring748232 schema and know what to join on ughhh.
anyone else have any feedback?
April 16, 2012 at 8:56 am
Also bear in mind that indexed views are actually materialised and stored. So if you create indexed views across an entire DB, you are doubling the size of the DB at the very least.
Based on what you said, I probably would not suggest indexed views. Normal views, one per table, with sensible names are fine and will have minimal to no performance impact.
Can you not get permission to add indexes to the base tables? Vendors will sometimes allow that as indexes can't break an application.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 16, 2012 at 8:58 am
xgcmcbain (4/16/2012)
Thank you for you feedback! I did not know they slowed down updates and inserts. Hope they dont bring that up.
If you are suggesting indexed views, it is your responsibility to point out any disadvantages. Otherwise you could land up in all sorts of trouble if there is an effect when you neglected to mention that it was a possibility.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply