473,322 Members | 1,409 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Don't 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?

Steve
Jul 20 '05 #1
1 2234
The 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" <st********@yahoo.com> wrote in message
news:5c*********************@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

Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Tryfon Gavriel | last post by:
Hi all I recently noticed when trying to optimise a major query of a chess website I am the webmaster of, that adding an order by for "gamenumber" which is a clustered index field as in for...
5
by: jim_geissman | last post by:
One table I manage has a clustered index, and it includes some varchar columns. When it is initially created, all the columns in the clustered index are populated, and then some of the longer...
6
by: bala | last post by:
hi guru's would appreciate if someone could show how to list all the clustered indexes in the database. if it can done as a output of single query it would be fine. the output should be the...
4
by: Dr Warehouse | last post by:
Hi, I am expanding our data warehouse solution with new filegroups on several subsystems. I want to know which idea is better! - create clustered indexes on tables to 'move' them to new...
2
by: Lyle Fairfield | last post by:
'Property Clustered As Boolean 'Member of DAO.Index Private Sub IsThereaClusteredIndex() Dim tdf As DAO.TableDef Dim idx As DAO.Index For Each tdf In DBEngine(0)(0).TableDefs For Each idx In...
1
by: Curt | last post by:
What is the difference please?
1
by: atul123 | last post by:
what is clustered and non clustered index and their types
0
by: tz71 | last post by:
If a new filegroup is created on a new disk drive, is there a way to migrate a specific clustered index to the new filegroup? SQL Server 2005 SP2 kb
1
by: David Portas | last post by:
"Erland Sommarskog" <esquel@sommarskog.sewrote in message news:Xns9B5AD2ADD1265Yazorman@127.0.0.1... Erland, I'm sure you mean that to be with love and care, rather than just "slap on" any...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.