Solved

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

Posted on 2004-10-06
12
291 Views
Last Modified: 2012-05-05
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
Comment
Question by:tf842
12 Comments
 
LVL 15

Expert Comment

by:mcmonap
ID: 12237188
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
 
LVL 15

Expert Comment

by:justinbillig
ID: 12237386
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
 

Author Comment

by:tf842
ID: 12237666
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12237843
"I was asking about keys "   to you mean indexes ?
0
 

Author Comment

by:tf842
ID: 12238005
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 18

Expert Comment

by:ShogunWade
ID: 12239538
it doesnt matter in that case whether you drop the pk and truncate or truncate and drop.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12247322
PAQ/refund ?    

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

Author Comment

by:tf842
ID: 12247719
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
 
LVL 18

Accepted Solution

by:
ShogunWade earned 400 total points
ID: 12247838
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
 

Author Comment

by:tf842
ID: 12247970
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12248021
You are welcome.     A very true statement there :)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now