Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Location
    London
    Posts
    169

    Unanswered: 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.

    Cheers

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Re: Primary Key Index

    Originally 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.

  3. #3
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    Hi,

    there is no other clustered index, they are all non clustered.

  4. #4
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    can u post the script for the table?

  5. #5
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    Here you go
    Attached Files Attached Files

  6. #6
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    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.

  7. #7
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    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.

  8. #8
    Join Date
    Sep 2003
    Posts
    364
    Have you tried dropping all indexes and pk's and reapplying them?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •