| insert into 消耗秒数/日志文件增长量 | select * into 消耗秒数/日志文件增长量 |
---|---|---|
简单日志 | 7 分 8 秒 / 10850 MB | 2 分 10 秒 / 100MB |
大容量日志 | 7 分 19 秒 / 10850 MB | 2 分 / 100MB |
完整日志 | 7 分 16 秒 / 10950 MB | 5 分 23 秒 / 3800MB |
源表:
name rows reserved data_size_MB index_size_MB unused_MB
t 36892291 3563 3555 7 0
----------- 0.创建测试数据库, 每次测试都重新创建1次, 共 6 次 -----------
IF EXISTS(SELECT * FROM sys.databases AS d WHERE d.name='test')
BEGIN
ALTER DATABASE [test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE test
END
GO
CREATE DATABASE [test] ON PRIMARY
( NAME = N'test', FILENAME = N'e:\database\2014\test.mdf' , SIZE = 51200KB , FILEGROWTH = 51200KB )
LOG ON
( NAME = N'test_log', FILENAME = N'e:\database\2014\test_log.ldf' , SIZE = 51200KB , FILEGROWTH = 51200KB )
GO
ALTER DATABASE [test] SET RECOVERY FULL --SIMPLE , BULK_LOGGED
GO
----------- 测试1: insert into
--创建表语句
USE test
GO
IF OBJECT_ID('[dbo].t') IS NOT NULL
DROP TABLE [dbo].t
GO
CREATE TABLE [dbo].t(
[id] [bigint] IDENTITY(1,1) NOT NULL,
[xx] [nvarchar](30) NOT NULL,
CONSTRAINT [PK_t] PRIMARY KEY CLUSTERED
(
[id] ASC
)
) ON [PRIMARY]
GO
--插入测试
SET IDENTITY_INSERT test.[dbo].t ON
INSERT INTO test.[dbo].t
(id,xx)
SELECT [id]
,xx
FROM source.[dbo].t WITH(NOLOCK)
GO
----------- 测试2: select * into target from souce
--1. 简单日志
SELECT *
INTO test.dbo.t_simple
FROM [source].dbo.t WITH(nolock)
--2. 大容量日志
SELECT *
INTO test.dbo.t_BULK_LOGGED
FROM [source].dbo.t WITH(nolock)
--3. 完整日志
SELECT *
INTO test.dbo.t_FULL
FROM [source].dbo.t WITH(nolock)