SQL Server Performance

Dropping Clustered index associated with Primay key

Discussion in 'SQL Server 2005 General DBA Questions' started by Fais, Aug 23, 2007.

  1. Fais New Member

    Hi all,
    I have a huge table with million of rows, which has ONE Clustered index associated with the PRIMARY KEY, and there are some NON_Clustered indexes.
    So,now i decided that, i dont need any more indexes ( not even one) on that table, but i need to maintain primary key on that table.
    (a) So, how can i accomplish this (i.e.) having primay key but not having indexes on the table.
  2. ndinakar Member

    If you want to have a primary key you need to have either a clustered index or non clustered index on it.
    Now would you elaborate on what basis you have "decided" "to remove all indexes" ?
  3. Fais New Member

    Actually, there is a big file, which bulk inserts into that huge table,and that table is very rarely used for selects statements.
    So, i was thinking , if we could remove the indexes before bulk inserting the data and then rebuild them ( as we build , every week), we can improve the performance of bulk inserting the data into that table.
    If you know any other way , plz let me know.
  4. satya Moderator

    IF the select queries are very less and inserts are high yes dropping indexes would benefit in load operation performance.
  5. Fais New Member

    Hi Satya/all,
    Actually there are 9 Non-Clustered indexes and a Clustered index associated with the Primary key on that huge table.
    I can drop the non-clustered indexes, But, the Clustered index is associated with Primary key. How can i drop the clustered index, by having primary key on the table.
    If i drop the Primary key consraint , then clustered index will be removed. But inserting data on a table without primary key , can lead into data inconsistency ( i mean, some duplicates/null can come into the table).
    Thanks for your help.
  6. thomas New Member

    A primary key is implemented with an index. It's how SQL Server knows if a value has already been inserted or not. So if you want a Primary Key, you must have at least 1 index.
  7. ndinakar Member

    Dropping the 9 non clustered indexes should be enough. Its better to keep the PK (with the clustered index) in case you have to query back to check for any duplicates or missing rows.. Lets say yiy start the bulk insert and it fails in the middle.. then you want to query the table to see if any rows got in.. then you restart the bulk insert and you need to query if there are any duplicates..?
  8. satya Moderator

Share This Page