• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • Last Modified:

Is it optimal to truncate and then drop keys or vice versa?

SQL Server 2000

Is it better to truncate and then drop keys prior to loading a table with a dts data transformation or is it better to drop keys and then truncate.

Please include the reasoning behind your answer.
Thank you,
Sami
0
tf842
Asked:
tf842
1 Solution
 
mcmonapCommented:
Hi tf842,

If you drop your constriants before truncating your data then there are no issues with your table truncations interfering with referential integrity.  So I would go this way around.
0
 
justinbilligCommented:
I would suggest NEVER EVER EVER EVER EVER dropping. Why even create the foreign keys if your going to drop them and possibly ruin your referential integrity
0
 
tf842Author Commented:
I was not asking about foreign keys. I was asking about keys for the table.

I have found an aswer to my question elsewhere and will deal with the points this afternoon or tomorrow.
Thank you,

Sami
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
ShogunWadeCommented:
"I was asking about keys "   to you mean indexes ?
0
 
tf842Author Commented:
I apologize for not being clear re: keys. I meant Primary Keys.
 
None-the-less, the Solution I have found and will implement is:
If the table is <= 1,000,000 records, I truncate the table leaving the primary keys intact.
If the table is > 1,000,000 records, I will drop the table and recreate it with the loading query.
Thank you for the try.
Sami
0
 
ShogunWadeCommented:
it doesnt matter in that case whether you drop the pk and truncate or truncate and drop.
0
 
ShogunWadeCommented:
PAQ/refund ?    

Could we have an explanation as to why this closure request was made, since the answer has been supplied
0
 
tf842Author Commented:
ShogunWade,
Your question is fair.

Although I supplied my working answer, I mistakenly assumed I answered my own question b/4 anyone else, and asked the moderator to close the question with the points refunded.

I believe you earned 100 partial points for your partial answer:
"it doesnt matter in that case whether you drop the pk and truncate or truncate and drop."

Should you address the remaining (and most important) part of the question:
"Please include the reasoning behind your answer." I will allocate the remaining 100 points. Depending on the quality of the answer, I will consider increasing.

I hope this seems fair to you.


I hope you would agree, the quality and completeness of an answer is most important and the points are moot.
Thank you,
Sami

0
 
ShogunWadeCommented:
Sorry tf.   i must have missed your comment way back "I have found an aswer to my question elsewhere and will deal with the points this afternoon or tomorrow."

I agree that you should have the points refunded.    I hope you didnt mind me posting, but a very small number of people on EE as questions get an answer then susequently request closure so they can keep their points for another question.  Forgive me :)

Please dont give me points just for this explanation but the answer to the reasoning behind this is:

When you drop an index the records themselves in the index are not 0'ed out but rather the pointers to the index pages are removed.  (similar to the way FAST filing system works when you delete a file)    Thus is is a very efficient operation.   When you truncate with an index, a similar operation is performed (essensially resetting the index by dumping all the pointers  (I'm being a touch abstract here because the actual specifics a lot to type, but if you need more info let me know).

The resultant index on a truncated table is a single page and a single pointer to that page.  As i mentioned earlier when you drop an index it wipes out the pointers not the pages, so a drop index on a truncated or (empty) table is simply a matter of clearing 1 pointer.

Technically speaking it is more efficient to drop the index first then truncate but the difference if so tiny that it would be innoticable (maybe 2 or 3 clock cycles)
0
 
tf842Author Commented:
ShowgunWade,
Thank you for the explanation.

Give me a fish (Answer) and I will eat for a meal(single solution). Teach me to fish(Reason and detail) and I will eat for a lifetime(this and new solutions).

Thank you,
Sami
0
 
ShogunWadeCommented:
You are welcome.     A very true statement there :)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now