如何获取SQL Server中DELETE语句删除数据的物理字节大小？

针对你的夜间数据清理场景，有几种实用方法可以获取删除数据的字节大小，分两种需求场景给你方案：

一、获取删除数据本身的字节数（不含索引占用空间） #

这种方法精准统计被删除行的实际数据字节，适合需要明确删除内容大小的报表。

方法1：用OUTPUT子句捕获删除行后统计 #

通过OUTPUT把删除的行临时存储，再统计总字节数，避免重复写WHERE条件，且是原子操作，不会出现统计与实际删除不一致的情况：

-- 定义临时表存储删除的行（列要和原表一一对应）
DECLARE @DeletedData TABLE (
    CustomerID INT,
    OrderDate DATETIME,
    OrderDetails VARCHAR(MAX),
    -- 补充原表所有列
    IsActive BIT
);

DECLARE @DeletedBytes BIGINT, @DeletedRows INT;

-- 执行删除并捕获数据
DELETE FROM YourCustomerTable
OUTPUT DELETED.* INTO @DeletedData
WHERE OrderDate < DATEADD(MONTH, -6, GETDATE()); -- 你的旧数据过滤条件

-- 统计字节数和行数
SELECT 
    @DeletedBytes = SUM(
        DATALENGTH(CustomerID) + 
        DATALENGTH(OrderDate) + 
        DATALENGTH(OrderDetails) + 
        DATALENGTH(IsActive)
    ),
    @DeletedRows = COUNT(*)
FROM @DeletedData;

-- 后续用于Slack通知的内容
SELECT 
    '删除行数：' + CAST(@DeletedRows AS VARCHAR(20)) AS NotifyText,
    '删除数据大小：' + CAST(@DeletedBytes AS VARCHAR(20)) + ' 字节' AS SizeText;

如果表列很多，不想手动写所有列的DATALENGTH，可以用动态SQL自动生成求和语句，比如查询sys.columns拼接列名。

方法2：删除前先统计待删除行 #

先查询符合条件的行的总字节数，再执行删除，适合低并发场景：

DECLARE @DeletedBytes BIGINT, @DeletedRows INT;

-- 先统计待删除数据
SELECT 
    @DeletedBytes = SUM(
        DATALENGTH(CustomerID) + 
        DATALENGTH(OrderDate) + 
        DATALENGTH(OrderDetails) + 
        DATALENGTH(IsActive)
    ),
    @DeletedRows = COUNT(*)
FROM YourCustomerTable
WHERE OrderDate < DATEADD(MONTH, -6, GETDATE());

-- 执行删除
DELETE FROM YourCustomerTable
WHERE OrderDate < DATEADD(MONTH, -6, GETDATE());

-- 验证一致性（高并发下可能有差异）
IF @@ROWCOUNT != @DeletedRows
BEGIN
    PRINT '注意：统计行数与实际删除行数不符，数据可能被其他操作修改';
END

-- 通知内容
SELECT 
    '删除行数：' + CAST(@@ROWCOUNT AS VARCHAR(20)) AS NotifyText,
    '删除数据大小：' + CAST(@DeletedBytes AS VARCHAR(20)) + ' 字节' AS SizeText;

二、获取实际释放的存储空间（含索引） #

如果你的报表需要体现实际释放的数据库存储空间（毕竟是按存储空间收费），可以统计删除前后表及索引的空间差值：

DECLARE @BeforeSpace BIGINT, @AfterSpace BIGINT, @DeletedRows INT;

-- 获取删除前表+聚集索引的总空间（单位：字节）
SELECT @BeforeSpace = SUM(total_pages) * 8192
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('YourCustomerTable')
AND index_id IN (0, 1); -- 0=堆表，1=聚集索引，要包含非聚集索引就去掉这个条件

-- 执行删除
DELETE FROM YourCustomerTable
WHERE OrderDate < DATEADD(MONTH, -6, GETDATE());
SET @DeletedRows = @@ROWCOUNT;

-- 获取删除后的总空间
SELECT @AfterSpace = SUM(total_pages) * 8192
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('YourCustomerTable')
AND index_id IN (0, 1);

-- 计算释放的空间
DECLARE @FreedSpace BIGINT = @BeforeSpace - @AfterSpace;

-- 通知内容
SELECT 
    '删除行数：' + CAST(@DeletedRows AS VARCHAR(20)) AS NotifyText,
    '释放存储空间：' + CAST(@FreedSpace AS VARCHAR(20)) + ' 字节' AS SizeText;

注意：total_pages包含数据页、索引页及管理页，乘以8192是因为SQL Server的页面大小是8KB。

内容的提问来源于stack exchange，提问作者eidylon