Dropping Clustered index associated with Primary Key.

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.

Thanks.

From BOL.

"When you specify a PRIMARY KEY constraint for a table, the SQL Server 2005 Database Engine enforces data uniqueness by creating a unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries. Therefore, the primary keys that are chosen must follow the rules for creating unique indexes."

so basically you can't have a primary key without any indexes. Although you can have PK with a non-clustered index.

I am curious what made you to go down this path to remove the indexes on this table. What are you trying to achieve here?

|||

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/anyone have any other idea about dealing with this kind of scenario, plz let me know.

Your help is greatly appreciatied.

Thanks.

|||In this scenario, whether to drop the indexes and re-create them after bulk insert or bulk insert data without dropping indexes depends on the ratio of data in the table vs the data coming to the table. If there are already millions of rows, and lot more millions to come then I think its best to drop the indexes and recreate them. Also you can pick up some performance, if you can split the one big file into smaller files and importing them concurrently by specifying TABLOCK after dropping the indexes. Make sure 'select into/bulkcopy' option is set to true.
|||

Along with the other suggestions, also consider changing the recovery model of the database to BULK LOGGED or SIMPLE recovery.

HTH!

|||

Thanks sankar and rich for your suggestions.

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.

|||

As Sankar mentioned, if you want a primary key or indeed any constraint to enforce uniqueness you will have an index. This is not something you can change.

You are right in saying that having it protects the integrity of your data, so unless you are able to drop the primary key, insert the data, tidy up any dupes and then reapply the primary key you're going to have to live with the fact that you have an index.