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
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
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
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 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 ?
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
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
Could we have an explanation as to why this closure request was made, since the answer has been supplied
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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 :)
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.