Re: index

On Jul 29, 9:08 pm, "Brian Selzer" <br...@xxxxxxxxxxxxxxxxxxx> wrote:
"David BL" <davi...@xxxxxxxxxxxx> wrote in message

news:67fa389f-9429-4244-907c-b72e3784f423@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx





On Jul 29, 1:18 pm, "Brian Selzer" <br...@xxxxxxxxxxxxxxxxxxx> wrote:
"David BL" <davi...@xxxxxxxxxxxx> wrote in message

news:f08bba46-7bd1-443e-97fd-fe07a9ec1a3f@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

On Jul 29, 10:45 am, "Brian Selzer" <br...@xxxxxxxxxxxxxxxxxxx> wrote:
"David BL" <davi...@xxxxxxxxxxxx> 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?

.

Relevant Pages

  • Re: index
    ... index is a form of redundancy that hurts write performance. ... non-clustered index as with a clustered index. ... Covering the heading--that is, ...
    (comp.databases.theory)
  • Re: index
    ... the heading with a clustered index. ... I would define a clustered index as what you call a covering ... - based on your argument concerning write locks for updates. ... keys can be considered to be part of the content of the database. ...
    (comp.databases.theory)
  • Re: index
    ... index is a form of redundancy that hurts write performance. ... non-clustered index as with a clustered index. ...
    (comp.databases.theory)
  • Re: Index size question (DDL)
    ... > size which is why you see larger size for non-clustered index. ... The size of the first table's clustered index ... > The final value of 1 represents the index row header. ... >>David Walker ...
    (microsoft.public.sqlserver.server)
  • Re: Is this really the best execution plan SQL2K can find?
    ... leaf level of the non-clustered index consists of the non-clustered ... if the table has a clustered index this will be the ... So the leaf level consists of the columns Field3, ...
    (microsoft.public.sqlserver.programming)