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


Indexed Views


Indexed Views

Author
Message
ss-457805
ss-457805
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: 4003 Visits: 2126
Comments posted to this topic are about the item Indexed Views

blog: http://sarveshsingh.com

Twitter: @sarveshsing
jts2013
jts2013
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2160 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
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8293 Visits: 3252
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.
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18678 Visits: 3674
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
Mr or Mrs. 500
Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)

Group: General Forum Members
Points: 500 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
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2255 Visits: 644

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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1647 Visits: 580
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
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2084 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
Dave Poole
Dave Poole
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42272 Visits: 3857
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
www.simple-talk.com
asiraky
asiraky
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 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