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
tf842Asked:
Who is Participating?
 
ShogunWadeConnect With a Mentor Commented:
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
 
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.