Re: Clustered Index

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 04/14/04 

Date: Wed, 14 Apr 2004 14:17:10 -0700

Hi Jeff

I'm sure you didn't mean to say 'multiple clustered indexes'.

However, I'm not sure you didn't mean the last sentence. This is definitely
not a universal truth. A clustered index can retrieve duplicate data much
more quickly than a nonclustered, for example if you want everyone in the
same zip code. WIth a clustered index on zipcode, all the relevant rows will
be together. A nonclustered works well for unique data where you're
retrieving a single row, so why not use a nc index there, and save your
clustered for where it will do more good and a nc won't help? 

--
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Jeff Duncan" <jduncan@gtefcu.org> wrote in message
news:3855777C-E84F-4FB7-AC62-95F1C7C22A9C@microsoft.com...
> Pablo
>
> You can only have 1 clustered index on any given table.  seeing as how it
is Clustered and ordered only by that column.  You cannot order it another
physical way also.
> You can have up to 16 columns specified in the index.
> and Yes you can have multiple clustered indexes and only 1 clustered.
>
> You normally try and make the Clustered index out of the most unique
column in the database.  Usually a Primary Key field.  Then you can build
the nonclustered indexes.
>
> Jeff

Relevant Pages

  • 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)
  • Re: index
    ... have only one clustered index per table ... which of course incurs a cost. ... multiple clustered indexes. ...
    (comp.databases.theory)
  • Re: index
    ... we can have several non clustered indexes on a table, ... have only one clustered index per table ... one can have multiple clustered indexes at the cost of ... which of course incurs a cost. ...
    (comp.databases.theory)