Excessive Non-Clustered Index Use?

I recently started at a new company as their DBA. In looking at their
databases they have indexes that are all non-clustered (including
PK's). There is rarely a clustered index on any of the tables. Can
anyone tell me why you would want to use exclusively non-clustered
indexes on tables? The largest table is >30M records. In my research
you want a clustered index on a table because the non-clustered use
the clustered index.

When I look at the execution plans on these tables table-scans are
constantly done and the non-clustered indexes are not used.

Thoughts? Suggestions?

C-Note ( ) writes:
> I recently started at a new company as their DBA. In looking at their
> databases they have indexes that are all non-clustered (including
> PK's). There is rarely a clustered index on any of the tables. Can
> anyone tell me why you would want to use exclusively non-clustered
> indexes on tables? The largest table is >30M records. In my research
> you want a clustered index on a table because the non-clustered use
> the clustered index.
>
> When I look at the execution plans on these tables table-scans are
> constantly done and the non-clustered indexes are not used.

There are people who believe heaps are the norm, and clsutered indexes
should be avoided. And, indeed, from what I have heard, this is also the
norm on Oracle.

But SQL Server is a different engine, and SQL Server is much more geard
towards clustered indexes, and it does not do a very good job with heaps.
They are more prone to fragmentation, but they are less conventient to
defragment. And while you can get plenty of table scans with clustered
indexes as well, there is at least some probability that the index
organisation can be useful.

The main argument I've heard against clustered indexes, is that since
the CI is used as row locator, a key lookup is more expensive than a
RID lookup. Personally, I don't care too much for that argument. I
think you will have to tune many queries, before any such overhead
is starting to become a concern.



--
Erland Sommarskog, SQL Server MVP, esquel.RemoveThis@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx