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.

DROP vs TRUNCATE which is faster?

I work with datafiles containing more than 1 million records. Which would be faster DROP TABLE or TRUNCATE TABLE?

You would DROP the table if you do not intend on using the table again.

You would TRUNCATE a table if you intend to use the table again.

I would imagine the speed difference is insignificant compared to doing what is functionally correct for your situation. The only case where the speed might make a difference is if you are performing the operation many hundred times over. So lets consider that scenario:

If you no longer need these several hundred tables, then you might as well DROP them, else they will remain there taking up resources.

If you are going to reuse the tables, then TRUNCATE them, else you are faced with having to recreate them. I would guess that TRUNCATE is faster versus the two oeprations of DELETE+CREATE.

drop a column

Hi,
What's the effect to drop a column from a table has 13 million records? I am
mainly concern the time it takes. Thanks
Hi Jen
"Jen" wrote:

> Hi,
> What's the effect to drop a column from a table has 13 million records? I am
> mainly concern the time it takes. Thanks
If you are using the ALTER TABLE statement to do this there will be a schema
modify (Sch-M) lock on the table whilst this is occuring. A Sch-M lock is
incompatible with all lock modes therefore no-one will have access to the
table.
You may want to check out the topics "Alter Table" and "Lock Compatibilty"
in Books Online.
John
|||It seems it doesn't mention Lock in ALTER TABLE, and can't seach for "Lock
Compatibilty". Is there other way to drop? Thanks
"John Bell" wrote:

> Hi Jen
> "Jen" wrote:
>
> If you are using the ALTER TABLE statement to do this there will be a schema
> modify (Sch-M) lock on the table whilst this is occuring. A Sch-M lock is
> incompatible with all lock modes therefore no-one will have access to the
> table.
> You may want to check out the topics "Alter Table" and "Lock Compatibilty"
> in Books Online.
> John
|||Hi Jen
"Jen" wrote:
[vbcol=seagreen]
> It seems it doesn't mention Lock in ALTER TABLE, and can't seach for "Lock
> Compatibilty". Is there other way to drop? Thanks
> "John Bell" wrote:
For the "Lock Compatibility" subject see
http://msdn2.microsoft.com/en-us/library/aa213041(sql.80).aspx
If you used EM to drop the column it would firstly create a new table, then
suck all the data from the old table, then drop the old table and finally
rename the new table. As you can imaging you would can grow quite a bit older
whilst this is happening! ALTER TABLE is your best option and you will need
to plan for an interuption to service. Your best indicator of how long it
would take may be to try it on a test system first.
Is there an issue leaving this column in place?
John

drop a column

Hi,
What's the effect to drop a column from a table has 13 million records? I am
mainly concern the time it takes. ThanksHi Jen
"Jen" wrote:

> Hi,
> What's the effect to drop a column from a table has 13 million records? I
am
> mainly concern the time it takes. Thanks
If you are using the ALTER TABLE statement to do this there will be a schema
modify (Sch-M) lock on the table whilst this is occuring. A Sch-M lock is
incompatible with all lock modes therefore no-one will have access to the
table.
You may want to check out the topics "Alter Table" and "Lock Compatibilty"
in Books Online.
John|||It seems it doesn't mention Lock in ALTER TABLE, and can't seach for "Lock
Compatibilty". Is there other way to drop? Thanks
"John Bell" wrote:

> Hi Jen
> "Jen" wrote:
>
> If you are using the ALTER TABLE statement to do this there will be a sche
ma
> modify (Sch-M) lock on the table whilst this is occuring. A Sch-M lock is
> incompatible with all lock modes therefore no-one will have access to the
> table.
> You may want to check out the topics "Alter Table" and "Lock Compatibilty"
> in Books Online.
> John|||Hi Jen
"Jen" wrote:
[vbcol=seagreen]
> It seems it doesn't mention Lock in ALTER TABLE, and can't seach for "Lock
> Compatibilty". Is there other way to drop? Thanks
> "John Bell" wrote:
>
For the "Lock Compatibility" subject see
http://msdn2.microsoft.com/en-us/library/aa213041(sql.80).aspx
If you used EM to drop the column it would firstly create a new table, then
suck all the data from the old table, then drop the old table and finally
rename the new table. As you can imaging you would can grow quite a bit olde
r
whilst this is happening! ALTER TABLE is your best option and you will need
to plan for an interuption to service. Your best indicator of how long it
would take may be to try it on a test system first.
Is there an issue leaving this column in place?
John