SQL Server - Clustered vs Non-clustered

[AppDeveloper-Sp9Jq0]

Hi,

When should I use clustered index versus non-clustered index?

Thanks in advance!

[Newsgroup_User]

AppDeveloper wrote:
> Hi,
>
> When should I use clustered index versus non-clustered index?
>
> Thanks in advance!

IIRC Non-Cluster Indexes are used when the values in the index field(s)
are unique across all records.

Clustered Indexes are when the values are *not* unique and thus come in
'clusters' of values.

[AppDeveloper-Sp9Jq0]

Hi lan,

Then how come some of the foreign key columns in my company's existing database are storing non-unique values (duplicate values exist) and they (these foreign key columns) are non-cluster indexed?

Based on SQL Server Books Online, their definition is as follows, but I'm just not exactly sure when (in what situation(s)) to use non-cluster and when to use cluster.

Clustered: Creates an object where the physical order of rows is the same as the indexed order of the rows...

Non-clustered: Creates an object that specifies the logical ordering of a table. With a nonclustered index, the physical order of the rows is independent of their indexed order.

Thanks in advance again.

[Newsgroup_User]

AppDeveloper wrote:
> Based on SQL Server Books Online, their definition is as follows, but I'm just
> not exactly sure when (in what situation(s)) to use non-cluster and when to use
> cluster.

O.K. Obviously I did not Recall Correctly!

>
> Clustered: Creates an object where the physical order of rows is the same as
> the indexed order of the rows...
>
> Non-clustered: Creates an object that specifies the logical ordering of a
> table. With a nonclustered index, the physical order of the rows is independent
> of their indexed order.

Using that definition it is a case of how is the data in the tables. If
the data is stored on the disk drive in the order of the index, then you
can use a clustered index.

Otherwise use a non-clustered.

This is something I have never had a large enough database to worry
about using anything other then the non-clustered.

[JR__Bob__Dobbs-qSBHQ2 · Accepted solution]

AppDeveloper,

You should use a clustered index when the index will be used in the majority of the SELECT statements run to query the table. Use non-clustered for less frequently used indexes.

Disclaimer: Your mileage may vary and this is just my rule of thumb.

Question: What version of SQL server are you using?

[JR__Bob__Dobbs-qSBHQ2]

You might try using the Database Engine Tuning Advisor tool included with SQL 2005, this can help you identify what indexes might be needed in your database.
http://msdn.microsoft.com/en-us/library/ms188639(SQL.90).aspx

Designing Indexes
http://msdn.microsoft.com/en-us/library/ms190804(SQL.90).aspx

[AppDeveloper-Sp9Jq0]

Many thanks to both of you.