sql server 测试delete后数据空间情况

总结结论：

【1】如果是索引组织表，删除的数据空间是会被文件设置为可用状态，其他表都可以使用。

【2】如果是堆表，删除数据空间也会设置为可用状态，但是只能给被删除数据的表使用。

【3】truncate删除表数据、drop table 表，是会释放数据空间（即把占用部分数据空间置为空闲）给数据文件（注意，这里是给数据文件而不是操作系统，数据文件本身大小不会改变），然后所有表对象的增删改都可以使用这部分空间。

1.测试delete后数据空间情况（聚集索引）

（1）文件大小　　

第1列为逻辑名称，第2列为文件大小，第3列为数据占用大小，第4列为数据占用/文件大小 的百分比。

（2）数据表情况（本测试用test103表进行操作，占了240M）

（3）表结构

（4）删除test103表200W数据

delete top(2000000) from test103

（5）查看验证数据量

操作前：

操作后：

数据文件还涨了？事务日志文件怎么多了这么多，什么情况？

查看下增长规律：

（6）再次删除200W条，查看数据文件

初步结论。（聚集索引）

在第1次删除的时候（删除200w）

数据文件：文件大小从260M-》320M，实际占用数据大小从260-》220M。 数据文件总大小加了60M，数据占用大小减少了40M左右。

日志文件：文件大小从380M-》1200M，实际占用大小从370M-》800M。。数据文件总大小加了800多M，实际数据占用增加了430M左右。

在第2次删除的时候（删除200w）

数据文件：文件大小从320M-》320M，实际占用数据大小从220M-》167M。 数据文件总大小加了0M，数据占用大小减少了53M。

日志文件：文件大小从1200M-》1588M，实际占用大小从800M-》1223M。。数据文件总大小加了400M左右，实际数据占用增加了430M左右。

2.插入操作

重新插入200W行数据到原表，

;with temp1 as(
select 1 as id,'a' as name,'b' as home,123456789 as phone,'gg' as 'desc'
union all
select id+1,'a' as name, home,phone+1,[desc] fromtemp1
where id <=2000000
)
insert into test103 fromtemp1
option(maxrecursion 0)

结果如下：

数据占用多了229-167 约等于50M

插入一个新表200W行（没有聚集索引，即堆表）

;with temp1 as(
select 1 as id,'a' as name,'b' as home,123456789 as phone,'gg' as 'desc'
union all
select id+1,'a' as name, home,phone+1,[desc] fromtemp1
where id <=2000000
)
select * into test1 fromtemp1
option(maxrecursion 0)

结果如下：

结论：通过聚集索引删除的数据，文件空间不会释放，但是会置于空闲状态。有其他数据使用的时候就会用次来填充。

3.Delete非聚集索引表，即堆表

直接删除test1即上面新建的200W数据行的堆表

delete test1

再查看大小：

嗯哼~！没有变化？

然后我们重复插入会该表100W数据看看。

插入完成之后来看看：

空间也没有变。

那么我再插入50W条到test3表（一个新表），看看数据文件会不会增加。（为什么50W呢？我不想超出200W的上线，刚已经插入了100W了）

然后发现，增了？？

难道，没有聚集索引的堆表，删除表数据后，数据只能给该表用？其他表不能占？

可能是这样，但我还要进一步确认，刚刚我们删除了test1表（堆表）的200W数据，后面又插入了100W数据，然而数据文件大小及实际数据占用内容并没有变化。

我们现在再插入99W试一试，200W删除-100W插入-99W插入，如果我们上面的推测是正确的，那么数据文件及文件实际占用也不会增加。

看看文件占用：

很明显，我们的理论是正确的。

结论：delete删除堆表数据，不会把数据空间置为空闲给交还给数据文件（注意，数据文件本身大小不会改变），然后所有表对象的增删改都不可以使用这部分空间，只有被删除数据的表才可以用这部分数据空间。

4.truncate 删除数据

truncate table test1（堆表）

结果：

很明显，truncate，是把数据空间占用给释放出来了，后续所有的表都可以使用该空闲空间；

结论：truncate删除表数据，是会释放数据空间（即把占用部分数据空间置为空闲）给数据文件（注意，这里是给数据文件而不是操作系统，数据文件本身大小不会改变），然后所有表对象的增删改都可以使用这部分空间。

5.drop 删除表

再往test1表中，插入200W数据；

结果：

然后我们delete整个表看看

delete test1

结果：

依然没有变化；

我们直接drop table test1看看

结果：

执行成功后，发现，数据空间也释放出来了。

总结结论：

【1】如果是索引组织表，删除的数据空间是会被文件设置为可用状态，其他表都可以使用。

【2】如果是堆表，删除数据空间也会设置为可用状态，但是只能给被删除数据的表使用。

【3】truncate删除表数据、drop table 表，是会释放数据空间（即把占用部分数据空间置为空闲）给数据文件（注意，这里是给数据文件而不是操作系统，数据文件本身大小不会改变），然后所有表对象的增删改都可以使用这部分空间。