Dont use clustered indexes?

Hi,

The more I read, the more confused I'm getting ! (no wonder they say
ignorance is bliss)

I just got back from the bookstore and was flipping through some SQL Server
Administration books.

One says, that to get the best query performance, youi do two things:

1. Cover all the columns used in each SELECT (including the WHERE, ORDER
BY , etc.) with an index

2. Make sure it's a NON-CLUSTERED index.

In this way, the author says, you avoid ever going directly to the base
tables for data to resolve the query - i.e. it's resolved in the index.

So, for example, he argues if you have:

SELECT Lname,Fname, CompanyName
from Contacts
inner join Customers
on (contacts.custid = customers.custid)

that you use two non-clustered indexes:
1. Lname,Fname and custid from the Contacts table
2. CompanyName and custid from Customers

(as opposed to the standard approach of a clustered index on the PK's of
each table)

He says that clustered indexes don't speed up performance because they're
the same as a full table scan. Should I drop clustered indexes from my
large tables, given that there are multiple non-clustered indexes on them?
Is it better to just use multiple non-clustered indexes on a heap table?

SteveThe best indexing strategy depends on a number of factors. It is true that
a covering non-clustered index will be beneficial to some queries,
especially when only a few columns are selected and most of the rows in the
table are needed. However, you need to balance the cost of maintaining the
index with the benefits of using it. Too many indexes can slow down
insert/update performance and increase the likelihood of blocking and
deadlocks. It's overkill to create a lot of non-clustered indexes to cover
queries unless the database is read-only, the additional disk space
requirements aren't a concern and you can anticipate the queries beforehand.

Personally, I rarely create heap tables in SQL 7 and above. The clustered
index eliminates the I/O overhead of maintenance to non-clustered leaf nodes
due to page splits because the clustered key rather than physical location
is used to as the bookmark to data rows. See the Books Online
<architec.chm::/8_ar_da2_8sit.htm> for a discussion on clustered and
non-clustered indexes.

Covering non-clustered indexes are appropriate to address specific
performance issues but, IMHO, are the exception rather than the rule.
Exercising a little common sense in creating useful indexes goes a long way
in preventing performance problems.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Steve_CA" <steveee_ca@.yahoo.com> wrote in message
news:5cccd.67695$3C6.2513201@.news20.bellglobal.com ...
> Hi,
> The more I read, the more confused I'm getting ! (no wonder they say
> ignorance is bliss)
> I just got back from the bookstore and was flipping through some SQL
> Server Administration books.
> One says, that to get the best query performance, youi do two things:
> 1. Cover all the columns used in each SELECT (including the WHERE, ORDER
> BY , etc.) with an index
> 2. Make sure it's a NON-CLUSTERED index.
> In this way, the author says, you avoid ever going directly to the base
> tables for data to resolve the query - i.e. it's resolved in the index.
> So, for example, he argues if you have:
> SELECT Lname,Fname, CompanyName
> from Contacts
> inner join Customers
> on (contacts.custid = customers.custid)
> that you use two non-clustered indexes:
> 1. Lname,Fname and custid from the Contacts table
> 2. CompanyName and custid from Customers
> (as opposed to the standard approach of a clustered index on the PK's of
> each table)
> He says that clustered indexes don't speed up performance because they're
> the same as a full table scan. Should I drop clustered indexes from my
> large tables, given that there are multiple non-clustered indexes on them?
> Is it better to just use multiple non-clustered indexes on a heap table?
> Steve