Solved

Clustered v. a non-clustered index...

Posted on 2000-03-08
5
478 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
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:
bhess1 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

How to fix error ""Failed to validate the vCentre certificate. Either install or verify the certificate by using the vSphere Data Protection Configuration utility" when you are trying to connect to VDP instance from Vcenter.
The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…

706 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now