DELETE释放空间

表中没有数据，但select时却有逻辑读

查看表占用空间

碎片情况

set statistics io on
select * from dbo.VoteRecord
set statistics io off

sp_spaceused VoteRecord

dbcc showcontig ('VoteRecord')

select object_id,index_type_desc,alloc_unit_type_desc,avg_fragmentation_in_percent,fragment_count
,avg_fragment_size_in_pages,page_count,avg_page_space_used_in_percent,record_count
from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('VoteRecord'),NULL,NULL,'detailed')

数据表是堆表，可以推测表中数据被delete，但其占用空间并没有完全释放。参考文章删除整张表数据但是空间没有减少

SQL Server HEAP表空间释放需要两个条件：
A deletion on this table occurs.
A table-levellock is being held.

重现这个问题

--step1、创建测试表
CREATE TABLE testfreespace
( column1 INT
,column2 CHAR(20)
,column3 VARCHAR(8000))
 
--step2、插入数据
DECLARE @count INT;
SET @count = 0;
WHILE @count < 3000
BEGIN
    SET @count = @count + 1;
    INSERT into testfreespace VALUES( @count,'test row # '+CAST(@count AS VARCHAR(10)),REPLICATE('TestData', 3000)) ;
END

--step3、查看使用空间使用情况
select object_id,index_type_desc,alloc_unit_type_desc,avg_fragmentation_in_percent,fragment_count
,avg_fragment_size_in_pages,page_count,avg_page_space_used_in_percent,record_count
from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('testfreespace'),NULL,NULL,'detailed')

直接delete删除，然后查看空间情况

记录数没有了，但所占用的空间并没有释放。解决方案有：1、DELETE语句中指定TABLOCK提示；2、TRUNCATE TABLE；3、对堆表创建聚集索引

delete from testfreespace with(TABLOCK)
TRUNCATE TABLE testfreespace