Re: index

On Jul 29, 9:08 pm, "Brian Selzer" <br..._at_selzer-software.com> wrote:
> "David BL" <davi..._at_iinet.net.au> wrote in message
>
> news:67fa389f-9429-4244-907c-b72e3784f423_at_m44g2000hsc.googlegroups.com...
>
>
>
>
>
> > On Jul 29, 1:18 pm, "Brian Selzer" <br..._at_selzer-software.com> wrote:
> > > "David BL" <davi..._at_iinet.net.au> wrote in message
>
> > >news:f08bba46-7bd1-443e-97fd-fe07a9ec1a3f_at_25g2000hsx.googlegroups.com...
>
> > > > On Jul 29, 10:45 am, "Brian Selzer" <br..._at_selzer-software.com> wrote:
> > > > > "David BL" <davi..._at_iinet.net.au> wrote in message
> > > > > > Physical duplication of data can be appropriate to increase read
> > > > > > performance at the expense of write performance. Indeed any
> > > > > > secondary
> > > > > > index is a form of redundancy that hurts write performance.
>
> > > > > Boosting read performance can be accomplished just as well with a
> > > > > covering
> > > > > non-clustered index as with a clustered index.
>
> > > > That is not always true. There could be an application involving a
> > > > query that uses the non-clustered index and also needs *all* the
> > > > additional data in the record. The additional seeks could mean the
> > > > read performance doesn’t meet the requirements.
>
> > > If the index is a /covering/ index, then there is no need for the
> > > additional
> > > read.
>
> > But then it won’t be a non-clustered index.
>
> Yes it would. The additional read and an exclusive lock /would/ be required
> whenever there is an update.

> You appear to be equating an index that covers
> the heading with a clustered index. Covering the heading--that is,
> containing all of the columns in the heading--is not what makes an index a
> clustered index.

Yes, I would define a clustered index as what you call a covering index. Evidently you want to distinguish a "primary" copy of the data - based on your argument concerning write locks for updates. However, it seems odd to me that the definition of a clustered index would be concerned with where locks are located. In fact a lock manager will invariably dynamically allocate locks (only) when they are needed, indexed in transient data structures (such as hash tables) in system memory. Where is the need to distinguish a primary copy of the data? Received on Tue Jul 29 2008 - 16:50:51 CEST