Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Permit TRUNCATE TABLE when referencing tables are empty

TRUNCATE TABLE is an efficient command to empty a table, as deallocates all extents and the extend allocation is all that is logged.

However, its usefulness is reduced by the fact that you cannot use it when there are referencing foreign keys. In the general case, this makes sense, as if the referencing table has data, truncating the table is almost bound to cause a constraint violation. The exception is if all rows in the referencing table has a NULL value in the FK column, but verifying this could be expensive.

However, you may want to use TRUNCATE TABLE on a set of related tables, for which you have set up foreign-key relations, for instance:

TRUNCATE TABLE child
TRUNCATE TABLE parent

Generally, you don't want to remove and restore FK-constraints as this potentially could lead to errors.

It seems that it would not be difficult to add logic to TRUNCATE TABLE, so rather giving up on a foreign key, it would check if the referencing tables actually have any extents allocated. (Which they would not have, it the child tables also have been subject to TRUNCATE TABLE.) I said "extents" here, rather than "rows", because if the table has been emptied with DELETE, I believe there are situation when a heap still can have many extents that has to be verified that the table is empty.

Note: there are two similar suggestions in this are:
https://feedback.azure.com/forums/908035-sql-server/suggestions/32900401-truncate-table-should-support-truncating-multiple
and
https://feedback.azure.com/forums/908035-sql-server/suggestions/32900404-truncate-table-should-respect-cascade-foreign-keys
which aim at the same problem, but with different solutions. I'm not saying that my solution is the best one, but I added it as a food for thought. It is likely to be sufficient if one of these three are implemented.

7 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Erland Sommarskog shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    0 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base