Permit TRUNCATE TABLE when referencing tables are empty

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 Microsoft 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 Microsoft 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

Posted by incsharp on 11/24/2007 at 3:27 PM

use slower DELETE TOP(100) PERCENT FROM <tableName>

Posted by incsharp on 11/26/2007 at 9:40 AM

The TOP() predicate is not neeeded.

DELETE FROM <tableName>

Posted by LesterW1 on 2/17/2009 at 1:24 PM

The following will work, but is less efficient, and slower. I would prefer to see this problem fixed. Meanwhile:

TRUNCATE TABLE tablename
DBCC CHECKIDENT('tablename', RESEED, 0)

Posted by Erland Sommarskog on 5/12/2011 at 1:06 PM

I learnt this trick from SQL Server MVP Paul White:

USE tempdb;
GO
-- Test tables
CREATE TABLE dbo.Parent (parent_id INT PRIMARY KEY);
CREATE TABLE dbo.Child1 (child_id INT PRIMARY KEY, parent_id INT NULL
CONSTRAINT FK_C1_P REFERENCES dbo.Parent);
CREATE TABLE dbo.Child2 (child_id INT PRIMARY KEY, parent_id INT NULL
CONSTRAINT FK_C2_P REFERENCES dbo.Parent);
GO
INSERT dbo.Parent VALUES (1), (2), (3);
INSERT dbo.Child1 VALUES (1, NULL), (2, 1), (3, 2), (4, 2), (6, 3);
INSERT dbo.Child2 VALUES (1, NULL), (2, 1), (3, 2), (4, 2), (6, 3);
GO
-- Error 4712
TRUNCATE TABLE dbo.Parent;
-- Disable constraint checking on the referecing tables
ALTER TABLE dbo.Child1 NOCHECK CONSTRAINT FK_C1_P;
ALTER TABLE dbo.Child2 NOCHECK CONSTRAINT FK_C2_P;
-- This would still fail
-- TRUNCATE TABLE dbo.Parent;
-- Can switch, truncate, and switch back
CREATE TABLE dbo.Workspace (parent_id INT PRIMARY KEY);
ALTER TABLE dbo.Parent SWITCH TO dbo.Workspace;
TRUNCATE TABLE dbo.Workspace;
ALTER TABLE dbo.Workspace SWITCH TO dbo.Parent;
-- Remove child rows
TRUNCATE TABLE dbo.Child1;
TRUNCATE TABLE dbo.Child2;
-- Success
ALTER TABLE dbo.Child1
    WITH CHECK
    CHECK CONSTRAINT FK_C1_P;
ALTER TABLE dbo.Child2
    WITH CHECK
    CHECK CONSTRAINT FK_C2_P;
go
DROP TABLE
    dbo.Child1,
    dbo.Child2,
    dbo.Parent,
    dbo.Workspace
GO


Certainly the best so far.

Permit TRUNCATE TABLE when referencing tables are empty by Erland Sommarskog