Get the physical size of data deleted?

Question

When doing a DELETE statement, I know you can get the number of rows deleted, with @@ROWCOUNT.

Is there a way to get the size of the data deleted, in bytes? Not something I need, but I'm just really curious, it would be a nice feature for reporting.

Specifically I'm working in SQL Server (2019).

The use case I'm thinking, is we have a nightly script which purges old data from client DBs, as we charge them for storage space. And the task sends a slack notification after it finishes, saying how many rows it deleted. It would be cool to be able to add how many bytes of data it removed.

Answer 1

You can output the data size into a temp table, then sum it up.

DECLARE @tmp TABLE (size bigint NOT NULL);

DELETE SomeTable
OUTPUT DATALENGTH(deleted.Column1) + DATALENGTH(deleted.Column2) + DATALENGTH(deleted.Column3)
INTO @tmp (size)
WHERE whatever;

SELECT SUM(size) AS sizeBytes, COUNT(*) AS countRows
FROM @tmp;