This is what I know regarding clustered index in MS SQL Server.

A cluster index in SQL Server is an IOT (Index-Organized Table) in Oracle.
Now this is what you need to know:

Clustered indexes are useful for:

1. Primary keys.

2. Columns that are not updated.

3. Queries that return a range of values, using operators such as BETWEEN,
>, >=, <, and <=
Here's an Example:
SELECT * FROM emp WHERE sal >= 5000

4. Queries that return large result sets, such as:

SELECT * FROM emp WHERE ename = 'Michael'

5. Columns that are used in sort operations (ORDER BY, GROUP BY).


6. Distributing update activity in a table to avoid hot spots. Hot spots are
often caused by multiple users inserting into a table with an ascending key.
This application scenario is usually addressed by row-level locking.

Note:
To reorganize your table in SQL Server, drop and re-create your clustered
index(data pages will became contiguous on disk,
you'd re-gain some free space in the table).
In Oracle you would export, drop and re-import the table.

Important:
A SQL Server clustered index is not at all like an Oracle cluster. An Oracle
cluster is a physical grouping of two or more tables that share the same
data blocks and use common columns as a cluster key. SQL Server does not
have a structure similar to an Oracle cluster.


If you define a clustered index on a table, this will improve SQL Server
performance and space management.
Now if you do not know the query or update patterns for a given table, you
can create the clustered index on the primary key.

Regis

-----Original Message-----
From: Shailesh Paliwal [mailto:[Email Address Removed] Monday, December 30, 2002 1:20 PM
To: LazyDBA.com Discussion
Subject: Re: Sql server 2000 data volume problem


Hi Regis,

But i rad somewhere if clustered index is not on incrementive key (o/p of a
sequence) it will slow down further inserts.I want to know will it a
significant slow in inserts.

Thanks,

Sheilesh

----- Original Message -----
From: "Regis Biassala" <Regis.[Email Address Removed] "Shailesh Paliwal" <[Email Address Removed] Monday, December 30, 2002 6:32 PM
Subject: RE: Sql server 2000 data volume problem


> a CLUSTERED INDEX is the way to go....it reduces space and improves
> performance...
>
>
> -----Original Message-----
> From: Shailesh Paliwal [mailto:[Email Address Removed] Sent: Monday, December 30, 2002 12:58 PM
> To: LazyDBA.com Discussion
> Subject: Sql server 2000 data volume problem
>
>
> Hi Gurus,
>
> I have Three tables -
>
> TableA 6 lac recs
> TableB 2 Lac recs
> TableC 1 Lac recs
>
> Table A is is in frequent is in Insert / Update/ Delete ,
> Pl. anybody who worked in large data volume in sql server 2000 can suggest
> me will it create any problem while accessing Join of TableA , TableB and
> TableC , can it help to create a clusterd index on joined column ,pl.
also
> let me know if clustered index cause further inserts will slow.
>
> TIA
>
> Sheilesh
>
*********************************************************************
This electronic transmission is strictly confidential and intended solely
for the addressee. It may contain information which is covered by legal,
professional or other privilege. If you are not the intended addressee,
you must not disclose, copy or take any action in reliance of this
transmission. If you have received this transmission in error,
please notify the sender as soon as possible.

This footnote also confirms that this message has been swept
for computer viruses.
**********************************************************************

Oracle LazyDBA home page