关闭

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

250人阅读 评论(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
查看评论

Oracle-09-删除表(drop和truncate区别、闪回删除等)

一、如果既要删除表中的数据也要删除表的结构,请使用droptable语句,其格式如下: drop table 表名   drop table语句(是DDL语句)有如下特征: (1)它删除表中所有的数据行和表结构; (2)它也删除表的所有索引(后续会讲到); (3)如果没有备份的话,所删除...
  • wy_0928
  • wy_0928
  • 2016-04-13 23:07
  • 3662

DROP TABLE、TRUNCATE TABLE和DELETE的区别

声明:本篇文章来自百度知道答案整理,附上链接 http://zhidao.baidu.com/link?url=zPEGUyOzPprMej31Yc50dXKzYEkBHijignfVk8sdUUPN1DmnEmXbEagQJLVMI3W8OtHu7jjrAwbPlQFWnmsDGq 一、相同...
  • u013815649
  • u013815649
  • 2015-12-23 10:13
  • 4938

truncate 与 delete 、drop 【PLSQL中truncate用法】

注意事项 1.在oracle 中数据删除后还能回滚是因为它把原始数据放到了undo表空间, 2.DML语句使用undo表空间,DDL语句不使用undo,  而delete是DML语句,truncate是DDL语句,别外DDL语句是隐式提交.所以truncate操用不能回滚,而d...
  • cicada688
  • cicada688
  • 2011-12-19 09:37
  • 6924

Truncate Delete Drop命令的区别 .

truncate和不带where子句的delete, 以及drop都会删除表内的数据
  • wx3957156
  • wx3957156
  • 2014-10-15 18:20
  • 1474

数据库中删除语句Drop、Delete、Truncate的相同点和不同点的比较(举例说明)

转载来自: 数据库中删除语句Drop、Delete、Truncate的相同点和不同点的比较(举例说明)   数据库删除语句的分别介绍: Delete:用于删除表中的行(注:可以删除某一行;也可以在不删除表的情况下(即意味着表的结构、属性、索引完整)删除所有行) ...
  • shehun11
  • shehun11
  • 2014-10-24 11:20
  • 1308

Hive学习之创建、删除、Truncate表

创建表的语句有两种,分别为: CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name [(col_name data_type [COMMENT col_comment], ...)] [COMMENT table_commen...
  • sky_walker85
  • sky_walker85
  • 2014-06-08 19:16
  • 17521

如何恢复被drop或truncate表的数据

如果在线上环境误操作删除或清空了不该删除的表,常用的方式是冷备+增量binlog恢复表数据,这个通用方法大家都会,不在本篇讨论范畴。 这篇文章主要介绍在没有冷备的情况下如何利用binlog恢复表数据 前提条件:必须具备表创建以来所有的binlog; 下图是能完全恢复表dba.kian的原理图。...
  • kianliu_007
  • kianliu_007
  • 2014-10-29 00:24
  • 2103

oracle之truncate table 的drop storage和reuse storage

首先创建两个测试表test,test1 两个表数据放在data_tablespace,索引放在index_tablespace表空间下(测试证明和表空间没有关系) 如果使用下面两个语句删除两个表中的数据 truncate table test drop storage; truncate t...
  • xwm1999
  • xwm1999
  • 2016-10-19 10:40
  • 1662

MySQL delete/truncate/drop的差别分析

用户想要删除数据表可以使用delete、truncate以及drop来删除数据。从功能上讲不带where子句的delete、truncate和drop是相似的。三者之间的不同之处主要包括以下几点:1、删除内容drop操作会删除表结构、依赖的约束、索引以及触发器,并且会将依赖该表的所有存储过程和视图设...
  • wanglei_storage
  • wanglei_storage
  • 2016-06-28 16:25
  • 410

Mysql中 delect 、truncate、drop 的区别

学习Mysql的时候,我们会学习到表中记录的删除、表的删除、 数据库的删除等操作,那么那些删除语句中有什么不同呢? delect   from   table_name; truncate table  table_name; 区别 ...
  • WCCchangchun
  • WCCchangchun
  • 2016-04-22 11:22
  • 903
    个人资料
    • 访问:2008489次
    • 积分:27733
    • 等级:
    • 排名:第237名
    • 原创:796篇
    • 转载:314篇
    • 译文:1篇
    • 评论:197条