Primary Key Non-Cluster Index

Is there a case when the primary key should not be cluster index?
I have a SQL Server 2000 database that has several tables that have
non-cluster indexes for primary keys. Will this slow down the performance o
f
application that uses this table often?
Thank You,Joe,
1. Yes. When the clustered index is better utilized on a different column
to support range and sorting queries.
2. It depends - on the type of queries that are being used in the
application.
HTH
Jerry
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:C3414875-894D-4F0E-87DE-46CA9A842CD8@.microsoft.com...
> Is there a case when the primary key should not be cluster index?
> I have a SQL Server 2000 database that has several tables that have
> non-cluster indexes for primary keys. Will this slow down the performance
> of
> application that uses this table often?
> Thank You,|||On Tue, 25 Oct 2005 11:16:11 -0700, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:
>Is there a case when the primary key should not be cluster index?
>I have a SQL Server 2000 database that has several tables that have
>non-cluster indexes for primary keys. Will this slow down the performance
of
>application that uses this table often?
If there is NO clustered index on a table, and if you delete rows
(including if you update rows and because they need more space they
have to move), then you may wake up the (potentially evil) SQLServer
ghost.
Otherwise, whether a clustered index is going to help, or hurt, or do
nothing is highly dependent on application logic.
Josh|||"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:C3414875-894D-4F0E-87DE-46CA9A842CD8@.microsoft.com...
> Is there a case when the primary key should not be cluster index?
> I have a SQL Server 2000 database that has several tables that have
> non-cluster indexes for primary keys. Will this slow down the performance
> of
> application that uses this table often?
> Thank You,
Of course. Keep in mind that a clustered index sorts the actual data rows
in index order. It is most often used when you are selecting on a range of
data like "OrderNumber Between 10 and 100", or you are sorting your data in
clustered index order.
If you are doing a lot of INSERTs into the table, the data must be placed in
clustered index order. If you are using a monotonically increasing value
(like IDENTITY), then there will always be space at the end of the last
datapage for your next row of data and page splitting doesn't occur. If you
are not using a monotonically increasing value, for example, LastName or a
GUID, then the database will have to make space for the new row if there is
not space already. This can cause page splitting which is time consuming
and resource consuming.
The performance of other applications depends on what those applications are
querying for.
Rick Sawtell
MCT, MCSD, MCDBA