Primary Key Index

Hi,

I have a table in my SQL 2000 database called utContact, this has a primary called ContactID (int, idenitity), by default this should have a clustered index on it, but when I go to view the indexes on the table it shows the primary key index as non clustered.

How can this be?

SQL server also will not let me change the index to clustered, but I need to as this is causing a lot of table scans in query execution plans.

Any help or advice on this matter would be greatly appreciated.

CheersOriginally posted by SQLSlammer
Hi,

I have a table in my SQL 2000 database called utContact, this has a primary called ContactID (int, idenitity), by default this should have a clustered index on it, but when I go to view the indexes on the table it shows the primary key index as non clustered.

How can this be?

SQL server also will not let me change the index to clustered, but I need to as this is causing a lot of table scans in query execution plans.

Any help or advice on this matter would be greatly appreciated.

Cheers
there can be only one clustered index on a table. by default when u add a primary key a clustered index is created, but if there is already a clustered index present on the table, a non clustered index will be created. Check if there exists a clustered index on any other column in the table.|||Hi,

there is no other clustered index, they are all non clustered.|||can u post the script for the table?|||Here you go|||it works for me.
i created the table as per your script and made contact id as the primary key, it creates a clustered index on the column.|||Yeah it would do,

But for some reason mine shows as a non clustered index, this is not a new table, it was created before I joined the company.

What I really need to know is how do I convert this index into a clustered index without losing any data.|||Have you tried dropping all indexes and pk's and reapplying them?