Permit TRUNCATE TABLE when referencing tables are empty - by Erland Sommarskog

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


94
0
Sign in
to vote
ID 312074 Comments
Status Closed Workarounds
Type Suggestion Repros 6
Opened 11/24/2007 9:42:41 AM
Access Restriction Public

Description

TRUNCATE TABLE is a very quick way to delete all rows in a table. However,
TRUNCATE TABLE is not permitted when there are FK referring to the table,
and for a very good reason: TRUNCATE TABLE is fast because it does not
look at the data in the table, it just deallocates all pages allocated to the
table.

But there is one special case that TRUNCATE TABLE ought to consider: all
referring tables are empty. That would permit you to run TRUNCATE TABLE
on a set of tables as long as you truncate them in the right order. Examining
whether a table is empty or not is a quick operations in most cases. 

This suggestion is similar to
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=251876
but with my suggestion you would not have to fiddle with enabling/disabling 
constraints. On other hand, the suggestion in 251876 covers the situation when 
you want to truncate a single table and reload it.
Sign in to post a comment.
Posted by Will Rayer on 7/29/2013 at 3:40 AM
Although this has been closed, I would ask Microsoft to consider re-opening the case. It would be a time saver to have this feature, and if implemented as Erland says below I cannot see that TRUNCATE would become expensive. So I feel the merits of the idea outweigh any complications.
Posted by Erland Sommarskog on 12/10/2011 at 11:42 AM
Of course, I don't know the internals very well, but I cannot really see where the big problems are.

To simplify, we can identify two situations where you could use TRUNCATE TABLE on a table referenced by many foreign keys.

The first situation is when all tables are idle. This is the typical use when you would actually use this feature. In this situation there is no concurrency problem, because there are is no active access to any of the tables. If the statement fails it would typically be because the user forgot to empty that table first. I can't see that there would be any damage with TRUNCATE TABLE checking for empty tables.

The second situation is when one or more of the tables are live. We can first note that this is not the correct usage pattern, but issuing the statement is a user error. What could happen here is that TRUNCATE TABLE first locks the target table with Sch-M, it then goes to check the other tables and tries to grab a shared lock in each table, to verify that it is empty and to prevent anyone else from inserting a row while we are running. Unfortunately, there is long-running transaction that has modified/inserted a row in this table, so we cannot get the shared lock. This means that we could be sitting with the Sch-M lock on the target table for a long time. That could indeed be bad. But if you think of it, if you can't get that shared lock in a few milliseconds, how likely is that the table is empty? While a novelty, it may be worth to have an implicit lock timeout here. (And an error message that explains that you only assume the table to be non-empty.)

I like to add here that I am not adamant on this particular feature. What I am interested in is one or more solutions to the root problem. People want to delete a lot of data without having their transaction logs exploding. There is a twin request:
https://connect.microsoft.com/SQLServer/feedback/details/509341/provide-a-mechanism-for-minimally-logged-deletes-that-wont-bloat-the-transaction-log
The way it is written, I have voted against it, because it seems to say that deletes have not to be rolled back. But I can see that deletes can be minimally logged, and still be rollbackable/recoverable: if more than x % of the rows in an extent are targeted by the delete, copy the remaining rows to a new extent (minimally logged) and log only the extent deallocation. This would also from a user perspective be a better solution, since there are no permissions issues with DELETE as there is with TRUNCATE TABLE, and it would also work when you only delete a subset of the rows.

However, I can also see that minimally logged deletes is a much taller order. My idea was intended to achieve a bond-fide solution that would be fairly simple to implement.

But if you say that you will do minimally logged deletes, go for it. But do something! This is a big painpoint for users.



Posted by Shantanu [MSFT] on 12/8/2011 at 4:16 PM
Dear Erland,

Although we agree this is a useful scenario, we had an extended discussion on this and decided against implementing it. The reason is that, after investigation we concluded, implementing this has a host of side-effects - it will hurt concurrency on other tables and make truncate expensive, among other things. We think the negative implications of implementing this feature seem to be larger than the benefits of doing it.

If you feel the merits of this feature outweigh the negative side-effects, kindly let us know and we will be willing to revisit it.

Thanks,
Shantanu Kurhekar
Program Manager, SQL Engine
Posted by Knot on 11/17/2011 at 8:26 PM
TRUNCATE TABLE is my preference over DELETE FROM because it reseeds the identity column automatically. Having this functionality available when all referring tables are empty would be an improvement over dropping FK constraints, truncating, re-instating FK constraints.
Posted by arthur661 on 7/15/2010 at 5:14 PM
I agree, this would be a very good feature. I am running into this limitation a lot and am very frustrated to constantly have to change scripts to use delete and then reseed my identities.
Posted by Joachim [MSFT] on 1/24/2008 at 5:53 PM
Dear Erland,

Agreed. Allowing TRUNCATE TABLE in the situation you describe is a useful special case. On the other hand, as we are wrapping up the current release we are not in the position to entertain any change requests. However, we will take your suggestion into consideration when we plan the next round of improvements.

Regards,

Joachim Hammer

Program Manager
SQL Server