I see some code where the author has truncated a temp table immediately before dropping the temp table. Is there a reason for doing this?
TRUNCATE TABLE #Temp
DROP TABLE #Temp
views:
92answers:
5
A:
Possibly to see if the TRUNCATE command throws an exception due to existing foreign keys?
davek
2010-04-09 15:28:10
I suppose that isn't out of the realm of possibilities but this particular code is not in any sort of error handling block
Joe Philllips
2010-04-09 15:35:53
I can't think of any other reason then, other than a wrong assumption (as mentioned in the answer from CodeByMoonlight)
davek
2010-04-09 15:38:41
+1
A:
Possibly the author was under the impression that DROP TABLE would be quicker if the table was already empty and knew that TRUNCATE would be quicker than DELETE.
CodeByMoonlight
2010-04-09 15:37:13
+1
A:
On very large temp tables, it's sometimes faster to truncate first then drop because truncate simply moves a pointer. It's normally not needed since temp tables drop on their own.
Jeff Moden
2010-04-10 00:41:09
+1
A:
Another reason is that a DROP TABLE is a fully logged operation, so by truncating first (which is never logged) you lower transactional logging overhead.
Alex K.
2010-04-10 14:43:57
I've learned recently that a truncate is logged (but not like a delete is) but I understand your point
Joe Philllips
2010-08-30 21:54:52