---------------------------------------------------------------------
--创建主密钥
use master
Create MASTER KEY ENCRYPTION
BY PASSWORD = 'zhouwei123!'
GO
--创建证书,用于透明数据加密
CREATE CERTIFICATE TDE_Server_Certificate
WITH SUBJECT = 'Server-level cert for TDE'
GO
--第一步：现在开始透明加密
USE cte
GO
CREATE DATABASE ENCRYPTION KEY--创建数据库加密密钥
WITH ALGORITHM = TRIPLE_DES_3KEY--加密方式
ENCRYPTION BY SERVER CERTIFICATE TDE_Server_Certificate--使用服务器级证书加密
GO
--第二步打开加密开关
ALTER DATABASE cte
SET ENCRYPTION ON
GO
--查看数据库是否加密
SELECT name ,is_encrypted
FROM sys.databases
--第三步在cte及nocet中创建相同表
use cte
create table GoodsInfoCte
(
Autoid int identity(1,1) primary key,
GoodsId int,
GoodsName varchar(50),
CusID int,
CusName varchar(50)
);
go
use nocte
create table GoodsInfoNocte
(
Autoid int identity(1,1) primary key,
GoodsId int,
GoodsName varchar(50),
CusID int,
CusName varchar(50)
);
Go
--第四步：清空缓存
DBCC FLUSHPROCINDB(5)
DBCC FLUSHPROCINDB(9)
---------------------------------------------------------------------
插入100000记录对比
--insert nocte..GoodsInfoNocte 100000
declare @b int
set @b=1
while(@b<100000)
begin
insert into nocte..GoodsInfoNocte(GoodsId,GoodsName,CusID,CusName)
select 123,'pc-dell',123456,'张三×××××××××××'
set @b=@b+1
end
insert into nocte..GoodsInfoNocte(GoodsId,GoodsName,CusID,CusName)
select GoodsId,GoodsName,CusID,CusName from logshink..GoodsInfo
--insert nocte..GoodsInfoNocte 100000
declare @b int
set @b=1
while(@b<100000)
begin
insert into nocte..GoodsInfoNocte(GoodsId,GoodsName,CusID,CusName)
select 123,'pc-dell',123456,'张三×××××××××××'
set @b=@b+1
end
insert into nocte..GoodsInfoNocte(GoodsId,GoodsName,CusID,CusName)
select GoodsId,GoodsName,CusID,CusName from logshink..GoodsInfo
执行时间对比（1-6 是循环语句执行时间，7-10从logshink..GoodsInfo插入）：
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
|
37 |
38 |
36 |
37 |
36 |
37 |
CPU 时间 = 1903 毫秒，占用时间 = 3042 毫秒 |
CPU 时间 = 2028 毫秒，占用时间 = 2921 毫秒 |
CPU 时间 = 1981 毫秒，占用时间 = 2999 毫秒 |
CPU 时间 = 1965 毫秒，占用时间 = 3244 毫秒 |
|
29 |
28 |
29 |
30 |
29 |
30 |
CPU 时间 = 515 毫秒，占用时间 = 1223 毫秒 |
CPU 时间 = 562 毫秒，占用时间 = 1175 毫秒 |
CPU 时间 = 483 毫秒，占用时间 = 1105 毫秒 |
CPU 时间 = 515 毫秒，占用时间 = 1275 毫秒 |
I0对比：
表'GoodsInfoCte'。扫描计数0，逻辑读取322516 次，物理读取0 次，预读658 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。
表'GoodsInfo'。扫描计数1，逻辑读取748 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。
表'GoodsInfoNocte'。扫描计数0，逻辑读取322516 次，物理读取0 次，预读658 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。
表'GoodsInfo'。扫描计数1，逻辑读取748 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。
占用空间对比：
---------------------------------------------------------------------
查询前300000对比
select top 300000 * from nocte..GoodsInfoNocte
select top 300000 * from cte..GoodsInfoCte
实际执行计划对比（左：GoodsInfoNocte，右：GoodsInfoCte）
执行时间对比（上：GoodsInfoNocte，下：GoodsInfoCte）：
|
1 |
2 |
3 |
4 |
5 |
6 |
|
CPU 时间= 109 毫秒，占用时间= 3338 毫秒。 |
CPU 时间= 62 毫秒，占用时间= 3285 毫秒。 |
CPU 时间= 109 毫秒，占用时间= 3263 毫秒。 |
CPU 时间= 125 毫秒，占用时间= 3271 毫秒。 |
CPU 时间= 234 毫秒，占用时间= 3353 毫秒。 |
CPU 时间= 265 毫秒，占用时间= 3353 毫秒。 |
|
CPU 时间= 234 毫秒，占用时间= 3283 毫秒。 |
CPU 时间= 250 毫秒，占用时间= 3310 毫秒。 |
CPU 时间= 187 毫秒，占用时间= 3291 毫秒。。 |
CPU 时间= 78 毫秒，占用时间= 3291 毫秒。 |
CPU 时间= 125 毫秒，占用时间= 3282 毫秒。 |
CPU 时间= 156 毫秒，占用时间= 3323 毫秒。 |
IO 对比：
表'GoodsInfoNocte'。扫描计数1，逻辑读取2255 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。
表'GoodsInfoCte'。扫描计数1，逻辑读取2254 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。
---------------------------------------------------------------------
更改300000记录对比
update nocte..GoodsInfoNocte set GoodsName='pc-hp' where Autoid<300000
update cte..GoodsInfoCte set GoodsName='pc-hp' where Autoid<300000
实际执行计划对比（左：GoodsInfoNocte，右：GoodsInfoCte）
Io对比
表'GoodsInfoNocte'。扫描计数1，逻辑读取2250 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。
表'GoodsInfoCte'。扫描计数1，逻辑读取2250 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。
执行时间对比：
DBCC FLUSHPROCINDB(5)
DBCC FLUSHPROCINDB(9)
update nocte..GoodsInfoNocte set GoodsName='pc-hp1-aa' where Autoid<300000
CPU 时间 = 2434 毫秒，占用时间 = 3438 毫秒
update nocte..GoodsInfoNocte set GoodsName='pc-hp2-bb' where Autoid<300000
CPU 时间 = 609 毫秒，占用时间 = 1213 毫秒
update nocte..GoodsInfoNocte set GoodsName='pc-hp3-cc' where Autoid<300000
CPU 时间 = 671 毫秒，占用时间 = 715 毫秒
update nocte..GoodsInfoNocte set GoodsName='pc-hp4-dd' where Autoid<300000
CPU 时间 = 593 毫秒，占用时间 = 677 毫秒
update nocte..GoodsInfoNocte set GoodsName='pc-hp5-ee' where Autoid<300000
CPU 时间 = 639 毫秒，占用时间 = 688 毫秒
update cte..GoodsInfoCte set GoodsName='pc-hp1-aa' where Autoid<300000
CPU 时间 = 5335 毫秒，占用时间 = 7251 毫秒
update cte..GoodsInfoCte set GoodsName='pc-hp2-bb' where Autoid<300000
CPU 时间 = 3791 毫秒，占用时间 = 6196 毫秒
update cte..GoodsInfoCte set GoodsName='pc-hp3-cc' where Autoid<300000
CPU 时间 = 3916 毫秒，占用时间 = 6197 毫秒
update cte..GoodsInfoCte set GoodsName='pc-hp4-dd' where Autoid<300000
CPU 时间 = 3401 毫秒，占用时间 = 5141 毫秒
update cte..GoodsInfoCte set GoodsName='pc-hp5-ee' where Autoid<300000
CPU 时间 = 3931 毫秒，占用时间 = 5933 毫秒
删除 前300000条记录
DBCC FLUSHPROCINDB(5)
DBCC FLUSHPROCINDB(9)
delete from nocte..GoodsInfoNocte where Autoid<300000
CPU 时间 = 624 毫秒，占用时间 = 2477 毫秒。
delete from cte..GoodsInfoCte where Autoid<300000
CPU 时间 = 3011 毫秒，占用时间 = 7073 毫秒