I know that a clustered index is really useful if you actually use it, but are
there any disadvantages of not having a clustered index on a table if that
index isn't used in the query?

If I have a clustered index on column a, are there any advantages to its
existence for a query that uses an index on column b?

In general, is not good having an index on a table if you are not going to
use it.

Personally, for most apps, I prefer having a clustered index over a
non-clustered index. All of my tables have a clustered index, usually a
unique clustered index. In this way, the apps which read the table do not
have to do a double disk search because the table data are the leaf nodes of
the clustered index. In other words, your searches are twice as fast.

Also, you will save disk space. For example, if the index is on most of
the columns in the table, a non-clustered index would increase your total
table data by 100%.




Brian
http://www.dbpowersuite.com




"Thomas Gagne" <tgagne (AT) wide-open-west (DOT) com> wrote

Brian Ceccarelli wrote:

That's overstating it. With a non-clustered index, the
index leaf page has a pointer to the data page, so ONE
more read is required. So if the height of your B-Tree
is four, then there are four I/Os with a clustered index,
and five with a non-clustered index.

The big performance advantage to clustered indexes (to
answer Thomas' question) isn't with single-row lookups,
it's with range searches and order-by. For example, if
you're selecting all addresses where state = "CA" and
you have a clustered index on state, then the rows that
meet your search criteria are all close toghether on the
disk. If your table has 10 rows per page, and 10 rows
match your search criteria, then all 10 rows might be on
one or two physical pages, requiring one or two data
page I/Os. Without a clustered index, the 10 matching
rows could be spread out anywhere, so you're likely
looking at 10 data page I/Os to read the same data.

And if you have a clustered index that matches your order
by clause, the data are already laid out in the right
order, again minimizing I/O by making it sequential
rather than random.

That's true if the index is on ALL of the columns of the
table (actually, a little more than 100%, given the
overhead for the root node and branch nodes). But since
the entire tuple usually isn't used for a composite index,
the penalty usually isn't that great in practice. But it
is true that with a non-clustered index, you have to store
the same data twice -- once in the data pages, and again
in the index leaf pages.


Larry Coon
University of California

Well-stated Larry.

Brian



"Larry Coon" <lmcoon (AT) nospam_cox (DOT) net> wrote