Re: index

On Jul 29, 4:24 am, "Brian Selzer" <br...@xxxxxxxxxxxxxxxxxxx> wrote:
"Bob Badour" <bbad...@xxxxxxxxxxxxxxxx> wrote in message

news:488de63f$0$4041$9a566e8b@xxxxxxxxxxxxxxxxxx





aark...@xxxxxxxxx wrote:

Hi all,

I read the following in a book

1) we can have several non clustered indexes on a table , but we can
have only one clustered index per table

2) index on the primary key can be clustered or non clustered

now my question is

1) to what extent these statements are true ?

To the extent the statements accurately describe the physical
implementation of a particular dbms, they are true.

Theoretically, one can have multiple clustered indexes at the cost of
duplicating the data, which of course incurs a cost. Vendors typically
assUme nobody would ever want to incur that cost.


Where there is a clustered index, that
index /is/ the table--that is, the table is physically stored using whatever
data structure is in use for indexes--probably b-trees. A table that does
not have a clustered index, on the other hand, is physically stored as a
heap. The leaf nodes of a clustered index /are/ the rows of the table. The
leaf nodes of a non-clustered index are either pointers to rows on the heap
(where there is no clustered index) or clustered index keys (along with a
uniquifier [I didn't coin the term, so don't blame me] if the clustered
index isn't also a unique index).

Agreed

Although it is possible to have multiple
indexes that cover the entire heading, that is not the same thing as having
multiple clustered indexes.

Why?

As you said a non-clustered index is characterised by some indirection
to the actual rows that are stored elsewhere. If we physically store
multiple indexes and each index records all the data in the table in
the manner of a clustered index, then don’t we by definition have
multiple clustered indexes?

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.

In specialised applications there could be considerable merit in
multiple clustered indexes.

.

Relevant Pages

  • Re: Clustered Index
    ... I'm sure you didn't mean to say 'multiple clustered indexes'. ... WIth a clustered index on zipcode, ... A nonclustered works well for unique data where you're ... "Jeff Duncan" wrote in message ...
    (microsoft.public.sqlserver.server)
  • Re: index
    ... have only one clustered index per table ... Theoretically, one can have multiple clustered indexes at the cost of duplicating the data, which of course incurs a cost. ...
    (comp.databases.theory)
  • Re: index
    ... we can have several non clustered indexes on a table, ... one can have multiple clustered indexes at the cost of ... which of course incurs a cost. ... A table that does not have a clustered index, ...
    (comp.databases.theory)
  • Re: index
    ... Theoretically, one can have multiple clustered indexes at the cost of duplicating the data, which of course incurs a cost. ... A table that does not have a clustered index, on the other hand, is physically stored as a heap. ... Although it is possible to have multiple indexes that cover the entire heading, that is not the same thing as having multiple clustered indexes. ...
    (comp.databases.theory)
  • Re: index
    ... have only one clustered index per table ... which of course incurs a cost. ... multiple clustered indexes. ...
    (comp.databases.theory)