Solved

Clustered v. a non-clustered index...

Posted on 2000-03-08
5
502 Views
Last Modified: 2013-11-15
When would one use a clustered v. a non-clustered index when using Sql Server ?
0
Comment
Question by:bhavesh55
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 2

Expert Comment

by:damienm
ID: 2596764
Clustered indexes tend to be I/O faster for a single row lookup but if you are using multiple row lookup you will need to have non-clustered index.  

You can have only 1 clustered index per table.
0
 
LVL 1

Expert Comment

by:prozak
ID: 2596899
I think that damienm has it backwards. Clustered indexes are optimized for multiple rows that have the same index value.

An example would be a one-to-many relationship. The child table should have a clustered index on the parent foreign key because typically you will retrieve all of the child records for the parent record at the same. By using a clustered index on the foreign key, child records with the same foreign key will be stored together on the disk.

If you will always only retrieve one record at a time from a table then you will want to have no clustered indexes because this will affect INSERT and UPDATE performance because the server will have to shuffle things around on the disk in order to place the new record on the same page(s) as the records with the same clustered index values.
0
 

Expert Comment

by:imackey
ID: 2596951
A Clustered index will physically store records in order specified by the clustered index.  When you plan to commonly process records in sequence, the use of a clustered index can help.  If you think of a payroll application that is sorted on a unique employee id that will process a table in that sequence, then there can be less jumping around of the read buffers, etc.  It can offer speed advantages for applications without identical index values either.

It follows from idea of physical storage being dictated by a clustered index that you can only have 1 per table.  
0
 
LVL 32

Accepted Solution

by:
Brendt Hess earned 20 total points
ID: 2597004
Clustered indexes control the order of data stored in your SQL Server tables.  This can allow for some significant speed gains, but can also cause some problems.

Don't use a clustered index if you will be doing frequent inserts into the middle of the range controlled by the index.  For example, an index on Last_Name, First_Name in your Customer Table would be a bad index to cluster, even if you always looked up entries by name.

Why?  Well, assume you have 10,000 entries in your CustTbl, and you want to enter a new customer named Aaron Aab.  Since this ends up being the first record in your table, all 10,000 other records must be physically moved down one space to allow for the addition of the new record!

On the other hand, If every new customer was issued a sequential Customer Number, you could have a clustered index on that.  Any new additions would be added to the end of your file, causing no additional overhead.

However, having a clustered index on customer # doesn't make particular sense unless you look up groups of customers by number, or you use the customer # for looking up your customer record fairly frequently.  You gain little advantage if you aren't using that index fairly frequently for look-ups, although it will not hurt at all to have an index like this, and some lookups will probably be faster.

A clustered index makes good sense for a file that logs activity by date and time.  For example, if you recorded when workers checked in and out, an index by Activity_Date and Activity_Time would allow rapid access to all of a day's activity.

For any other case, use a non-clustered index.

0
 
LVL 1

Expert Comment

by:prozak
ID: 2597052
Gee is there an echo in here ...
0

Featured Post

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question