drop 和 truncate 再 drop 两种删除表的方式对比评测

USE [master]
GO
--1. 创建测试库
CREATE DATABASE [test]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'test', FILENAME = N'D:\database\2014\test.mdf' , SIZE = 8192KB , FILEGROWTH = 8192KB )
 LOG ON 
( NAME = N'test_log', FILENAME = N'D:\database\2014\test_log.ldf' , SIZE = 8192KB , FILEGROWTH = 8192KB )
GO
--2. 创建测试表
USE test
GO
IF OBJECT_ID('tmp') IS NOT NULL
    DROP TABLE tmp
GO
CREATE TABLE tmp(
    id bigINT IDENTITY(1,1) PRIMARY KEY,
    n NVARCHAR(MAX) 
)
GO
--3. 插入1千万条测试数据
SET NOCOUNT ON
DECLARE @i INT,@iMax INT
SET @iMax=10000000
WHILE 1=1 
BEGIN
    SELECT @i=COUNT(1) FROM tmp
    PRINT @i
    IF @i>=@imax
    BEGIN
        BREAK;
    END
    IF @i=0
    BEGIN
        INSERT INTO tmp (n) VALUES(N'xxxxxxxxxxxxxxx') 
    END
    IF @i<=@imax/2
    BEGIN
        INSERT INTO tmp (n)
        SELECT n FROM tmp
    END
    ELSE
    BEGIN
        INSERT INTO tmp (n)
        SELECT TOP( @iMax-@i ) n FROM tmp
    END
END
-- 1000 万数据也就 2 分 45 秒
--4. 查看文件的大小
EXEC sp_spaceused
/*
database_name	database_size	unallocated space
test	1488.00 MB	4.75 MB

reserved	data	index_size	unused
511232 KB	507272 KB	3480 KB	480 KB
*/
SELECT * FROM sys.sysfiles AS s
/*
fileid	groupid	size	maxsize	   growth	status	perf	name	    filename
1	       1	64512	-1	       1024	       2	 0	    test	    D:\database\2014\test.mdf
2	       0	125952	268435456  1024	       66	 0	    test_log	D:\database\2014\test_log.ldf
*/
--5. 备份数据库，便于测试对比
BACKUP DATABASE test TO DISK =N'd:\database_bak\test20170919.bak' WITH COMPRESSION,STATS=20

--6. 只做 drop 操作(不做truncate)
DROP TABLE dbo.tmp

EXEC sp_spaceused
/*
database_name	database_size	unallocated space
test	1488.00 MB	501.63 MB

reserved	data	index_size	unused
2424 KB	928 KB	1096 KB	400 KB
*/
SELECT * FROM sys.sysfiles AS s
/*
fileid	groupid	size	maxsize	growth	status	perf	name	filename
1	       1	64512	-1	1024	2	0	test	D:\database\2014\test.mdf
2	       0	125952	268435456	1024	66	0	test_log	D:\database\2014\test_log.ldf
*/

--7. 还原这个库， 重新测试
--kill 指定库上的所有进程
DECLARE @sql NVARCHAR(MAX)
SET @sql=''
SELECT @sql = @sql+ 'kill '+cast(spid AS VARCHAR(10))+';' FROM sys.sysprocesses AS s WHERE s.dbid=DB_ID('test') AND spid!=@@spid
SET @sql=REPLACE(@sql,';','
')
PRINT @sql
EXEC (@sql)

USE [master]
GO
RESTORE DATABASE test FROM DISK=N'd:\database_bak\test20170919.bak' WITH FILE=1

--8. 执行 truncate 和 drop 
USE test
GO

TRUNCATE TABLE dbo.tmp
DROP TABLE dbo.tmp

EXEC sp_spaceused
/*
database_name	database_size	unallocated space
test	           1488.00 MB	   501.63 MB

reserved	data	index_size	unused
2424 KB	936 KB	1096 KB	392 KB
*/

SELECT * FROM sys.sysfiles AS s
/*
fileid	groupid	size	maxsize	growth	status	perf	name	filename
1	       1	64512	-1	1024	2	0	test	D:\database\2014\test.mdf
2	       0	125952	268435456	1024	66	0	test_log	D:\database\2014\test_log.ldf
*/
--改为完全模式再试
USE [master]
GO
DECLARE @sql NVARCHAR(MAX)
SET @sql=''
SELECT @sql = @sql+ 'kill '+cast(spid AS VARCHAR(10))+';' FROM sys.sysprocesses AS s WHERE s.dbid=DB_ID('test') AND spid!=@@spid
SET @sql=REPLACE(@sql,';','
')
PRINT @sql
EXEC (@sql)


RESTORE DATABASE test FROM DISK=N'd:\database_bak\test20170919.bak' WITH FILE=1

USE [master]
GO
ALTER DATABASE [test] SET RECOVERY FULL WITH NO_WAIT
GO

USE test
GO

DROP TABLE dbo.tmp

EXEC sp_spaceused
/*
database_name	database_size	unallocated space
test	1488.00 MB	501.63 MB

reserved	data	index_size	unused
2424 KB	936 KB	1096 KB	392 KB
*/
SELECT * FROM sys.sysfiles AS s
/*
fileid	groupid	size	maxsize	growth	status	perf	name	filename
1	        1	64512	-1	1024	2	0	test	D:\database\2014\test.mdf
2	        0	125952	268435456	1024	66	0	test_log	D:\database\2014\test_log.ldf
*/
--再恢复再改完全用 truncate , drop 试
USE [master]
GO
DECLARE @sql NVARCHAR(MAX)
SET @sql=''
SELECT @sql = @sql+ 'kill '+cast(spid AS VARCHAR(10))+';' FROM sys.sysprocesses AS s WHERE s.dbid=DB_ID('test') AND spid!=@@spid
SET @sql=REPLACE(@sql,';','
')
PRINT @sql
EXEC (@sql)


RESTORE DATABASE test FROM DISK=N'd:\database_bak\test20170919.bak' WITH FILE=1

USE [master]
GO
ALTER DATABASE [test] SET RECOVERY FULL WITH NO_WAIT
GO

USE test
GO

TRUNCATE TABLE dbo.tmp
DROP TABLE dbo.tmp

EXEC sp_spaceused
/*
database_name	database_size	unallocated space
test	1488.00 MB	501.63 MB
reserved	data	index_size	unused
2424 KB	928 KB	1096 KB	400 KB
*/

SELECT * FROM sys.sysfiles AS s
/*
fileid	groupid	size	maxsize	growth	status	perf	name	filename
1	       1	64512	-1	1024	2	0	test	D:\database\2014\test.mdf
2	       0	125952	268435456	1024	66	0	test_log	D:\database\2014\test_log.ldf
*/

本人测试版本为： 2014

实际上两种方式是一样的， 直接drop 并不会产生多少日志， truncate 再 drop 的做法可能在低版本（如2000）上是有效的。