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 testGOIF OBJECT_ID('tmp') IS NOT NULL    DROP TABLE tmpGOCREATE TABLE tmp(    id bigINT IDENTITY(1,1) PRIMARY KEY,    n NVARCHAR(MAX) )GO--3. 插入1千万条测试数据SET NOCOUNT ONDECLARE @i INT,@iMax INTSET @iMax=10000000WHILE 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    ENDEND-- 1000 万数据也就 2 分 45 秒--4. 查看文件的大小EXEC sp_spaceused/*database_namedatabase_sizeunallocated spacetest1488.00 MB4.75 MBreserveddataindex_sizeunused511232 KB507272 KB3480 KB480 KB*/SELECT * FROM sys.sysfiles AS s/*fileidgroupidsizemaxsize   growthstatusperfname    filename1       164512-1       1024       2 0    test    D:\database\2014\test.mdf2       0125952268435456  1024       66 0    test_logD:\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.tmpEXEC sp_spaceused/*database_namedatabase_sizeunallocated spacetest1488.00 MB501.63 MBreserveddataindex_sizeunused2424 KB928 KB1096 KB400 KB*/SELECT * FROM sys.sysfiles AS s/*fileidgroupidsizemaxsizegrowthstatusperfnamefilename1       164512-1102420testD:\database\2014\test.mdf2       01259522684354561024660test_logD:\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!=@@spidSET @sql=REPLACE(@sql,';','')PRINT @sqlEXEC (@sql)USE [master]GORESTORE DATABASE test FROM DISK=N'd:\database_bak\test20170919.bak' WITH FILE=1--8. 执行 truncate 和 drop USE testGOTRUNCATE TABLE dbo.tmpDROP TABLE dbo.tmpEXEC sp_spaceused/*database_namedatabase_sizeunallocated spacetest           1488.00 MB   501.63 MBreserveddataindex_sizeunused2424 KB936 KB1096 KB392 KB*/SELECT * FROM sys.sysfiles AS s/*fileidgroupidsizemaxsizegrowthstatusperfnamefilename1       164512-1102420testD:\database\2014\test.mdf2       01259522684354561024660test_logD:\database\2014\test_log.ldf*/--改为完全模式再试USE [master]GODECLARE @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!=@@spidSET @sql=REPLACE(@sql,';','')PRINT @sqlEXEC (@sql)RESTORE DATABASE test FROM DISK=N'd:\database_bak\test20170919.bak' WITH FILE=1USE [master]GOALTER DATABASE [test] SET RECOVERY FULL WITH NO_WAITGOUSE testGODROP TABLE dbo.tmpEXEC sp_spaceused/*database_namedatabase_sizeunallocated spacetest1488.00 MB501.63 MBreserveddataindex_sizeunused2424 KB936 KB1096 KB392 KB*/SELECT * FROM sys.sysfiles AS s/*fileidgroupidsizemaxsizegrowthstatusperfnamefilename1        164512-1102420testD:\database\2014\test.mdf2        01259522684354561024660test_logD:\database\2014\test_log.ldf*/--再恢复再改完全用 truncate , drop 试USE [master]GODECLARE @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!=@@spidSET @sql=REPLACE(@sql,';','')PRINT @sqlEXEC (@sql)RESTORE DATABASE test FROM DISK=N'd:\database_bak\test20170919.bak' WITH FILE=1USE [master]GOALTER DATABASE [test] SET RECOVERY FULL WITH NO_WAITGOUSE testGOTRUNCATE TABLE dbo.tmpDROP TABLE dbo.tmpEXEC sp_spaceused/*database_namedatabase_sizeunallocated spacetest1488.00 MB501.63 MBreserveddataindex_sizeunused2424 KB928 KB1096 KB400 KB*/SELECT * FROM sys.sysfiles AS s/*fileidgroupidsizemaxsizegrowthstatusperfnamefilename1       164512-1102420testD:\database\2014\test.mdf2       01259522684354561024660test_logD:\database\2014\test_log.ldf*/

本人测试版本为： 2014

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