测试脚本：
-- 创建测试库
-- drop database TestDB
use master
go
create database TestDB
go
-- 创建证书
-- drop certificate Mycertificate;
use TestDB
go
create certificate Mycertificate
encryption by password = N'Hello@Mycertificate'
with subject = N'EnryptData certificate',
start_date = N'20150101',
expiry_date = N'20160101';
go
-- 创建以证书加密的对称密钥
-- drop symmetric key MySymmetric;
use TestDB
go
create symmetric key MySymmetric
with
algorithm=aes_128
encryption by certificate Mycertificate
go
-- 测试加密和解密
-- close symmetric key MySymmetric;
use TestDB
go
open symmetric key MySymmetric
decryption by certificate Mycertificate with password = N'Hello@Mycertificate';
go
select encryptbykey(key_guid('MySymmetric'),cast('123456' as varchar(20)))
go
select convert(varchar(20),decryptbykeyautocert(cert_id('Mycertificate'),N'Hello@Mycertificate'
,0x001E60848B02184E9106B2BDF6F612470100000023BE0228F35192CC39EE810A0B6D31B4EC12F68EAFC2DA8FB4F6C688F869D7EF))
go
-- 创建分表
-- drop table objects_Part1,objects_Part2
use TestDB
go
select object_id,name,principal_id,schema_id,parent_object_id,type,type_desc,create_date,modify_date
into objects_Part1
from sys.objects
go
select object_id,is_ms_shipped,is_published,is_schema_published
into objects_Part2
from sys.objects where object_id<>object_id('objects_Part1')
go
create clustered index ix_objects_Part1 on [dbo].[objects_Part1]([object_id])
go
create clustered index ix_objects_Part2 on [dbo].[objects_Part2]([object_id])
go
-- 创建联合视图
-- drop view objectsAll
use TestDB
go
create view [dbo].[VobjectsAll]
as
select t1.object_id,t1.name,t1.principal_id,t1.schema_id,t1.parent_object_id,t1.type,t1.type_desc,t1.create_date
,t1.modify_date/*,t2.object_id*/,t2.is_ms_shipped,t2.is_published,t2.is_schema_published
from [dbo].[objects_Part1] t1 left join [dbo].[objects_Part2] t2 on t1.object_id=t2.object_id
go
-- 创建更新触发器
-- drop trigger [dbo].[tgr_objectsall_update]
use TestDB
go
create trigger [dbo].[tgr_objectsall_update]
on [dbo].[VobjectsAll]
instead of update
as
begin
declare @COLUMNS_UPDATED varbinary(100)
declare @IsUpdateTab01 int = 0
declare @IsUpdateTab02 int = 0
declare @T1 int
declare @T2 int
set @T1 = 65281
set @T2 = 254
/*表1: 11111111 00000001*/
/*表2: 00000000 11111110*/
set @COLUMNS_UPDATED = COLUMNS_UPDATED()
set @IsUpdateTab01 = SUBSTRING(@COLUMNS_UPDATED,1,2) & @T1
set @IsUpdateTab02 = SUBSTRING(@COLUMNS_UPDATED,2,1) & @T2
if ( @IsUpdateTab01 > 0 AND @IsUpdateTab02 = 0 )
begin
print 'update [objects_Part1]'
update t1 set
t1.name = t2.name
,t1.principal_id = t2.principal_id
,t1.schema_id = t2.schema_id
,t1.parent_object_id = t2.parent_object_id
,t1.type = t2.type
,t1.type_desc = t2.type_desc
,t1.create_date = t2.create_date
,t1.modify_date = t2.modify_date
from [dbo].[objects_Part1] t1,inserted t2 where t1.object_id = t2.object_id
end
else if ( @IsUpdateTab01 = 0 AND @IsUpdateTab02 > 0)
begin
print 'update [objects_Part2]'
open symmetric key MySymmetric
decryption by certificate Mycertificate with password = N'Hello@Mycertificate';
update t1 set
t1.is_ms_shipped = t2.is_ms_shipped
,t1.is_published = t2.is_published
,t1.is_schema_published = t2.is_schema_published
from [dbo].[objects_Part2] t1,inserted t2 where t1.object_id = t2.object_id
end
else
begin
print 'update [objects_Part1] and [objects_Part2]'
update t1 set
t1.name = t2.name
,t1.principal_id = t2.principal_id
,t1.schema_id = t2.schema_id
,t1.parent_object_id = t2.parent_object_id
,t1.type = t2.type
,t1.type_desc = t2.type_desc
,t1.create_date = t2.create_date
,t1.modify_date = t2.modify_date
from [dbo].[objects_Part1] t1,inserted t2 where t1.object_id = t2.object_id
update t1 set
t1.is_ms_shipped = t2.is_ms_shipped
,t1.is_published = t2.is_published
,t1.is_schema_published = t2.is_schema_published
from [dbo].[objects_Part2] t1,inserted t2 where t1.object_id = t2.object_id
end
end
go
-- 测试!
use TestDB
go
select * from [vobjectsall]
update [vobjectsall] set principal_id = 0 where object_id = 3
更新视图字段 principal_id ，将更新触发器中的第一个分支。执行一次更新耗时不明显，现在单线程更新30次。对比耗时如下图。
第二次更新时，去掉触发器中第二个条件分支的加密语句：
-- 第二次更新去掉触发器中的打开密钥语句
open symmetric key MySymmetric
decryption by certificate Mycertificate with password = N'Hello@Mycertificate';
这就是奇怪的地方，更新的只是第一个分支中的表。第二个分支是没有执行的，但是第二个分支的 “打开密钥” 却影响到总体时间！
为什么？为什么？为什么？……