if object_id('t_encryption','u') is not null drop table t_encryption 
go
create table t_Encryption
(
    DCNO int identity primary key,
    DCNAME varchar(50) ,
    SalaryInfo varbinary(max)
)
go
---------------------------------------------------创建加密证书
create certificate cert_SalaryInfo
Encryption by password='P@ssw0rd'
with 
    subject=N'Salary Information Encryption',
    expiry_date='9999-12-31'
-----删除证书 
----- drop certificate cert_SalaryInfo
go
---------------------------------------------------创建对称加密
create symmetric key sym_SalaryInfo
with 
    algorithm=DES    --使用DES 加密算法（*不知道原理）
encryption by certificate cert_SalaryInfo
----删除对称加密
----drop symmetric key sym_SalaryInfo
go
---------------------------------------------------打开对称加密(插入数据前要打开对称加密，否则将无效)
open symmetric key sym_SalaryInfo
decryption by certificate cert_SalaryInfo
with password =N'P@ssw0rd'
go
---------------------------------------------------插入加密数据
insert into t_Encryption(DCNAME,SalaryInfo)
select 'andy',ENCRYPTBYKEY(KEY_GUID('sym_SalaryInfo'),N'1000.00') union all 
select 'job',ENCRYPTBYKEY(KEY_GUID('sym_SalaryInfo'),N'2000.03')
go
---------------------------------------------------关闭密钥
close symmetric key sym_SalaryInfo
go
---------------------------------------------------查询表数据
select * from t_Encryption
/*
DCNO        DCNAME                                             SalaryInfo
----------- -------------------------------------------------- -----------------------------------------------------------------------
1           andy                                               0x00A4EFE20ABB16458517D289B626ECB20100000060F12ABBD792B7C542DC8AAEC496A3796A9C0B7D454F56CAC6AA60763953B631
2           job                                                0x00A4EFE20ABB16458517D289B626ECB2010000003CAAC262CE95588561CA30139608AD380608F12F70E7271006702FA037D5AE1A

(2 row(s) affected)
*/
go
---------------------------------------------------使用对称加密直接查看(不开启对称加密)
select 
    DCNO,
    DCNAME,
    convert(nvarchar(500),DECRYPTBYKEY(SalaryInfo)) as SalaryInfo
from t_Encryption
/*
DCNO        DCNAME                                             
----------- -------------------------------------------------- -------------------
1           andy                                               NULL
2           job                                                NULL

(2 row(s) affected)
*/
go
--------------------------------------------------查询数据前要打开加密
open symmetric key sym_SalaryInfo
decryption by certificate cert_SalaryInfo
with password =N'P@ssw0rd'
go
select 
    DCNO,
    DCNAME,
    convert(nvarchar(500),DECRYPTBYKEY(SalaryInfo)) as SalaryInfo
from t_Encryption
/*
DCNO        DCNAME                                             SalaryInfo
----------- -------------------------------------------------- ------------------
1           andy                                               1000.00
2           job                                                2000.03

(2 row(s) affected)
*/
go
--------------------------------------------------更新DCNO=1的salaryinfo 变为1500
update t_Encryption set SalaryInfo=ENCRYPTBYKEY(KEY_GUID('sym_SalaryInfo'),N'1500')
where DCNO=1
go
--------------------------------------------------再次插入一条记录
insert into t_Encryption(DCNAME,SalaryInfo)
select 'Ivan',ENCRYPTBYKEY(KEY_GUID('sym_SalaryInfo'),N'3000')
go
select 
    DCNO,
    DCNAME,
    convert(nvarchar(500),DECRYPTBYKEY(SalaryInfo)) as SalaryInfo
from t_Encryption    
/*
DCNO        DCNAME