Someone had an issue where a third party app would truncate a table for maintenance and they had no control over the truncate and when it happened. The issue was that they needed to know how many records were in the table before the truncate occurred.
Let’s start by understanding what DELETE and TRUNCATE are and how they work.
When you delete from a table, you are removing rows. You can specify what rows to delete with a WHERE clause. When you TRUNCATE a table, it is not logging what rows are deleted and it does perform minimal logging. TRUNCATEis a great way to clear a table without causing the Transaction Logs to balloon.
Likewise, a Trigger is not going to capture a truncate statement as that is a limitation of triggers. You can use extended events to capture Truncate events as Jonathan Kehayias shows on MSDN.
If you want to prevent users from running Truncate, you can create a view or another object with SCHEMABINDING. This prevents DDL statements from altering the table in question, which includes Truncate.
One last note about Truncate is that if you have an identity column, it will reseed the value. I have seen people use the RESEED function and Truncate together. They will capture the last identity used, store it into a variable table, truncate the table removing all records with minimal logging and resetting the identity column, then reseed the last incremental. Usually if you truncate though, you don’t care about the reseed. (Not always true, but usually.)
I truncate primarily on staging tables or when I need to reset an instance for testing. I rarely truncate tables otherwise because I care about the referential data integrity and which rows are removed from the tables. When I care about neither, I truncate.
I would highly recommend to read more on both Delete and Truncate, they will achieve similar results but are two very different things.
Atul Gaikwad on MSSQLTips wrote a nice quick article on some of those differences.
Now let’s look at this question:
Can I count the rows that will be affected by the truncate from a third party application?
Ideally, the truncate event is scheduled or it can be planned for. Even more ideally, you will be able to alter the code or add something in so it can log information for you.
So how do you track what rows will be affected by truncate if you had to, with absolute certainty?
I personally would create two tables, a trigger, and a procedure.
The trigger would be based on Insert or delete tasks and it would insert into a table the number of rows affected, the table name, and the datetime. So if your process inserted 6 rows, the trigger would insert the value 6 indicating 6 new records flowed in. If you deleted 10 rows, it would insert a value of -10 indicating 10 records are now gone.
I would want to create an extra column for notes or events. I would want the audit or extended events to log to this table the day / time the truncate occurred. They won’t be able to tell you how many rows, but it can tell you when and who. That’s what’s really important at this moment.
The second table would be an aggregate of the first table as I assume you may have many transactions in a day. This would tell you the net total of records at the end of the day and also tell you when truncates occurred. This means you would know how many records were in the table just prior to truncation, meeting your requirements.
The procedure would do the aggregation between tables and it would also perform cleanup work on the first table. Thusly allowing you to retain when your table was truncated and allowing you to keep a rolling count of how many records have flowed through that table.
