?
Solved

Clustered v. a non-clustered index...

Posted on 2000-03-08
5
Medium Priority
?
508 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 80 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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

Are you looking to recover an email message or a contact you just deleted mistakenly? Or you are searching for a contact that you erased from your MS Outlook ‘Contacts’ folder and now realized that it was important.
In this article we will learn how to backup a VMware farm using Nakivo Backup & Replication. In this tutorial we will install the software on a Windows 2012 R2 Server.
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…
Suggested Courses

770 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