SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Indexed Views


Indexed Views

Author
Message
ss-457805
ss-457805
SSC Eights!
SSC Eights! (805 reputation)SSC Eights! (805 reputation)SSC Eights! (805 reputation)SSC Eights! (805 reputation)SSC Eights! (805 reputation)SSC Eights! (805 reputation)SSC Eights! (805 reputation)SSC Eights! (805 reputation)

Group: General Forum Members
Points: 805 Visits: 1904
Comments posted to this topic are about the item Indexed Views

blog: http://sarveshsingh.com

Twitter: @sarveshsing
jts2013
jts2013
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1354 Visits: 5009
A good introductory article, but I think people need to be careful when looking into using Indexed Views.

I recently added a view to a 3rd party application and thought it would be sensible to make it an Indexed View for performance reasons, so I created the view with the SCHEMABINDING option. However, the 3rd party application that updated the underlying base table stopped working and we had to remove SCHEMABINDING and the index from the view. I was told that SCHEMABINDING had made the base table read only, but I don't think this is the correct explanation. BOL didn't seem to suggest this could happen either.
sknox
sknox
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3024 Visits: 2852
jts_2003 (11/16/2010)
A good introductory article, but I think people need to be careful when looking into using Indexed Views.

I recently added a view to a 3rd party application and thought it would be sensible to make it an Indexed View for performance reasons, so I created the view with the SCHEMABINDING option. However, the 3rd party application that updated the underlying base table stopped working and we had to remove SCHEMABINDING and the index from the view. I was told that SCHEMABINDING had made the base table read only, but I don't think this is the correct explanation. BOL didn't seem to suggest this could happen either.


SCHEMABINDING doesn't make the table data read-only, but it does prevent modification of the table structure if that would affect the view. So if the application is for whatever reason modifying the structure of the base table during normal processing, SCHEMABINDING can be a problem.

http://msdn.microsoft.com/en-us/library/ms187956.aspx
ps.
ps.
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4039 Visits: 3668
One of the applications that i was recently looking at had 32 indexes on the indexed view(1 clustered, 31 non clustered) Hehe.
Last time i pulled report, it had 32000+ inserts where as only some of the indexes were actually getting used for reading. It was real bad setup. Though i agree, indexed views will increase read performance, however we must also consider the amount of writes happening to the base table.



Pradeep Singh
sixthzenz
sixthzenz
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 348
If you have a table (let's say an Employee table) that has an index on one or more columns (let's say the Employee ID), is there any reason why you'd have an index tied to the same field(s) on a view that, for the most part, mirrors that base Employee table? If my applications are using views, should the index be only on the view and not the table? Unless you're building a special view, I've found it to be more beneficial to put indexes on the base tables in this context.
Lisa Slater Nicholls
Lisa Slater Nicholls
Mr or Mrs. 500
Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)

Group: General Forum Members
Points: 551 Visits: 618

I recently added a view to a 3rd party application and thought it would be sensible to make it an Indexed View for performance reasons, so I created the view with the SCHEMABINDING option. However, the 3rd party application that updated the underlying base table stopped working and we had to remove SCHEMABINDING and the index from the view. I was told that SCHEMABINDING had made the base table read only, but I don't think this is the correct explanation. BOL didn't seem to suggest this could happen either.


I had a similar situation with a third party application -- when I looked at it closely, it turned out to be related to SET QUOTED IDENTIFIERS. This is not something you can fix at runtime, it has to do with the way their sprocs were saved/parsed.

You might want to check this out.

In my case, the vendor realized that their scripting process for writing upgrade code had left out the appropriate statement and they will eventually fix it. But first they had to understand/notice it <shrug>.

They also told me that they frown on schema-bound views because "what if they want to change their schema". There are two interesting things about this, for the purposes of our discussion:

*1 -- They also frown on our creating additional indexes on the base tables, and they don't create any. They also try to discourage any interop/integration efforts, for the same reasons. So this, globally considered, is a ridiculous attitude, IMHO. Beyond the obvious optimization reasons to do this, our dealing with the schema as it currently exists, in some manner -- whether for a report or other custom output, or interop with another enterprise app -- is not something that can be avoided because a vendor tells you it's going to cause an issue. No matter how many layers of abstraction you put on top of the base tables, if they change schema it's on your head to re-build your custom indexes, or whatever it takes. You can't just "not do it".

*2 -- I am really wondering if this is what your vendor meant by telling you the "base table was readonly". IOW, maybe somebody mis-repeated something to you, what they meant was "if you create a schema-bound view, it hampers our code because the structure of the table is readonly (not the data). For example, it's possible that they dynamically create and drop flag fields, or that the person who made the original statement was thinking long term, "the schema will be readonly when we upgrade".

Anyway, this may not be involved, but I thought I'd share.

>L<
David Rueter
David Rueter
Old Hand
Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)

Group: General Forum Members
Points: 356 Visits: 574
Indexed views provide the most value when you are indexing on columns from multiple tables that are joined in the view--particularly if all of the columns you need to access can be stored in the index.

If all of the index segment columns are in a single base table, or even if the columns are clustered indexes in the base tables, you are generally better off putting the index on the base table.

Even in cases where there are multiple joined tables involved, indexed views do not always increase performance enough to warrant the write cost, inconvenience, and storage overhead.

My rule of thumb is to avoid indexed views unless there is a compelling benefit for a particular scenario...and sometimes there is a compelling benefit.
getoffmyfoot
getoffmyfoot
SSC-Addicted
SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)

Group: General Forum Members
Points: 482 Visits: 412
Eh? No mention of the huge pitfall of indexed views?
If there is some sort of math in the view that could generate an ARITHABORT error, an indexed view has the capability to break inserts to the underlying tables. We tried this in our environment and found out about this the hard way. Microsoft points out the issue here - http://support.microsoft.com/kb/305333
I'd never make a view an indexed view if any sort of math is involved in the view unless the math has been carefully, carefully protected from arithabort errors.

In short, test it in a test db before you index your production views :-)

Edit - spelling error
David.Poole
David.Poole
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10132 Visits: 3341
Another easy mistake to make is to put a clustered index on the view that conflicts with the clustered index in the base tables. I now hold the world record for the shortest time to create a deadlock.

At least I got the chance to see some genuine output from the deadlock trace in SQL Profiles:-P

LinkedIn Profile

Newbie on www.simple-talk.com
asiraky
asiraky
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 13
indexed views have lots of drawbacks (i.e no left joins, subqueries etc etc) but in a highly normalised setup they are priceless when used correctly, and can solve some real performance issues. i couldnt live without them.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search