Tuesday, March 27, 2012

do indexed views slow down inserts

sql2l sp3
Im starting to do a bit of research on indexed views. On a
normal table, a clustered index slows down inserts. Is the
same true for a clustered indexed view? Will it slow down
inserts for the underlying table?A clustered index does not have to slow down inserts. If you understand how
clustered indexes work and choose the appropriate column(s) for the index
expression it can actually speed it up and certainly can make a difference
on selects. Since an Indexed view uses a clustered index it can have the
same properties. That said an indexed view is always going to be slower on
inserts than a straight table simply because of the extra data your dealing
with. Each time you do an insert, update or delete on the underlying table
it potentially has to populate and recalculate the indexed views data. How
much depends on several factors such as the table size and definition and
the hardware setup etc. However these losses may be offset by the gains
that you can achieve on the selects against an indexed view. Again it
depends but in general they are not ideal for situations where you have lots
of inserts.
--
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:428701c47326$2badabd0$a601280a@.phx.gbl...
> sql2l sp3
> Im starting to do a bit of research on indexed views. On a
> normal table, a clustered index slows down inserts. Is the
> same true for a clustered indexed view? Will it slow down
> inserts for the underlying table?

No comments:

Post a Comment