关闭

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

61人阅读 评论(0) 收藏 举报
分类:
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)上是有效的。


0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1781279次
    • 积分:24951
    • 等级:
    • 排名:第256名
    • 原创:728篇
    • 转载:297篇
    • 译文:0篇
    • 评论:176条