February 17, 2012 at 11:51 am
Some senior people argue that I should not use drop table on the working table and using truncate instead.
Can any one tell me it is right or not.
My own thought is depend on the situation. But it should not be big deal.
Please advise
Thanks
February 17, 2012 at 12:02 pm
but it is a working table...do you care to use truncate or drop??
February 17, 2012 at 12:04 pm
It depends.
A drop table removes the object from the database. (including indexes, grants, ...)
What would be the difference of using a temp table ?
A truncate only removes the data ( but does it in a fast way ) as would a delete without where clause do.
Advantage is indexes, grants, ... would still be in place for usage later on.
Your DBA may even work with you to optimize it all.
edited:
Yes this may be interesting, even for a worker table . ( put it in a worker db / filegroup / ... )
(if you cannot do it with a #temptb)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 17, 2012 at 1:00 pm
stephenhai (2/17/2012)
but it is a working table...do you care to use truncate or drop??
Depends what you're trying to achieve.
Truncate removes all the data from the table, but leaves the empty table with all its indexes, constraints, triggers, etc. Drop table removes the table and all dependant objects from the database
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 17, 2012 at 1:15 pm
Truncate also resets any column seed values
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 17, 2012 at 1:56 pm
If you want to get rid of the data use truncate.
If you want to get rid of the object use drop.
What tool would you use to screw togheter two pieces of wood? a hammer or a screwdriver?
Bottom line, use the right tool for the task.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 17, 2012 at 3:40 pm
PaulB-TheOneAndOnly (2/17/2012)
What tool would you use to screw togheter two pieces of wood? a hammer or a screwdriver?
Both the tools bond 2 pieces of wood.
I would ask "What tool would you use to screw togheter two pieces of wood? a screwdriver or a saw?" :w00t:
No pun intended, just for a little gag 😎
February 17, 2012 at 4:08 pm
ColdCoffee (2/17/2012)
PaulB-TheOneAndOnly (2/17/2012)
What tool would you use to screw togheter two pieces of wood? a hammer or a screwdriver?Both the tools bond 2 pieces of wood.
I would ask "What tool would you use to screw togheter two pieces of wood? a screwdriver or a saw?" :w00t:
No pun intended, just for a little gag 😎
Tools selection was on purpose because one of them do it properly while the other one does a poor job at it
Have you ever hammered a screw? it works but doesn't look professional, init?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 17, 2012 at 4:15 pm
PaulB-TheOneAndOnly (2/17/2012)
Have you ever hammered a screw?
If the screw is tapered and should have a sharp point, then yes 😀
PaulB-TheOneAndOnly (2/17/2012)
it works but doesn't look professional, init?
Totally agree!
February 17, 2012 at 4:16 pm
sumitagarwal781 (2/17/2012)
both are totally different, Drop removes the object from database and truncate just deallocates the data pages but retains the definition.
If you drop the object to recreate it you need much higher rights then if you truncate the table. Thus, truncate unless you're doing schema work. Why do you believe drop is the better choice to clear data when you'll need to rebuild the object?
It's like arguing should I empty out a box and reuse it or throw it in the fire and get a new one when it's the exact size I already needed.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply