Link to home
Start Free TrialLog in
Avatar of tf842
tf842

asked on

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
Avatar of mcmonap
mcmonap
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of justinbillig
justinbillig

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
Avatar of tf842

ASKER

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
"I was asking about keys "   to you mean indexes ?
Avatar of tf842

ASKER

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
it doesnt matter in that case whether you drop the pk and truncate or truncate and drop.
PAQ/refund ?    

Could we have an explanation as to why this closure request was made, since the answer has been supplied
Avatar of tf842

ASKER

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

ASKER CERTIFIED SOLUTION
Avatar of ShogunWade
ShogunWade

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of tf842

ASKER

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
You are welcome.     A very true statement there :)